Calling Microsoft Excel ODBC Query
 

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!

Calling Microsoft Excel ODBC Query
 

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." wrote in message
...
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!

Calling Microsoft Excel ODBC Query
 

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


"Steve C." wrote in
message
...
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!



.