I'm at 2gb help me!

[vandido626]

My database has reached the 2gb limit. I have already compacted the database and splitting it is not an option. What else can I do? Is there a third party programs that will help you create Access files over this limit?

Thanks in advance

 

[KenHigg]

It's hard to imagine a 2g access db that doesn't have records that can be archived...

 

[ghudson]

I believe that Access 2003 does not have the 2 gigabyte limitation.

 

[ScottGem]

My database has reached the 2gb limit. I have already compacted the database and splitting it is not an option. What else can I do? Is there a third party programs that will help you create Access files over this limit?

Thanks in advance

Why is splitting not an option? ANY multi-user database NEEDS to be split. If your database is growing that big, it can be split into several files to get around the 2G limit.

 

[boblarson]

Sorry, 2Gb IS the limit for Access (2003 also). But, you can have a separate database for each table and link them together as linked tables in your frontend, thereby getting around the space requirements.

 

[jeremie_ingram]

Would the upsizing utility be an option. If you have reached the limit and are still going, I highly recommend using the right tool for the job. Upsize to SQL and develop the FE in Access.

 

[boblarson]

IF they have SQL Server (which costs quite a bit, mind you), it isn't as simple as upsizing. There are several considerations to be made.

1. Do you need to use any local "temp" tables. If so, you can't use an Access Data Project (ADP). You would need an MDB for that. However, if you use an MDB, there are several things that you need to program in manually to ensure things operate properly. Error handling is of critical importance because, if not done right, it can look like your data is being updated when, in fact, it isn't.

2. Moving to SQL Server will require moving to Stored Procedures for parameterized queries. While not a bad thing, it is a little more complex than just writing a query in the Access QBE grid and adding criteria.

3. Another consideration, is do they have a SQL Server Administrator? If not, that is something that does need to happen, whether it is someone who is doing it as part of their other job, a contractor, contracted help through a support company, or as a full-time employee.


There are more things to be aware of when going to SQL Server. I'm definitely NOT saying that they shouldn't do it, but it is not necessarily a quick and easy fix for the problem that they are experiencing.

I recommend reading the book "Microsoft Access Developer's Guide to SQL Server" by Mary Chipman and Andy Baron, if you want a good resource on this subject.

 

[vandido626]

The database is the backend for a home grown VB program. The initial program desing was flawed. It was done about 10 years ago and at that time the programer did see us ever reaching the 2gb limit.

Splitting the DB can lead to problems for a number of reasons. The DB is connected to several offsite locations in a 100 mile radius. These machine replicate data on a nightly basis to the DB via hard code. The DB is also connected to several reporing programs. If I split the DB i might run into functionality problems.

We plan to move the files over to Oracle or SQL in the NEAR future. However, my companies definition of "near" differs a bit. It requires a mount load of approvals and budget analysis. So the quick upgrade is not so quick.

I'm just trying to squeeze a few more months of life out of Access until we upgrade the DB.

 

[boblarson]

Sorry for the bad news, but you aren't going to squeeze anything out of it unless you do some changes somehow. So, it will either be making the change to separate some of the tables into other databases and then link them (figuring out how to replicate them). Or, it will be moving to Oracle or SQL Server.

Your process is, at this point, essentially dead. So, you may be able to claim "emergency" on this and get some help moving it up the line. If not, the people who are using this, will likely not be able to, until something happens. Not fun, not nice, but that's the way it is.

 

[jeremie_ingram]

Could you possibly build a replication of the table in another database and then use it to archieve older records? At least enough to get you a little time out of it. You wouldnt be deleting them, merely relocating them in a safe and consistent environment.

 

[vandido626]

Yea, it appears that I will have to distribute the data someway in order to bring down the size of the DB. I was just hoping for a tool that would allow me to exand the size of DB would be available.

 

[ScottGem]

Well no tool exists, but this may not be as bad as you think. What I would try is the following:

1) make a backup of the current MDB file and store in another location.
2) split the MDB into one or more MDBs (you can decide a logical grouping of tables
3) create a blank MDB with the same name as the original MDB. put links to all the tables in the split MDBs into this one MDB.
4) test the app to see if the VB FE and the replication works.

If that works, you will have bought yourself some time.

If the replication doesn't work, check to see what tables are being replicated. Maybe you can fit just those as local tables and have everything else linked.

 

[vandido626]

Well no tool exists, but this may not be as bad as you think. What I would try is the following:

1) make a backup of the current MDB file and store in another location.
2) split the MDB into one or more MDBs (you can decide a logical grouping of tables
3) create a blank MDB with the same name as the original MDB. put links to all the tables in the split MDBs into this one MDB.
4) test the app to see if the VB FE and the replication works.

If that works, you will have bought yourself some time.

If the replication doesn't work, check to see what tables are being replicated. Maybe you can fit just those as local tables and have everything else linked.

Nice and easy plan, I like the sound of it. I will try it in my testing enviroment.

 

[ScottGem]

good luck, let us know how it works