help on troubleshooting performance issues with Access 2010 vs 2003

[sark666]

hello, I'm pretty good with excel vba but have limited knowledge with Access. The developer who coded this is busy with other projects. From time to time I've found a bug or something small that needed to be changed and mudled my way through the code and have fixed it, but again my access knowledge is very limited.

This access is split front end/back end. On the back end are numerous databases for individual offices stored on a remote server and what is installed for the end-user is just the front-end and it links to their appropirate database upon logging in.

There is this daily check that needs to be run once a day when they log in. On access 2003 (and 2000) for the largest office it takes about 40 seconds. However in access 2010 it's taking about 5 minutes. I still have 2003 so it's still fast for me but I have access to a laptop now and I as well am experiencing the 5 minute run-time. On a hunch, I copied the backend mdb file to my local drive and linked to that. It then ran in about 40 seconds.

So in 2003: local or remote same run-time.
2010 local is fine, remote run-time takes a nose dive.

It doesn't seem to be anything wrong with how the network share is mapped in 2010 as I have written a few excel utilities that query all the databases for reports and they run in the same time in excel 2003 vs 2010, so it seems to be access specific.

Again, I know very little about access, is there some general things I could look at that would explain the above?

It seems the front-end is slower all around in 2010 and not just in this daily check, it's just most apparent when this is running. I'm hoping there's something basic to check on why something would be slower remotely vs a local database in 2010.

 

[sark666]

To add to this, I did have an issue with access 2003 if the office folder paths were too long (over 8 characters)

network_share\123456789 would lag over the network
network_share\12345678 would be fine.

I'm wondering if with Access 2010 the network share's name can be affected by this? The network share path is over 8 characters. I have no way of renaming that to test it. I tried putting the drive letter assignment instead for linking so I ended up with Z:\path\to\office\data.mdb but it still had the slow performance.

 

[sark666]

Maybe someone could help if I ask a more specific question. I came across a thread that suggested this:

One little trick is to open a recordset at startup and leave it open until
the database is closed. The recordset doesn't have to contain any records.

Split DB (2007) on network is very slow


I don't know if this is the equivalent but I opened the mdb file prior to running this daily check and it seems to have sped up immensely </SPAN>
</SPAN>

I've fixed the odd bug or two in the access app, but again I'm still very new to access from a development standpoint. Can anyone show a basic example that I may be able to shoe-horn into my existing code of opening (and properly closing when done) a recordset?

It doesn't sound too complicated and I'll search for existing code samples, but thought I'd ask.

Also another user on that same post as above states:

You don't actually need to open a recordset. All that's necessary is
to initialize a database variable pointing to the back end, which
creates the LDB file. My dbLocal() function (which you can find in
Google Groups quite easily, since I've posted it a bazillion times)
accomplishes that transparently, though you do need to de-initialize
it in your app's shutdown routine.

I'm searching for that now, but if anyone can provide what I asked above to see if that works first.