By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,204 Members | 1,178 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,204 IT Pros & Developers. It's quick & easy.

Error accessing data from Access through Excel

P: n/a
I am trying to get data from Access through a SQL query from Excel via
VBA: but i get an error of "Run Time Error '-2147467259 (800040005)':
Method 'Open' of object '_Recordset' failed"

I have no idea why this is having an error, it is crashing at the line
(any ideas? thanks):
rsData.Open szSQL, connecto, adOpenForwardOnly, adLockReadOnly,
adCmdText

I have the following code:
================================================== =
Public Sub PlainTextQuery()

Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String

Dim connecto As ADODB.Connection
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=H:\user\Le\MSRB Daily Data\testDB.mdb;"

Set connecto = New ADODB.Connection

szSQL = "SELECT * FROM Current WHERE Current.CUSIP='005482F82';"

Set rsData = New ADODB.Recordset
connecto.Open szConnect
rsData.Open szSQL, connecto, adOpenForwardOnly, adLockReadOnly,
adCmdText
If Not rsData.EOF Then
Sheet1.Range("A2").CopyFromRecordset rsData
rsData.Close
Sheet1.UsedRange.EntireColumn.AutoFit
Else
MsgBox "Error: No records returned.", vbCritical
End If

If CBool(rsData.State And adStateOpen) Then rsData.Close
Set rsData = Nothing
End Sub
==================================================

Nov 13 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.