Memory problems with Access

[suezq2]

I have a problem with the Access 2000 database that I created. It grows at a very rapid pace (@5 mb a day). The back end is a SQL Server database and I mostly use pass-thru queries to get the data to the Access front end. I also seem to get an error at times that is a "Copy to Clipboard". Could this have something to do with the abnormal growth. This is the second database my company has created (The first one being in Access 97) and the other one doesn't grow as fast. Someone told me that it could be the queries that do not get completely deleted from the system. Could this happen with pass-thru's too? Please Help!

Sue

 

[Francescina]

There is a quite complete article on the memory problem in the ACCESS ONLINE ENCYCLOPEDIA (

http://www.unsoftwareag.com)

that you should have a look at.

 

[Kdogg]

You might want to upsize to an Access Project. Remember however you have a capacity level of 2GB per DB after which after which you need to find a different Front End

 

[suezq2]

Thanks for the help!

 

[rbowes]

Are you holding some of the data in temp tables in the front end? If you are, drop the temp tables as soon as you can and make sure that you compact on close. One thing you might try, and this is only if you wish to revisit how your mdb works, is to scrap your queries and use the sql statements as sources for recordsets. This might add some weight to your forms and reports, but it gets rid of the queries (which can be weighty themselves) and it allows you to clear out the MSysQueries table which holds query information. If you're using SQL Server as a backend, you can also speed up performance with stored procs.

I am in a similar situation with an access front end and sql server backend. The only saved queries I use are for crosstabs - transact sql in sql server does not understand the Transform keyword - and they are based on temp tables.


Hope that helps.

Bob