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

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

have you tried doing a compact and repair of the BE?

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$

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

Have you tried compacting the back end database?

WOOPS... didn't see cap already suggested that.

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.

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

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.

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.

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

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

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.

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

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.