Linked SQL Server Back End?

[Sheldon Penner]

After years of use, the back end mdb file of my client's Access application
has grown to a humungous half-a-gigabyte. The app still runs reliably, but
the most recent attempts to "Compact and Repair" the backend file have
failed, probably because the file server lacks sufficient blank disk space.

The application uses vba extensively in its queries, so converting to an adp
project would be an extensive and costly undertaking that my client is not
ready for at this time.

I am considering, instead, exporting the Access tables into a SQL Server
database, then linking to it from the Access front end. Is this a sensible
solution?

I would appreciate any advice and comments on how to deal with this situation.

Thanks.

Sheldon

 

[Roger Carlson]

Yes, SQL Server is a fine, long-term solution. I encourage you to pursue
it.

However, in the short term, you might try copying the Back-End database down
to your local drive, compact it there, and then copy it back up. My
experience is that this works better than compacting a large database on the
network. It's faster too.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Albert D.Kallal]

the most recent attempts to "Compact and Repair" the backend file have

failed, probably because the file server lacks sufficient blank disk
space.

ouch!!!

I would create a blank database..and try impoting. You realy need to find
out how large the file is

I seen 500 meg files after compatcing come down to 15 megs. You sound like
you know what you are doing here..and obilairy you know aobut compacing.
however, it would be intersing to see how large the file is fater a
compacnt).

Perhaps you copy to your pc and comact...

I am considering, instead, exporting the Access tables into a SQL Server
database, then linking to it from the Access front end. Is this a
sensible
solution?

yes, it is great idea, and this is the pefferced apprahc to migratte the
back end ot sql server.

Note that the aporoch is simply one of putting out fires!

What do I mean by fires?

Well, you move hte data to sql srever, and get all of the linked tables
working. Remember, for each table, you need a primary key, and ALSO a
timestamp field (ms-acces uses the time stamp field to figure up updates).

Asimong you move data...link the tables. At his point, about 90% or more of
your appcation will work. In fact, oftne even more then that. Howver, some
things will run slow as a dog. So, those are the fires I talked about. You
simply address the things that don't perform well.

Forms that are based on queryes with more then one table should be repaced
with a view. (they perfoerm WAY better). And, the same goes for combo boxes.
Remove the sql from the combo box...build a view..and in the combo boxes
souce put ONLY the name of the view

eg;

vCboCustomer

This speeds up the combo box signcility on a form.

Here is a few more tips:

** Ask the user what they need before you load a form!

The above is so simple, but so often I see the above concept ignored.
For example, when you walk up to a instant teller machine, does it
download every account number and THEN ASK YOU what you want to do? In
access, it is downright silly to open up form attached to a table WITHOUT
FIRST asking the user what they want! So, if it is a customer invoice, get
the invoice number, and then load up the form with the ONE record (how can
one record be slow!). When done editing the record...the form is closed, and
you are back to the prompt ready to do battle with the next customer. You
can read up on how this "flow" of a good user interface works here (and this
applies to both JET, or sql server applications):

http://www.members.shaw.ca/AlbertKallal/Search/index.html

My only point here is restrict the form to only the ONE record the user
needs. Of course, sub-forms, and details records don't apply to this rule,
but I am always dismayed how often a developer builds a nice form, attaches
it to a large table, and then opens it..and the throws this form attached to
some huge table..and then tells the users to go have at and have fun. Don't
we have any kind of concern for those poor users? Often, the user will not
even know how to search for something ! (so, prompt, and asking the user
also makes a HUGE leap forward in usability. And, the big bonus is reduced
network traffic too!...Gosh...better and faster, and less network
traffic....what more do we want!).

So, how well your application migrates to sql server really depends on how
now you restricted the forms to the needed records only.

There is many more tips...here few articles...

http://support.microsoft.com/default.aspx?scid=kb;en-us;175619&Product=acc

ACC2000: "Access 2000 Upsizing Tools" White Paper Available in Download
Center
http://support.microsoft.com/?id=241743

ACC2002: "Access 2002 Upsizing Tools" White Paper Available in Download
Center
http://support.microsoft.com/?id=294407

ACC2000: Optimizing for Client/Server Performance (odbc)
http://support.microsoft.com/?id=208858

ACC: "Upsizing to Microsoft SQL Server" White Paper Available in Download
Center (a95, and a97)
http://support.microsoft.com/?id=175619

HOW TO: Convert an Access Database to SQL Server (a97,a2000)
http://support.microsoft.com/?id=237980

ACC: Tips for Optimizing Queries on Attached SQL Tables
http://support.microsoft.com/?id=99321

 

[Tony Toews]

After years of use, the back end mdb file of my client's Access application
has grown to a humungous half-a-gigabyte. The app still runs reliably, but
the most recent attempts to "Compact and Repair" the backend file have
failed, probably because the file server lacks sufficient blank disk space.

The application uses vba extensively in its queries, so converting to an adp
project would be an extensive and costly undertaking that my client is not
ready for at this time.

I am considering, instead, exporting the Access tables into a SQL Server
database, then linking to it from the Access front end. Is this a sensible
solution?

Yes.

Do a search at the Knowledge Base at support.microsoft.com using the
keywords "upsizing" to review the various white papers on upsizing
Access to SQL Server as well as to ensure you have any updates
required.

Also see my Random Thoughts on SQL Server Upsizing from Microsoft
Access Tips page at
http://www.granite.ab.ca/access/sqlserverupsizing.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm