Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Microsoft Query/ODBC issue | Excel Discussion (Misc queries) | |||
Syntax Error - Using Microsoft Query and ODBC link to a table | Excel Discussion (Misc queries) | |||
Excel ODBC Query | Excel Programming | |||
EXCEL AND ODBC AND QUERY | Excel Programming | |||
How to insert data into microsoft excel sheet using ODBC API | Excel Programming |