Hi Jethro,
There are lots of reasons to use a more powerful database server, such as
SQL Server or Oracle, but, for relatively small numbers of users, say 20~50
people, on a LAN (Local Area Network), a properly designed JET back-end
database should serve you just fine. SQL Server and Oracle are designed to
meet additional requirements, including security, scalability (hundreds or
thousands of users), reliability (transaction logs, real-time backups), etc.
If you decide that you really need to migrate, you should obtain a copy of
the book titled "Microsoft Access Developer's Guide to SQL Server", written
by Mary Chipman and Andy Baron (SAMS Publishing). Here is a link to the book
at Amazon.com:
http://www.amazon.com/exec/obidos/t...103-5399559-8631817?v=glance&s=books&n=507846
Early in the book (page 6) the authors write:
<Begin Quote>
"Many people think that upsizing from the Jet database engine to SQL Server
is a universal panacea for whatever is ailing their Access databases. It's
not. In fact, just the opposite is usually true. If your Access application
is a dog, then most likely it will still be a dog after you upsize it to SQL
Server--perhaps an even bigger, uglier, shaggier dog! Even a well-designed
Jet database often won't run any faster after the tables are upsized to SQL
Server if you are using the same data access methods you used for your Access
database. In order to successfully convert your Access database, you have to
be clear about why it needs to be upsized, and you need to understand how to
take advantage of the strengths of SQL Server by reworking the data access
elements of your Access application."
</End Quote>
Avoid using special characters in the names of your tables (or any objects
for that matter). This includes spaces, # signs, etc. Use good database
design practices, most importantly proper normalization. Here is an article
that you may find helpful:
Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html
These tips, and many more, are included in a Word document that I have
created. You are welcome to download a zipped copy from here:
http://home.comcast.net/~tutorme2/samples/accesslinks.zip
There is a SQL Server Migration Assistant for SQL Server 2005 that is
helpful for migrating a JET back-end to a SQL Server back-end:
http://www.microsoft.com/downloads/...B4-C914-4AC7-B2F3-D25FFF4E24FB&displaylang=en
Optimizing Microsoft Office Access Applications Linked to SQL Server
http://msdn2.microsoft.com/en-us/library/bb188204.aspx
Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
