Front and Back End Databases Slow after Migration to Windows 7

[Pierkes]

Hi,

i run a front end/back end database for a few years now. It worked fine until the latest update of our system in the office.

We upgraded to windwows 7, office 2010 and vpn through Juniper.


now, starting the front end takes 10-15 minutes instead of the 15-20 seconds prior to the migration.

any ideas to speed up my app?

thanks very much for replying to me!
Pierre

[zmbd]

1) Upgrading Win7 and Juniper-VPN
VPN isn't going to be your friend here:

So is this just Access, or are you seeing this with more than one application (especially Outlook, Internet connections, etc...)

The reason I ask is that there are settings in Group Policy that can cause you issues (I'm not a heavy IT guy so you'll have to have your local IT check), You may also be having issues with the VPN itself Juniper Networks:[SSL VPN] Users experienced slow performance when accessing Windows file share via Core mode

The VPN model may be taking a lot of bandwidth for each database record lock-read-write.

There could be an issue with the transport - TCP/UDP vs how the data is being sent.

Then, there's the actual ISP connection.

2) Upgraded to office 2010
You may have to look at moving from the MDB files to ACCDB.
However, what I've read, is that there are often issues with using the original MDB file with ACC2007/2010 that are often solved by creating a new MDB file in the new version of Access and then importing the old elements into the new file. You need to do this for both the frontend and backends. This would be one of my first steps.
Keep in mind, if you are using user level security in the MDB file, this can be corrupted during the move and worse, going to the newer ACCDB format strips all user level security. -- BACK UP YOUR FILES BEFORE YOU DO ANYTHING! --

3) Database design.
Over networks when we were using a slower infrastructure, what I found helped:

Numbers transfer faster than text (I'll use a numeric primary key for all but the most basic of needs).

Single field PK are better than compound field PK

Queries shouldn't be written as Select * ....
when you only need one or two fields from the table.

Late bind the recordset to your forms.

Anything that is static or mostly static should be in the frontend. When I used the wizard, all of my tables were sent to the backend; however, I found that this wasn't always the best... I'll rarely update the state table and it is the same for everyone, that was pushed back to the front end...
I have various schemes for other tables that are large, need to be updated; however, maybe just once a quarter or once a year. I either push out a new MDE file or a trigger code to update the local front end tables (or, I've actually had the code create another DB at the Client end and transfer information from the server to the client pc during the initial setup (then update if needed), you can have multiple connections from a front-end (^_^).

A vast majority of my form level comboboxes/lookups have embeded SQL instead of directly at the table.

Keep in mind that Access thinks it is the server; thus, each user has the "server" on their client PC. Therefor, usually, the more "static" type information you have the the client end the better.

4) Clean the client PC.
Rid it of all, un-needed, cookies.
Rid if of all the flash cookies! (Popular Mechanics:(Sept ember 23, 2010) What Are Flash Cookies and How Can You Stop Them?
Rid it of all un-needed messaging, social media, apps, and games. The first few are obvious; however, anymore somethning as simple as the age old ms-minesweeper wants to call home and tell someone something about you, your PC, and what you've been up to. The VPN is already going to hog the bandwidth, why let anything else have a chance!

Hope this helps... and I'm sure I've missed something.

[Pierkes]

Thanks zmdb!

I will have my it service unit tak a look at the various possibilities and wil come back to you with the best solution (if they can find it...)

Best regards,
Pierre