New to databases. SQL vs Jet

[John]

Today I decided to get serious about learning Access. My version is old
(Access 2000) and an upgrade is not in the immediate future. Should I install
MSDE before I go any further? I have read 5 year old posts here and it seems
like our company needs could grow to require SQL. Right now, there would be
just 4 or 5 simultaneous users on a peer-to-peer network at each office but
we have 5 locations and I anticipate a day when we'll need to combine these 5
databases on a single server. Because my references are for Access 2000, I am
not certain of the advice I am reading. Time and technology marches on. I
don't want to burn a bridge now. Thanks, in advance, for your help, patience
and understanding.

 

[Arvin Meyer MVP]

I suggest starting by using the JET engine, then, if you requirements demand
it, upgrading to the current SQL-Server (lite) engine which is SQL-Server
Express. I'd also suggest using the MDB format for the front-end, regardless
of which engine that you ultimately use. You can install the MSDE, but it is
subject to some serious security flaws without making sure that all service
packs and hotfixes are applied.

Using this plan will ultimately give you the most leeway in making choices
later.

 

[Albert D. Kallal]

I can't really add much to Arvin's comments.

As Arvin hints, you really don't need to bother with the MSDE, since the
express edition of sql server 2005 is not only REALLY nice, but sql server
2005 express tools to manage and work with sql server are also very nice
(and free). One of the big drawbacks of the MSDE is that it never came with
a nice set of management tools to work with sql server, and now that
shortcomings been fixed with the arrival of sql 2005 express;

So, access 2000 will work just fine with sql server 2005 when you use linked
odbc tables to sql server....

The only other suggestion for the 4-5 users on your office file share
network is to split your database. In other words, with only 4-5
users you not really need sql server as of yet. I explain this concept here:

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

If your talking about deailing with data and users spread over a wide area
and not necessary on your office network, then I explain some soltions here:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html
The above artcile on wans much assume you read and abosred the article on
splitting....

So, your verison of access should be just fine...and it will work well with
sql server 2005...

 

[John]

Albert (and Arvin),

Thank you so much for your insight. That was exactly what I was looking for.
The road ahead can be treacherous when one makes a wrong turn early in the
journey. I appreciate your help and guidance (and the links to more
information).