Access tables linking to ODBC

[DennisJones]

Hello, I am working on an Access project where I have to connect to a MySQL database and download the tables using ODBC my problem is that every time I open a form it seems to kick off the process whereby it downloads the tables afresh (which takes ages) . How can I stop this happening and force it to use local tables?

Thanks

And a second question I want to write a macro or VB code to update my tables on demand, any hints on what to call would be appreciated.

 

[roelandkim]

We use SQL server at my office, but sometime localize tables to make them respond faster. Basically if we have a table dbo.MyTable through an ODBC connection, we create a Make-Table query (naming the table Tbl_MyTable_Local or something), which creates a local version of the data from the SQL server. This works best if your data is not very time sensitive, as each refresh takes some time to run.

If we have multiple tables, we write multiple Make-Table queries, put a "Refresh Data" button on the menu, and link the button to a macro or vb code. The user can then refresh on demand, but doesn't need to refresh the data each time the report is run, and since the data is local, it runs much faster (a lot depends on how much data you are trying to pull together).

 

[DennisJones]

Thanks for your response. I have followed the general strategy that you outlined but when I came to run the make_table queries Access objected on the grounds that it couldn't rewrite the tables without destroying the relationship structure.

I guess I really need some sort of update query to avoid this.

Regards