CODEWITHSUNDEEP
sqldatabasems-accessms-access-2007
jerry
jerry

Reputation: 2789

MS Access 2007 Performance Issues

I am building a database is Access 2007, and we don't even have any data yet but the database is constantly freezing. I used the built in performance checker, and it said everything was fine, but I am worried that the database will be unusably slow if I don't fix it soon.

Here is why I think it may be slow.

  1. We have 300+ queries saved in the database, all of which need to run weekly.

  2. We have 4 main reports and a sub report for nearly all of the queries above. Why? Because the 4 main reports need information from all of the queries, and we are using sub reports as the source.

  3. A few of our queries are pulling information from at least 15 other sub queries.

Other than this, I don't know why it could be slow, unless it's just my computer. Could someone pleas give me some insight about what might be wrong, how I might improve our database's performance, and if this amount of queries and sub reports is abnormally high.

Thanks,

Upvotes: 0

Views: 556

Answers (1)

Albert D. Kallal
Albert D. Kallal

Reputation: 49264

Links to tables on network share, or even a default printer that is part of the network can cause many delays. One often used solution is to keep open (force) a persistent connection. During development you can simply in the front end open any linked table (one that is linked to the back end), and then minimize it. This often will solve those delays. The list of other things to check can be found here:

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

If the above persistent connection works, you also want to ensure in your startup code you open a connection to the back end to a global database var, or perhaps open up a table to a global reocrdset.

Upvotes: 1

Related Questions