CODEWITHSUNDEEP
vb6ms-access-2007database-connection
DJ0073
DJ0073

Reputation: 43

MS - Access 2007 database operations and MDB file size

I have a legacy vb6 monolithic application which reads/writes/deletes records from Access database using ADO library functions. There are many connections which are made in multiple functions to a single database:

Despite the fact that the data in the DB is not too large, the Access MDB file grows rapidly leading to hit the 2 GB mark. The only way out for me then is to stop the application, compact and repair the DB and then restart the app. Does creating too many connections from the application cause this issue? What is the way to resolve this?

Upvotes: 0

Views: 148

Answers (1)

joehanna
joehanna

Reputation: 1489

As per @plutonix, data manipulation operations expand the DB. What do you call "not too large". An MDB >100MB is very large!

It sounds like it's time to scale out your application to use Linked Tables and store the data in an SQL Server instance (Express is free). This leaves only your application logic in MS Access. Doing this requires minimal effort and you get to keep almost all of your code without touching it.

https://support.office.com/en-sg/article/Import-or-link-to-SQL-Server-data-a5a3b4eb-57b9-45a0-b732-77bc6089b84e

Upvotes: 1

Related Questions