Moving to other backend

[John J.]

I have a split Access database of which the backend is growing rapidly. To
be prepared for the future and to make my database more safe I'm thinking of
moving the backend to antoher kind of backend, for instance SQL server. If
we would have an SQL server expert, would it take much time to move the data
to the other backend? Will it be almost plug and play or do I have to
rewrite my application? My app has about 35 forms, 30 tables, 20 reports. I
know this maybe hard to answer, but I'd like to know what problems I may
face.
Thank you.
John

 

[Jeff Boyce]

John

Several years back the powers-that-be decided that Access wouldn't provide a
sufficiently robust back-end and mandated a move of several in-house
applications to SQL-Server. When they learned that the front-end would be
considerably more work (i.e., re-development), they mandated that the data
move to SQL-Server, rather than the entire applications.

To maintain acceptable response-time, it was necessary to make modifications
to most of the forms I had early had working with Access/JET data. Also,
since SQL-Server views and stored procedures run on SQL-Server, a more
robust server and db engine, it made sense to re-create functionality in
SQL-Server and move many queries over to the new back-end. Not all, but
many...

Since I have no way of knowing how your forms are connected to your data
now, it's a bit tough to come up with an estimate of how much work it will
take.

Perhaps you could migrate a copy of the data over to SQL-Server and begin
testing your existing front-end against the (new) data source ... not a bad
plan at any rate.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John J.]

Thanks Jeff, Chris,
The testing, as always, is a good idea!

 

[Jeff Boyce]

And for folks in the middle, who know more "now" than they did "then", the
migration presents opportunities to incorporate better practices <g>!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Klatuu]

How rapidly is it growing?
How large was it when you started?
How large is it now?
How often to you compact and repair the back end?
Is it possible the backend could be redesigned to put some tables in a
different mdb?
Is it possible the data could be logically split on some entity where
reporting requirements would allow the data to be separated. For example,
we have one application where the reporting never combines data beyond a
client/region level. We have split the data so there are multiple back ends
each specific to a client/region.

It may or may not be necessary to upsize.

 

[John J.]

How rapidly is it growing?

3-4 MB per week

How large is it now?

60 MB

How often to you compact and repair the back end?

Every day.
I read the max size of the BE is 2GB but I can imagine performance wil drop
already when you're dealing with a 1GB mdb. Or does Acces still perform fast
then? I'm especially afraid of searching through memo fields.

Is it possible the backend could be redesigned to put some tables in a
different mdb?

That might be a solution. I'll look into that.

Is it possible the data could be logically split on some entity where
reporting requirements would allow the data to be separated. For example,
we have one application where the reporting never combines data beyond a
client/region level. We have split the data so there are multiple back
ends each specific to a client/region.

It may or may not be necessary to upsize.

Thanks for your comments.
John

 

[Klatuu]

Performance is less dependant on mdb size than on a well normalized
application with good indexing. It also helps performace to compact and
repair regularly. We have some running that are as large as
799.22 MB that perform well. All our mdbs (we have over 500) are compacted
every 60 days, but how often depends on the usage.

It doesn't appear to me you have any real concern.

There is one issue you need to be aware of (I had a misunderstanding on this
point for a long time). If you have compact on close set for the back end,
it will not execute. It only executes when you open and close an mdb
manually. In addition, I have heard of problems with compact on close. I
have not experienced any, but have heard it is best not to use it.

 

[John J.]

Thanks for this practical info.

Btw: for compacting the BE I use a VBA procedure which runs every morning
when the first users logs in. A lockfile prevents other users from logging
in when compacting is being done.

 

[Klatuu]

Thats a good plan.
I use an mdb with no interace, only VBA code that launches from an autoexec
macro that runs from the Windows scheduler.
So we don't have to keep a work station up 24X7, we have Access on the
server and it runs from there