Sub LoadDataFromAccess()
Dim MyFile As String 'DB name
Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim SQL As String
Dim i As Long 'for/next loop counter

MyFile = "Risk.mdb"
SQL = "SELECT * FROM BondTable"

con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyFile

rst.Open SQL, con, adOpenStatic

Cells.Clear
'add headings
For i = 0 To rst.Fields.Count - 1
Cells(1, i + 1).Value = rst.Fields(i).Name
Next

Range("A2").CopyFromRecordset rst

"davyboom" wrote in message
...
HI all,

I am learning to use SQL w. Excel.
I copied the follwowing book example into VBA(Excel) code window, I got
problem with Jet.OLEDB.4.0

Book example code:
Public Sub GetCn(ByRef dbcon As ADODB.Connection, ByRef dbrs As
ADODB.Recordset, _
sqlstr As String, dbFile As String, usernm As String, pWord As String)

Set dbcon = New ADODB.Connection
dbcon.Open "Provider=Microsoft.jet.OLEDB.4.0; Data Source=" & dbFile &
";", _
usernm, pWord
Set dbrs = New ADODB.Recordset
dbrs.Open sqlstr, dbcon
End Sub

Question:

MY VBA module debug got into error on this line
"Provider=Microsoft.jet.OLEDB.4.0: Data Source=".......
Error Message
[run time error '-2147467259 (80004005)' Automation error, unspecified
error]
what has been worng? how to fix?

thanks,
davy


"heavenlyhost1" wrote:

I have a report in ACCESS where the user enters a start date and end date
of
the 'Date Submitted' field in the table. The report then gives the total
count of records that that are generated between two given dates. I have
an
excel report where I would like this total count to go to when the excel
report is opened. I've tried the ODBC thing and link thing but can't
seem to
get this one field to plug into the excel spreadsheet. Can this be done?