MS Excel VBA to Access Runtime error -2147467259 1

[MsHart2u]

I've searched the blogs and cannot find my scenario, in trying to debug... I am using Excel 2000 to connect to Access 2000. I get this Runtime error -2147467259 Automation error Unspecified error.

My basic goal is to connect to Access and retrieve a record set into a temp table. Any help would be greatly appreciated.

I compare my references to those recommended yet I don't see what I am missing...References:
Visual Basic for Applications
Microsoft Excel 10.0 Object Library
OLE Automation
Microsoft Office 10.0 Object Library
Microsoft ActiveX Data Objects 6.0 Library
Microsoft Data Access Components Installed Version

******* BEGIN CODE ************
Sub ImportFromAccess()
Dim DBFullName As String
Dim TableName As String, TargetRange As Range
Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Dim dtDay1 As Date

DBFullName = "C:\Users\Owner\Documents\KH.mdb"
TableName = "Clients"
dtDay1 = Range("C9").Value

' open the database
Set cn = New ADODB.Connection
'****** ERROR HAPPEN HERE cn.Open *******
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=DBFullName;"
'************END ERROR*******************
Set rs = New ADODB.Recordset
With rs
' open the recordset
.Open "SELECT count(*) FROM " & TableName & _
" WHERE date = " & dtDay1, cn, , , adCmdText
' filter records

'********** END CODE

[blue]Kathy,
Bus Sys Analyst[/blue]
"I am always doing that which I can not do, in order that I may learn how to do it."– Pablo Picasso
The person who says it can't be done shouldn't interrupt the person who is doing it.—Chinese Proverb

 

[Golom]

Change this

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=DBFullName;"

to this

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=[red]" & DBFullName & ";"[/red]

 

[MsHart2u]

Thank you...So my script has progressed and looks like this but returns and an empty recordset. Please advise as to what I am doing wrong.

'*****BEGIN *******
' open the database
Set cn = New ADODB.Connection

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";"
Set rs = New ADODB.Recordset

vtSql = vtSql & "SELECT row_date,vector, incalls "
vtSql = vtSql & "FROM " & TableName
vtSql = vtSql & " WHERE row_date = " & dtDay1 & " AND vector = 9"
vtSql = vtSql & ";"
' Open the recordset.
Set rs = New Recordset
rs.Open vtSql, cn, adOpenStatic, adLockReadOnly
' Loop through the recordset and send data to the Immediate Window
Debug.Print vtSql
Debug.Print rs![row_date] & " " & rs![vector] & " (" & rs![incalls] & ")"

'**** END ******

[blue]Kathy,
Bus Sys Analyst[/blue]
"I am always doing that which I can not do, in order that I may learn how to do it."– Pablo Picasso
The person who says it can't be done shouldn't interrupt the person who is doing it.—Chinese Proverb

 

[Golom]

Difficult to say because I don't know what value "dtDay1" has. I also don't know what's in your table so there may not be any records that qualify.

I do notice that, if "row_date" and "dtDay1" are DateTime fields, then you should enclose the value in # ... # marks

vtSql = vtSql & " WHERE row_date = [red]#[/red]" & dtDay1 & "[red]#[/red] AND vector = 9"

 

[MsHart2u]

Again, you are awesome and quick!!!

Kudos and congrats on winner of the week.

[blue]Kathy,
Bus Sys Analyst[/blue]
"I am always doing that which I can not do, in order that I may learn how to do it."– Pablo Picasso
The person who says it can't be done shouldn't interrupt the person who is doing it.—Chinese Proverb