OBDC Links in Access (Access2000)

[lfritcher]

I have an Access Database that uses data from OBDC links from a Visual FoxPro Database. One of my reports will run once but it hangs up when I try to run it a second time. This report runs from a macro that runs three make table queries, then opens a form for the user to select criteria from drop down list, then opens the report, then deletes the first make table. I need this report to run multiple times, however it only runs once and then it hangs up and I have to end task from the windows task manager. Anyone have any suggestions?

[GARYPSWANSON]

A couple of things that you might want to review:

1. In your Macro, you open queries and form(s). Are the queries and forms being closed after they are opened? Your code might hang if you try to open a query or form that is still open.

2. You indicate that the macro deletes the first make table (I am assuming the table is deleted.) When you run the report a second time, is the first make table made in sequence such that the table is created again before it is referenced or requeried?

It sounds as if something is still open or missing when you try to re-run the report.



HTH

[lfritcher]

Gary,

First of all, thank you for getting back to me, this has been giving fits for a while now... very frustrating. Yes everything is being closed after they are opened. And yes the make table that is deleted is the first one to be created after the report is run for a second time. I think I might have come up with a solution though. I added import commands to my macro so that the ODBC links are actually being made into static tables and the queries are now pulling from these static tables. The report takes longer to run, however it doesn't hang up on me. I was wondering if you knew anything about ODBC connectivity times (connect and disconnect). Maybe before I wasn't fully disconnected before I was trying to run the report again? Now with the importing commands added into the macro, maybe I am giving the system enough time to disconnect and reconnect. What do you think.

[GARYPSWANSON]

Typically, I do what you are suggesting using the Transfer Database command to transfer tables into access and querying against them as I have run into issues connecting to other applications with ODBC. Creating linked tables may also cause significant degradation in the run times of the queries as well.

The times to transfer the database will really depend on your computer / network setup and the speed of the lines between the systems. For example, our IT department just changed the server we are using to a, very fast (I cant remember the speed) dual processor and my ODBC connection / transfer database dropped from 15 minutes to 3 minutes to download data. Now using the same server, If I download from a remote location, the time increases to about 3 hours. (Again - the speed of the line transferring the data is much slower.)

What application are your transferring data from? Does your routine transfer the data each time the report is run or perhaps there are static tables that only need to be downloaded when the database is opened instead of when the report is run. (Saves on run-time to load data when creating the report.) Just some ideas.

HTH