Upsizing Access to SQLServer

[LynnEsther]

Hello. I recently inherited a very sick MS Access 2000 database that crashes several times per day. It is ~60 MB in size and has about 30 users in there reading/writing at any given time. We are considering upsizing to SQLServer and keeping the Access front end (linking to the SQLServer tables). My question is: is it worthwhile to move to SQLServer yet still keep the Access front end? Will the performance issues go away based on where the data is stored, or are the issues related to the Access front-end?

When I say the Access database crashes, here is what I mean: A user will receive the following error message (happens randomly while they are in the db): "2004 - There isn't enough memory to perform this operation. Close unneeded programs and try the operation again." After that, other users, upon opening the db for the first time, will get: "This database is in an unrecognized format." Then all the forms, reports, macros, and modules disappear from the database window (tables and queries remain intact). It's the weirdest thing.

Thanks.

 

[pbaldy]

The first question would be is the application split? In other words, does each user have a mdb on their computer that has forms, reports, etc in it, and each of those points to an mdb on a server that has the data tables in it? If not, that is likely the source of your problems.

http://www.granite.ab.ca/access/splitapp/index.htm

 

[LynnEsther]

No, the database is not split. Everything resides in one massive file. I will try splitting first and see if that resolves things.

 

[GaryPanic]

thats your problem then -

Make a copy first before you split -

then split the d/b
front end on each users desk


if you get and glitches - on the data - copy over to a local PC then make a copy and compact and repair - don't do it over a network -

also import data into a new file if you compact/repair doesn't work (again do it on a local PC not over a network )

 

[Steve R.]

As an additional observation. We have a database that is split over five locations that are each in excess of 80 miles from each other. We originally had the database on a remote server. It initially worked fine, until the database grew. Then it became clunky. We migrated it to Microsoft SQLSever and it is working just fine.

 

[LynnEsther]

Did you keep the front end in Access?

 

[Steve R.]

Did you keep the front end in Access?

The front end is in Access 2007.