Changing Backend on Bloated Access Database

[clmarks]

I have a very large Access database. It is separated into a front-end which contains the forms, reports, queries, etc., and a back-end which contains only tables.

I am told that the back-end has grown too large for Access to handle it and that we have reached the limit that Access will allow for concurrent users. I have also been told that I should be using SQL (or MySQL) for the back-end. I had our IT person download MySQL because it was free and I could use it to learn the skills I would need.

My understanding was that I would be need to create my tables in MySQL then connect to them with an Access front-end using the ODBC driver. That made sense to me. Now I am being told that all I have to do is connect to my existing Access back-end using the MySQL ODBC driver.

Now I am confused. What is the reality here? I've been working in desktop database applications for years and am very comfortable with Access. But I feel like I'm entering a whole new world.

I've been searching the internet for information and have read that some of the differences between Access and SQL/MySQL (like handling of null fields in a query) may cause problems. My company is completely dependent on this database for it's billing and I can't afford to make a mistake here.

Could someone please explain the situation to me?

Thanks,

Cherry

 

[DCrake]

It may well be that your back end can be further split into several smaller back ends which are then linked to the exiting front end.

I tend to seperate tables based on the activity growth, so tables such as lookup lists that rarely change are kept in one mdb and tables with high activity ar kept in another.

How often have you done your compacts. A tip os to copy if to a local machine before compacting as it may hang due to size/caching limitations.

If MySQL is ilike SQL Server you ned to export/import your tables from/to MySQL then use ODBC to create a DNS link between client and host.

David

 

[shadow9449]

David:

I tend to seperate tables based on the activity growth, so tables such as lookup lists that rarely change are kept in one mdb and tables with high activity ar kept in another.

What I do is to put tables that NEVER change (such as lists of valid data in certain forms) in each front end to keep them local. As far as tables that change rarely (such as price lists), you are suggesting putting them in a separate mdb. What is the benefit of this? Does it improve performance or limit network access? I've never done it because I didn't realize that it could help but I certainly would if someone can explain the benefit.

How often have you done your compacts. A tip os to copy if to a local machine before compacting as it may hang due to size/caching limitations.

Has anyone else noticed that Access 2007 bloats tables REALLY fast to REALLY high sizes compared to previous versions? I have databases that uncompacted are about 25 MB, and used for a few days will bloat to 40 MB and then they get compacted again. The same database using Access 2007 will go WELL over 100 MB in one day of use!

Does anyone else experience this or know why this happens?

SHADOW

 

[clmarks]

The back-end (no pun intended) is about 250 meg. when compacted. We have about 6-7 users who use it all day long and another 2-3 who use it occasionally.

But I still don't understand the logic of not converting the backend to SQL and simply using the SQL ODBC driver. Does this make sense?

The back-end contains a number of small lookup tables that rarely change. But the actual data tables are huge. I can buy myself some time by archiving some of the older data, but we need to keep at least three years of history in the current database. It has been during the last three years that has grown so rapidly so that will be a short-term fix.

My only past experience with SQL was helping to define the requirements for a large Sybase database, and help out occasionally on a lobotomized version or Oracle. In that case the tables were already set up. Both of those situations occurred over 15 years ago.

I've got to figure out what to do about our database and write a proposal for my director. So other than dividing up the back-end and archiving off some of the data, does anyone have any suggestions?

Thanks for your input.

 

[DCrake]

Shadow,

There is no reason why you cannot use a pre 2007 backend with a 2007 front end. This will reduce bloating as you explained. It has to do with 32/64 bit processing and code page sizes.

 

[boblarson]

250 MB is not the problem. The problem may be in what your operations do to bloat the thing. The database can store 2Gb of data (or 8 times as much as it currently has). You can, use more than one Access database for the backend (even as far as 1 database file for each table) but the bloat when then needs to be compacted, is probably caused by operations which could be better optimized. One example is doing make tables instead of clearing data and appending new data. While both cause bloating, the constant creation and destruction of tables can cause more bloating than just filling those containers again and again.

 

[shadow9449]

Shadow,

There is no reason why you cannot use a pre 2007 backend with a 2007 front end. This will reduce bloating as you explained. It has to do with 32/64 bit processing and code page sizes.

No, I always use a .mdb so computers on the network with pre-2007 don't have to replace their version of Access with 2007. As soon as one or more clients on the network access the backend using Access 2007 or Access 2007 Runtime, the backend - even as an .mdb - will bloat like crazy. I'm sure it's a "feature" or "upgrade" of 2007.

SHADOW

 

[rafael]

Hi clmarks,

From what I understand , you want to convert MS Access backends into MySQL and then connected the Access front-end to it.

First to convert the Access tables over to MySQL I used a program called Bullzip http://www.bullzip.com/products/a2m/info.php

Once you have done that, you create an ODBC connection (control panel - admin tools - data sources) to your database. If you are using MySQL, you will need to use the MySQL ODBC Driver, which you can obtain from http://dev.mysql.com/downloads/connector/odbc/
Then in Access, you select 'External Data' tab and select ODBC, select that data source (DSN) you created and then the tables you need.

At this point all your Forms etc should still work as they did before.


In terms of deployment, you have to bare in mind that the DSN you created earlier does not exist on all of your client machines. So you have 2 main options.

1. DSN-less method which you can read about from
http://www.accessmvp.com/DJSteele/DSNLessLinks.html
the only thing you need to change in that code is the connection string so it works with whatever DB type you choose. I believe this is the prefered way to deploy it.

2. Creating a DSN (user or system) programmatically, this is more complex from what I have read, as the coding plays around with the clients registry to create the DSN required for connecting to the tables.

I hope this helps to get you started. Let us know if you need more help, I am fairly new to this also but I have found this forum to be really helpful.

raf