How to use MS Access pass through query with MySQL

[James Choi]

Can anyone please help me!

I have relatively very simple query.
When I run it in native MS Access DB as back end this query runs fine but
when I try to run the query in link MySQL DB as back end then query runs incredibly slow.

I read that this is because MS Access has to translate it into OLE DB first before it can be sent as ODBC. To work around this problem I need to send pass through query to MySQL and have query run directly in MySQL.

I know how to create pass through query but I don't know how to send it in to MySQL?

Here is the simple query that take way to much time to run:


Dim cnn As New ADODB.Connection
Dim rst As New ADODB.RecordSet
Dim rst1 As New ADODB.RecordSet

Set cnn = CurrentProject.Connection

gstrSQL = "SELECT * FROM tblmemberTemp WHERE DataUD = 'Y'"
rst.Open gstrSQL, cnn, adOpenForwardOnly, adLockReadOnly

Do Until rst.EOF
gstrSQL = "SELECT FamilyID, DistrictID, RegionID, CellID, CellLevel,
CellSort FROM tblmembers WHERE FamilyID = '" & rst.Fields!FamilyID "'"
rst1.Open gstrSQL, cnn, adOpenForwardOnly, adLockOptimistic

Do Until rst1.EOF
rst1.Fields!DistrictID = rst.Fields!DistrictID
rst1.Fields!RegionID = rst.Fields!RegionID
rst1.Fields!CellID = rst.Fields!CellID
rst1.Fields!CellLevel = rst.Fields!CellLevel
rst1.Fields!CellSort = rst.Fields!CellSort
rst1.Update

rst1.MoveNext
Loop
rst1.Close
Set rst1 = Nothing

rst.MoveNext
Loop
rst.Close
Set rst = Nothing