Solved

How can I avoid the 2GB limit of Access and Windows XP

Posted on 2011-02-23
21
1,467 Views
Last Modified: 2012-05-11
I have a split Microsoft Access database which has a backend mdb file which is growing in size every day and now 1bout 1.5GB.  I believe that I heard there is a 2GB limit and am concerned that I may be getting too close to this limit.

The backend MDB file can not easily be split into separate files because I need to query it and include all records that match my criteria in a single query.

My server is Windows 2000 and the work stations are XP Professional.  All workstations have Access 2000 installed and the development has always been done on an Access 2000 machine.

I suspect I will soon be forced to create a backend database on a different platform and if this is so, would like to know which platfrom to use and what I will need to convert my Access MDB file.

I am also aware that 64Bit machines have enhanced capablities and wonder whether all I need to do is buy a new computer to host the mdb file, allowing me to keep the access mdb file as-is.

I'm asking for advice what to do?  I have not kept up with other database technologies.

Thanks

0
Comment
Question by:pcalabria
  • 5
  • 4
  • 3
  • +5
21 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 34963417
How many tables in the back-end database?  You can break apart the back-end database into many back-end databases and still link the individual tables in the front-end application.
OM Gang
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34963429
have you tried doing a compact and repair of the BE?
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 50 total points
ID: 34963439
Moving to 64 bit won't help.

You can actually have multiple back end files.  If you re-distribute the tables to say 3-4 backend files, how big would they get?
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 50 total points
ID: 34963441
Yes the limit is 2GB ... and you are approaching that limit ... which is like approaching a Black Hole.

Since you cannot split into multiple mdb's, then you will need a new back end platform ... suggesting SQL Server.

AFAIK ..., 64 bit Office/Access  has the same 2GB limitation.  And in general, 64 bit Office is not recommended at this time.

mx
0
 
LVL 28

Assisted Solution

by:omgang
omgang earned 50 total points
ID: 34963452
more:
You can have a single query in your front-end application querying multiple linked tables from multiple databases.

If you anticipate contrinued growth of your data you may want to look into upsizing the backend databases to MS SQL or similar.

Also, have you compacted/repaired the backend database files?
OM Gang
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 34963454
You can convert to SQL Server Express 2008 sp2 which has a 10GB database size limit and use linked tabled which while allowing no performance gain would increase db size greatly. You can use SQL Server Migration Assistant for Access which is a free download from M$
0
 
LVL 75
ID: 34963462
Also ... one note about splitting into multiple db's:  IF ... you decide to do so, you *cannot* create Relationships between tables across multiple db's ... if that is important to you.

mx
0
 
LVL 10

Expert Comment

by:VTKegan
ID: 34963464
Have you tried compacting the back end database?
0
 
LVL 10

Expert Comment

by:VTKegan
ID: 34963480
WOOPS... didn't see cap already suggested that.
0
 
LVL 6

Assisted Solution

by:dr_linux
dr_linux earned 50 total points
ID: 34963594
Using Access you'll always be stuck with that 2GB limit.  Splitting up the database only buys you time up to a point, then becomes a beast to maintain.  With a fast growing database as you have indicated, you best bet isn't as easy as just a new computer.  My advice would be to implement SQL Server if at all possible.  This will involve learning about SQL server the migrating the data over to it, which is pretty easy since it is a Microsoft product.  If that isn't an option, well the 2GB limit isn't going to go away.  There may be some other cheaper or free databases that you can convert to, but that 2GB limit is a good GOTCHA.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:pcalabria
ID: 34966718
Thank you all, I'll try to answer all the questions.

omgang and Capacorn1,
I currently have eight different mdb files as the backend, and yes, I actually use the compact and repair feature daily.

I do believe that any attempt to split the table into two mdb files will become a beast to maintain, as menitioned by dr linux.

As far as using a different platform, I have heard a lot about upsizing to SQL, but I am confused.

DatabaseMX, you suggested SQL Server, and omgang suggested MS SQL, and jmoss11 suggested  SQL Server Express 2008 sp2.  Are these all different products?

If I upsize, will I be able to keep the other seven mdb files running under Access and only upsize the one table to SQL Server, MS SQL, or SQL Server Express?

Also, what other thing(s) would I need to purchase before getting started?

... and finally, once upsized, what would the new sze limit be?

Your help is appreciated.


0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 50 total points
ID: 34966770
0
 
LVL 18

Accepted Solution

by:
jmoss111 earned 250 total points
ID: 34966805
SQL Server Express sp2 is free and has max db size of 10 GB, while SQL Server Express has a max db size of 4 GB both are free. Also free is SQL Server Migration Assistant for Access which will migrate your tables to SQL Server and link them if you wish. Other version of SQL Server are pay for play.


Basically all mention of MS SQL, SQL Server and SQL Server Express are basically one product.

A workstation with XP Pro or better with a couple gigs of RAM would most likely do. Howmany users?
0
 
LVL 75
ID: 34967137
Since SQL Express / Sp2 both have max sizes, seems like a dead end.  So ... SQL Server would seem to be the answer.  Of course, any scenario involving SQL Server is likely to be more expensive that one involving Access as a Back End.

If your C&R'd mdb is really at 1.5GB ... you are approaching the black hole ... soon. Time to move on.

mx
0
 
LVL 6

Expert Comment

by:dr_linux
ID: 34969559
While you are developing you can use the Development Edition of SQL Server 2008.  It is VERY cheap and will let you start to develop and figure out if it is going to work for you.  It seems the cost for the Developer edition is around $50.  The Development Edition of SQL Server 2008 allows you to use it for NON-Commercial use.  So develop away!  When  you feel it is time to go live, you'll need to purchase the commercial use version.  SQL Server 2008 Standard should be fine for what you are wanting to do.  And look at the Server license for around $870 (I think).  Then buy the cals for the number of users needing to connect.  Unless you have tons of users this would be the best licensing route to take.

BTW the developer edition has the FULL SQL Server 2008 Enterprise functionality.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 34971054
SQL Server Express costs nothing and 1.5 GB on Access equates to maybe .5 GB on SQL Server. After 10 GB  with SQL Express, you're looking at spending bux.

But 10 GB with SQL Server Express would equate to about 30 - 40 GB in Access if Access could do more than 2 GB.

0
 
LVL 18

Expert Comment

by:jmoss111
ID: 34971079
Also, by the time SQL Express 2011 comes around, the max limit may creep up from 10 GB to ???.
0
 

Author Comment

by:pcalabria
ID: 35007652
Hello,

I just wanted to get back to everyone who has been helping to let all know that I'm still researching these options in order to make my decision.

I'm still not clear as to what upsizing to SQL exactly means as I currently have a single front end file with a handful of backend files.  Currently, 10-12 users run the front end from their XP Pro workstations.  Microsoft Access 2000 is installed on all of the workstations.  When I make a change to the front end, I compile and deploy a new MDE file to each workstation. The backend files are on a Windows 2000 Server and are rarely changed because they only contain data and the tables are already setup.  The file which is approaching the black hole is a mdb file stored on the server.

Can someone explain what will change once I upsize?  I really don't understand the process.  Very important, will I need to install the new software on the server, the development machine, or all the workstations?

I'm hoping the process is to simply install new software on my development machine that will convert the single back end table that I want to change to SQL format, and then link it to my Access front end.  Is this what actually happens?

Very important, will I have to make any changes to the software on the workstations other than distribute a new MDE file which has changes made?

Thanks
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 35013098
the sql server should reside on a stand alone machine; you can link the tables in the front end to the sql server just like the Access backend. Without any other changes you'll be able to take advantage of the larger data store.
0
 

Author Comment

by:pcalabria
ID: 35107646
Thanks everyone.

Sorry its taken so long to respond but there are a lot of considerations and other issues at this time.

I made some changes to temporarily buy time if I stick with Access for a while, but this sounds like the best path to take at this time will be the solution suggested by jmoss.

My plan it to start learning about SQL Server Express by downloading it to my workstation, to see what I'm getting in for!  - Although I don't need another project at this time  :-(  LOL

I've decided to split the points as so many of you provided advice and information that was helpful in make the decisions.

Thanks
0
 

Author Closing Comment

by:pcalabria
ID: 35107692
Lots of info was provided here by lots of experts, and I wasn't sure what to do with the points.  I decided to give half of the points to the expert who suggested the route that I plan to take, and split the other half amoung the experts who provided the most helpful advice.

Hope this is was the right thing to do. Thanks.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now