MYSQL database linked in MS Access - slow response time (1 Viewer)

[Yann63]

Hello

I have successfully linked a MYSQL database into MS Access. But the response time in Access when opening or quering such a linked table is very slow.
Is this a problem of MYSQL or Access ?

Furthermore what is the correct VB code to open such a linked table ?

Thank you in advance.

 

[mdlueck]

I have successfully linked a MYSQL database into MS Access. But the response time in Access when opening or quering such a linked table is very slow.

Could you be a little more specific? How many rows are in the remote MySQL table? 2 billion?

Furthermore what is the correct VB code to open such a linked table?

"Open" how? Open a linked table view to 2 billion records?

I prefer to do things thusly in the Client/Server environment:

Client/Server Architecture
http://www.access-programmers.co.uk/forums/showpost.php?p=1110794&postcount=5

 

[Yann63]

No this is really a test database with a table of 25 lines. So volume of the table can't be an issue.

 

[mdlueck]

No this is really a test database with a table of 25 lines. So volume of the table can't be an issue.

All right. Again...

"Open" how?

 

[Yann63]

With "open the linked table" I mean the following.

To open an internal Access table I use following code

Set db = DBEngine.Workspaces(0).Databases(0)
Set rs1 = db.OpenRecordset("users", DB_OPEN_TABLE)
rs1.Index = "primarykey"

Which kind of code I should use to open the linked MYSQL table ?

Any idea regarding the slow response time when manually accessing the table in Access ?
Thanks

 

[mdlueck]

So you mean "open" as in connect to a Linked table in VBA code... as opposed to opening a Linked Table window and displaying "all" of the records.

Is there any reason that you are not opening the connection in Pass-Through SQL mode? That is, passing the credentials directly to the remote SQL box, passing the SQL directly, and receiving the results back?

The code you showed did not indicate such, so I assume that it is not utilizing Pass-Through queries, rather / instead Access thinks about the query, then passes it to the SQL box to have it think about the query. Frequently, with two interpretations of the query, garbage results.