Using a local table vs qry Criteria

[tuktuk]

Hey there,

this is sorta a strange quesiton but here goes:

i've noticed that running a qry with a ODBC linked table (to our as400 server) runs very slow when i link to a locally stored table. the Locally stored table is quite simple contain field call ProdName and ProdCode.

If i link the ODBC connected table to the local table on ProdCode then set the crieteria on ProdName it is very slow.

therefore i find removing teh local table and typing in the criteria for [ODBC'dTable].[ProdCode] as "In ("ProdCode1","ProdCode2"....."ProdCodeX").

is there some code that cna look at a table and concatenate the recordsets in that format.

I know this is a workaround and that there has to be a better way to increase the speed on the qry but for now it appears to be a good workaround.

T

 

[xenou]

Not tested but should work:

Sub Foo()
Dim s As String
Dim strSQL As String
Dim rs As DAO.Recordset

Set rs = currentdb.OpenRecordset("Table1",dbOpenTable)

If Not rs.EOF Then
    s = ""
    Do While Not rs.EOF
        s = s & Chr(39) & rs.Fields("ProdCode").Value & Chr(39)
	rs.MoveNext
    Loop 
    s = Left(s,Len(s) - 1)
    strSQL = "SELECT t1.Field1, t1.Field2, t1.[ProdCode] FROM [ODBC'dTable] t1 WHERE t1.[ProdCode] IN (" & s & ");"
    CurrentDB.QueryDefs("Query1").SQL = strSQL
    Debug.Print strSQL '//If query doesn't work check this SQL string and make sure it's a valid SQL string.
    DoCmd.OpenQuery "Query1"
Else
    msgbox "Error: No prodcodes"
End If

On Error Resume Next
rs.Close
Set rs = Nothing

End Sub

Check me for typos as I typed this out in a text editor.

 

[tuktuk]

hey there so after working with this a bit i am very close.

i had change a few thing tho:

here is my almost working code: (the error lies in the parameter)

Set rs = CurrentDb.OpenRecordset("tbl_AgntKey_CURRENT", dbOpenTable)

If Not rs.EOF Then
    's = ""
    Do While Not rs.EOF
        's = s & Chr(39) & rs.Fields("AGTKEY").Value & Chr(39)
        s = s & rs.Fields("AGTKEY").Value & ","
    rs.MoveNext
    Loop
    s = Left(s, Len(s) - 1)

the values of the "tbl_AgntKey_CURRENT" are 10898, the next being 10899, 108900, etc....

currently after stepping thur the code compiling the () if i hover over s then i get

="10898,10899,108900"

i need to drop the " on the beginning and end of the compiled string

thanks

i cant find what adds it......is it in the declaration?

i should say the values in the recordset used in the parameter are data types = number

 

[xenou]

The double quotes you see in mouseovers are just for looks - they aren't really part of the string (you can verify this in the immediate window). Offhand, it looks okay to me.

Often the best way to debug your result is to get to the point that you have the completed SQL string. Debug.Print this in the immediate window, copy it into a new query (SQL view), and see if it works ...

If not, post your entire code so we can see how the string gets built up. And what you get for your final strSQL value in the immediate window.

ξ

Edit: I noticed you did use quotes in Post #1 ... it doesn't matter what the source data type is, but rather what the data type of the value in the table you are querying (the Product ID's in your "ODBC'd" Table)