MS Access VBA text Search records in multible tables in ODBC and import to local tabl
I am developing a UI in Access 2007 using ODBC. The first task I need to do is to develop a form. The form1 has a logic in order as bellow
1. With search box filled with a list of IDs(1111,2222,3333,4444), on click on the search button
2. The VBA code will run a query to get corresponding field1, field2 in
T1(linked from ODBC) from ODBC, field3, field4 in T2(linked from
ODBC) from ODBC and import them into two new local tables. (say
the result will be two new local tables T1_LOCAL WITH field 1 and
field 2, T2.Local with field3 and field 4) *the reason I am doing
this is because there are a lot of query need to be run, and the
odbc TABLES HAS TOO MANY DATA RECORDS which made the query to
slow.*
3. Then display a datasheet on form1 as a search
result that contains IDs, Check Box,filed 1, field 2, field 3, field
4
I tried to call the SQL in vba but always get error. Also I don't know how to make the multiple IDs search as a dynamic reference in my SQL. Say if I copy past 100 IDs from (comma separated) how to reference them in the VBA code to get 100 corresponding records from ODBC tables.
Below is the code I tried
Code:
Option Compare Database
Private Sub runsql()
Dim qdfCurr As DAO.QueryDef
Dim strSQL As String
strSQL = "SELECT T1.ID, T1.field1, T1.field2 into T1_LOCAL FROM T1 WHERE T1.ID IS IN txtSearch;"
strSQL = "SELECT T2.field3, T2.field4 into T2_LOCAL FROM T2 WHERE T2.ID IS IN txtSearch;"
On Error Resume Next
Set qdfCurr = CurrentDb.QueryDefs("TempQuery")
If Err.Number = 3265 Then ' 3265 is "Item not found in this collection."
Set qdfCurr = CurrentDb.CreateQueryDef("TempQuery")
End If
qdfCurr.SQL = strSQL
DoCmd.OpenQuery "TempQuery"
End Sub
