only reference required is
Microsoft ActiveX Data Objects 2.7 Library

the Connection string is in this, and mine should look like this:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Risk.mdb"
The provider tells the connection that its going to connect to an Access
database





"davyboom" wrote in message
...
Hi Patrcik,

thought your answer is to me.

I hd copy yr. module into my Excel VBA editor nand changed the poitner to
my
mdb file and its table.
Simialr error echoed.
Just wonder hw u reference yr. module.
I got "ticked" in my reference-
Microsoft ADO (multidimensional) 2.8
Microsoft ADO ext 2.8 for DDL and security
Microsoft ADO2.8 library
Microsoft Jet & application objects 2.6 library
Microsoft ADO recordset 2.8
Microsoft OLEDB provider for OLAP services connection dialog 8.0

I really do not know which give function to Jet.OLEDB.4.0

can anyonehelp me to resolve?

thanks,
davy

"Patrick Molloy" wrote:

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?