CODEWITHSUNDEEP
sql
oldcat
oldcat

Reputation: 11

How to minimize bloating Access database having queries and Macros, no VBA code?

I am new to Access, I am a C programmer who also worked with Oracle. Now I am creating an Access database for a small business with Access front-end and SQL Server back-end. My database has about 30 small tables (a few hundreds records each) and a rather complicated algorithm.

I don't use VBA code because I don't have time for learning VBA, but I can write complicated SQL statements, so I use a lot of queries and macros.

I am trying to minimize the daily growth of my database. I've thought about splitting the Access DB. It doesn't make sense because my DB is rather small. After compacting its size is about 5 MB. The regular compact procedure is not convenient because my client's employees work from home any time they wish. So I need to create a DB that would bloat as slowly as possible.

I did some research and found a useful info: "the most common causes of db bloat are over-use of temporary tables and over-use of non-querydef SQL" (http://www.access-programmers.co.uk/forums/showthread.php?t=48759). Could somebody please clarify that for me? I have 3 questions about that:

1) I cannot help using temporary tables, I tried re-using the same table names in 2 ways: a) first clear all records and then run an append query or b) first run a Macro command "DeleteObject" (to free the space in full) and then re-create the temporary table. Can somebody please advise which way is better in order to reduce the DB growth?

2)After running a stored query I cannot free the space like I did in C using VBA statement "query.close" (because I don't use VBA). But I can run Macro command "close query" after each "OpenQuery". Will it help or just double the length of my Macros?

3)Is it correct that I shouldn't use Macro commands RunSQL for simple SQL statements and create stored queries instead? Even though it will create additional stored queries.

Any help would be appreciated!

Upvotes: 1

Views: 1448

Answers (1)

Tim
Tim

Reputation: 766

Ah the joys of going back to lego after being a brickie! :)

1) Access is essentially a text-based file system. When you delete a record or a table, is persists in the file but with a flag which marks it to be ignored. When you compact an Access db, the executable creates a new file, and moves everything unmarked into that, then deletes the old file. You can see this actually happening if you use Windows Explorer to monitor the folder during this process.

You mention you are using SQL Server, is there a reason you are not building the temp tables on the server? This would be a faster, cleaner and all-round more efficient solution - unless we've missed something. Failing that, you will have to make the move from macros, but truthfully, if you can figure out C, then VBA will be like writing a memo! http://www.access-programmers.co.uk/forums/showthread.php?t=263390

2) issuing close commands for saved queries in Access has no impact on the file-bloat issue, they just look untidy

3) yes, always used saved queries, since this allows Access to compile the SQL in advance, and optimise execution. ps. did you know you can call SQL Server Stored Procs from within an Access saved query? https://accessexperts.com/blog/2011/07/29/sql-server-stored-procedure-guide-for-microsoft-access-part-1/

If at all possible, you should look for ways to dispense with the Access back-end, since you already have SQL Server as the backend - though I suspect you have your reasons for this.

Upvotes: 1

Related Questions