Calling Microsoft Excel ODBC Query

[Steve C.]

Hi all,
I have an Excel query that extracts data from my Microsoft
Access database. Is there anyone be kind enough to show me
how to call the query in Excel using VBA? I can double
click it in Window Explorer to get the data but I don't
know how to do it in Excel VBA.

Appreciate your help!

 

[RB Smissaert]

Install the MS ADO library, this is in the VBE, Tools, References:
Microsoft ActiveX Data Objects 2.x Library

Then this is a simple example of code:

Sub test()

Dim rs As ADODB.Recordset
Dim strConnect As String
Dim strSQL As String

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test.mdb;"

strSQL = "SELECT I.SURNAME FROM IdTable I WHERE I.ID = 7800"

Set rs = New ADODB.Recordset

rs.Open strSQL, strConnect, adOpenForwardOnly, adLockReadOnly, adCmdText

MsgBox rs.Fields(0)

rs.Close
Set rs = Nothing

End Sub


RBS

 

[Steve C.]

Thanks RBS. Have a nice day!

-----Original Message-----
Install the MS ADO library, this is in the VBE, Tools, References:
Microsoft ActiveX Data Objects 2.x Library

Then this is a simple example of code:

Sub test()

Dim rs As ADODB.Recordset
Dim strConnect As String
Dim strSQL As String

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test.mdb;"

strSQL = "SELECT I.SURNAME FROM IdTable I WHERE I.ID = 7800"

Set rs = New ADODB.Recordset

rs.Open strSQL, strConnect, adOpenForwardOnly, adLockReadOnly, adCmdText

MsgBox rs.Fields(0)

rs.Close
Set rs = Nothing

End Sub


RBS




.