Access2003 - Linked Tables - Very Slow 2

[GOstlund]

I've got an Access 2003 database which works fairly well when all combined as one mdb file, but it's nearly time for data entry to begin and I've still got work to do. So I saved all the tables to a seperate file and then brought them in as linked tables, but as soon as I do that my VBA app becomes very slow to bring up data.
Now, this could be a result of network lag, however I have trouble believing it could be affecting it this much. The only thing I can consider is the fact that all data interaction is handled via code using a great deal of SQL. I am concerned that this is going to be a "Access isn't meant to be an SQL engine" type of problem, but it's really my only option as it is meant to be a single file "application". The only reason I have the linked tables is that I need the ability to continue development on a seperate copy of the file to finish some sections while people are working on what will be the live database.
I suppose I could just import their data into my copy of the database once I'm ready to move it over, but I'd rather use linked tables if I can. If anyone has suggestions as to possible solutions to the speed issue, or other ways I might be able to "link" the data which might be faster, it would be most appreciated.

Thanks,
Gavin

 

[lupins46]

You can look here for lots of tips on performance:

http://www.granite.ab.ca/access/performancefaq.htm

 

[GOstlund]

Having read through all the suggestions on that page, I'm not sure that any of those will help me. For one, the only things on there that deal with linked tables are an issue where the linked table doesn't exist anymore, an issue with refreshing, and an issue datasheet bindings to linked tables. So, none of those solutions apply and there weren't any other suggestions on there which would help/apply in this situation.
I appreciate all the help I can get on here, I'm not sure though if I was clear. Nothing in my project is "bound" to a particular data source, it's all aquired through
Set OBJ.RecordSet = DB.OpenRecordSet("SQL HERE")
The data entry stuff is handled by manual SQL inserts, but that is not an issue, those are fast. The problem I'm having is in pulling data from the tables, it queries five tables in succession getting data from each, then it finally queries two tables, which in turn provide values needed to grab data from ten more tables in succession. Hopefully that at least made some sense... I'm aware that this "appears" convoluted and unnecessary, but I know the importance of joins and am using them as well in other ares. I can't explain it in detail as I'm sure all professionals out there understand.

TIA,
Gavin

 

[HappyRed]

I have a 30 PC network with Windows 2003 hosting SQL 2000 as well as 2 Access 2003 Applications with linked tables.

Speed is something I have wanted to improved since day one.

Unfortunately teh SQL client-server App is not mine but the Access is.

I have seen times when the table look ups drag to their knees but it seems to be like for waves. Then it fixes itself.

Try Tools - Compact and repair and I think you are going to see a difference right away but I don't know for how long it will stay.

I guess this one is really worth the $ 150.00 call to Microsoft.

 

[willir]

Gavin

Have you turned off AutoCorrect??? By default, the autocorrect feature is turned on. With BE/FE, you have to turn it off...
"Tools" -> "Options" -> "General" tab

Other things
- Keep the name of the backend short, no more than 8 characters, and near the root of the mapped drive. These are know issues documented at the Microsoft site.
- If you have a large database, then you may look at strategies of minimizing network traffic. When Access loads a form, by default, it will load the entire table or query that is used to create the form, plus data for the combo and list boxes. Instead, just load a minimal amount of data, and then retrieve the rest of the information as requied for each record.

HappyRed
A common problem between Access and an SQL server is the ODBC database connection having to be re-established. The lag or performance problem can be the time it takes to re-establish the connection to the database, security verification, etc. Open a simple form that connects to a small, unimportant table, and hide the form and keep the form loaded for the entire session. Since the form is open and connected to the server database, ODBC will not drop.

Richard

 

[HappyRed]

Thanks a lot Willir.

Why or when the ODBC drops off. Is there a timer?

Something that I have notized but I have not been to find additional information is the protocol. You can connect to SQL via TCP or name pipes. For whatever reason TCP won't work most of the time but names pipes will always work. However name pipes is not as fast. Do you know anything about this?

 

[willir]

It depends...

In Access XP, Tools -> Options -> Advanced, a refresh interval can be reset.

Under the ODBC client configuration (system32\odbcadm32.exe), you may be able to control over options. One thing that may be useful is to take advantage of any logging ability. Note that different server platforms will have different ODBC drivers.

Then there are other things to consider... Server security, database configuration, and look for an INI file which may also have settings that can be tweaked.

I am familiar with my work environment; chances are your work environment is different, so you may want to talk to the DBA and senior IT techs.

Richard

 

[Tranman]

Gavin,
When you say:
Set OBJ.RecordSet = DB.OpenRecordSet("SQL HERE")

In the "SQL HERE" part, you might achieve some efficiencies by playing with the "opening" and "locking" parts of the message, like this:

rs.Open "Select * from cust",currentproject.Connection,adOpenStatic,adLockReadOnly

in order to minimize overhead in the connection.

Tranman

 

[misscrf]

Willir, thanks a bunch for the post! I turned off auto-correct, compact and repaired again, and it shortened the lag by a noticeable amount. I am going to go decrease the name of the back_end to less then 8 char.

How do I set the amount of data to initially load?
What will this do to the end user who goes right to search for any given record?
Thanks!


misscrf

It is never too late to become what you could have been ~ George Eliot