Hi there,

I am about to start working on a new project where I have a front-end in Access2000 that is linked to a ODBC Data Source (ORACLE).

There could be some performance issues in the future, when the users have to retrieve (query) the data from the ODBC over the network and it slows down. Nothing is sure yet, but when this is the problem, I will need to look at an alternative plan. I had the following in mind:

I would like to create a scheduler program, that will retrieve all the relevant data from the ODBC and update the tables that are local to each user’s Access .mdb file. Can I do something like this in VBA, or do I need to do something with the Windows Scheduler?

Any tips are welcome, thanks in advance

J

You can import the data each time the user opens the database or the first time he opens it each day (you'll need to keep a table with the last import date to manage this).

I do not recommend importing the data except as a last resort. If you end up doing it, I would recommend a separate database that contains empty tables with all primary keys and alternate indexes for foreign keys defined. No relationships are necessary since no updates will be done to the imported data. Each time you want to import a new set of data your code would copy the saved "empty" .mdb to a local drive overlaying the previous version and run the append queries that copy down the data. With this method, you won't need to worry about database bloat or import order.

Thanks Pat for the wise words.
If push comes to shove I will consider this method. I have only just started running some queries on the backend and it is going fine.

Regards,
J