Migrating from 97 to 2002+ / JET to MSDE

[JonWayn]

Need help in understanding what is involved in migrating from Access97 to
2002+. I'll try to be a brief as possible. I am a self-taught programmer
that writes databases for a firm which, in my mind, has a non-standard way
of operating databases. Non-std in the sense that instead of incorporating
everything under a single database, it has a different database for each
vendor from which it buys data before modifying and selling the data. I
didnt change that format when I started programming for them. The company
has several employees, myself the only programmer/data processor; the
others, including owner, are just data processors. The owner does the final
bit of compilation and sends extractions to his various clients. Each
employee works from home and at the end of each day, sends by email, the
data processed, which he (owner) takes the following day into a final single
database to do the rest. Also, employees operate multiple databases, but at
the end of the day transfer all finished data to a single database and email
the company that database. All of this has been easy since we are using
version 97 with data-and-front-end contained in the same file. So at the end
we send that 1 file, he receives it (about 5) and do his stuff.

Migration issues:
I have been doing some reading on the differences of Access 2002+; the fact
that Microsoft will no longer update JET and is now focussed on SQL server,
which is a split client/server system. According to books, unless the firm
is a large one, there's no need to adapt the features of SQL server/MSDE.
However, if JET is no longer being updated, I am afraid of getting left
behind in the future, or not taking advantage of any new features the SQL
server will be getting. Therefore, I want to get into SQL server. The big
question is: does it allow me to keep our existing format - of having
multiple separate databases that transfer data to a central database and
then email that database to a remote user? If so how. Please explain.

Sorry for the verbosity of this message.

 

[Barry Gilbert]

The rumors you've read about the future of Jet don't seem to me to be a
big issue for you. First of all, Jet isn't going away; it's just
morphing. Look here for an good overview:
http://blogs.msdn.com/access/archive/2005/10/13/480870.aspx

Secondly, you are migrating to 2002, not 2007. 2002's version of Jet
shouldn't be significantly different from 97.

If you were to move to SQL Server, I would suggest rethinking your
entire architecture. It sounds like a great opportunity to develop/buy
some sort of portal in which the employees work from home. This will
eliminate the replication-style, disconnected nature of your app.

HTH,
Barry

 

[JonWayn]

Thanks for your input. However, you still didnt answer the core question,
which is; If I migrate to the latest version of Access, will there be a
problem retaining our existing format of attaching a table from multiple
databases to one database, email that database to another user, and, have him
import that data into another database on his local machine? I am asking
because from my understanding of MSDE/SQL Server, there isnt much, if any,
direct interaction with the data store. The tables you see in the UI are
pretty much linked to a backend. Then, that implies that if I email the
database the way I normally do, the receiver, which doesnt have my backend on
his machine, would not have the data - just the links. If there is a way to
send the data, please explain how it is done.

 

[Rick Brandt]

Thanks for your input. However, you still didnt answer the core question,
which is; If I migrate to the latest version of Access, will there be a
problem retaining our existing format of attaching a table from multiple
databases to one database, email that database to another user, and, have him
import that data into another database on his local machine? I am asking
because from my understanding of MSDE/SQL Server, there isnt much, if any,
direct interaction with the data store. The tables you see in the UI are
pretty much linked to a backend. Then, that implies that if I email the
database the way I normally do, the receiver, which doesnt have my backend on
his machine, would not have the data - just the links. If there is a way to
send the data, please explain how it is done.

Yes if you use any sort of back end besides an MDB file to hold the data then
Emailing it back and forth will not be an easy thing to do.

You could IMPORT the data into a temporary MDB file that you Email to the other
person (then they would have data), but then merging any changes they make back
into your MSDE back end would not be trivial to accomplish.

 

[JonWayn]

So, that brings my next question up. What file do I attach to the email
message to send. Lets say I had a .adp file called A.adp and another B.adp.
Can I link each to its own individual backend server? Is there some standard
as to where the backend server is to be stored, its naming convention, if any
(same base name as client database, etc.)?

 

[Brendan Reynolds]

If you switch to a later version of Access but do *not* switch to SQL
Server/MSDE, you can continue e-mailing the MDBs. If you switch to SQL
Server/MSDE, this becomes more complicated. Prior to SQL Server 2005, you
have to detach a database from SQL Server before you can copy it. At the
receiving end, you'd have to detach the old copy and attach the new one.
This can be easily done using SQL Server Enterprise Manager. Unfortunately,
MSDE does not include Enterprise Manager, so may find yourself having to
call command-line tools from batch files.

SQL Server 2005 has introduced some features (auto-close and FOR ATTACH) to
make this kind of thing easier. There's also a free graphical management
tool for SQL Server 2005 Express, though it is still in Beta. But I don't
have enough experience with SQL Server 2005 to advise you on the use of
those features. You might want to read up on them in SQL Server Books Online
and/or ask about them in a SQL Server forum if you're interested.

By default, SQL Server gives data files an .MDF extension and log files an
..LDF extension. These are the files you would have to send.

Alternatively, you might want to look into SQL Server Replication. Again, I
don't have enough experience to advise you on that, see Books Online and/or
a SQL Server forum.

 

[JonWayn]

What if I have 2 databases, A.adp and B.adp, what would the corresponding
..ldf and mdf files be named? I guess I dont choose the name, SQL server or
the application does. In that case are they named A.ldf, A.mdf, for the A.adp
database? In either case, where are they stored. Does the developer have
control over name and location of corresponding data files?

 

[Brendan Reynolds]

The developer has control. I haven't used the upsizing wizard recently, no
doubt it will propose default file names and locations, but those are just
defaults. You can over-ride them if you wish.

 

[Albert D.Kallal]

As a few mentioned, JET will be around for a long long time..so, I don't
know where you heard that rumor...it is incorrect...



On the other hand with such system now that includes emailing of data
files, then all kinds of data merges, it sounds like a difficult system to
use (and, if not difficult, it at least's wastes a lot of time and needs
extra more human interaction and steps then it should.). Remember, that time
wasted could be used to feed the poor, or help others...or simply increase
company efficiency.



If you move to sql server, then it seems reasonable to have each of the
clients connect OVER the internet to ONE data store. No more emailing, no
more merging....all of this time wasting would eliminated with a solution
that allows people to connect to a central system . Since users now can
email, then one has to assume they currently have access to the internet?



Perhaps now they have limited connectivity, or no high speed internet, so,
perhaps emailing is the ONLY reasonable approach you have here. (Your hands
might be tied here - but, if they have good connections, then you can use
sql server to eliminate the emailing, and merging part.).



I see no reason to migrate to sql server unless you going to use the extra
features and abilities of sql server to eliminate several steps you now use
as a result of using ms-access. And, as mentioned, ms-access is not going
away.



On the other hand, I still have clients of mine using 16+ year old FoxPro
programs written in DOS that works on a BRAND NEW machine. And, version 2.0
of ms-access, or even earlier versions of ms-access still work fine on a
brand new machine today.



Given that the track record of Microsoft is the best in the industry in
terms of past compatibility (apple for example has forced users to throw out
old software more then once in the same time period as MS track run). So, I
see little reason to worry about running a older jet based application.



In fact, if you want to, you can find a copy of the ORIGINAL spreadsheet
written by Dan Bricklen...it is dated 1981..and ran on the FIRST ibm pc.
And, it needs pointing out that this 1981 program still runs on a BRAND NEW
machine. So, if you can run 25 year old software on a new machine, then
really, why be worried?



So, a good reason if, and when you do migrate to sql server is that of
connectivity. Sql server can feed the data to a web based interface (so, you
might build a web based interface and application for these users...and
again eliminate all of that emailing and merge stuff). Ms-access can also
connect to Sql server OVER the internet. (again, then, after a user enters
their data...they are done...no merge,...no email...they are just done!!).



So, I would only move to sql server if there are specific features of sql
server that you need (or, are WILLING to use. So, if you are not willing to
give up the clumsy email approach, then taking on sql server would likely
result in more work, more training, and more administration. If you don't
take any advantage of sql better connectivity, then you don't gain here. It
is like finally realizing that you need a LARGE delivery truck. Unless you
can use the large truck (along with the extra expertise required to run the
large truck), you are better to stick with your delivery vans.

 

[JonWayn]

I know JET will be around. What I heard is that there will be no new upgrades
or versions of it. The emailing of the data is not as interactive as you
might think. It is all made easy through automation. There is a form button
for each employee which creates the email, adds the attachment and sends it
to the company. Also the company does not have a network server. What it has,
though, is an FTP site. Will that suffice as a central point to keep our data?

 

[TC]

I know JET will be around. What I heard is that there will be no new upgrades or versions of it.

100% incorrect. Read the early postings here:
http://blogs.msdn.com/access

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Albert D.Kallal]

Also the company does not have a network server. What it has,

though, is an FTP site. Will that suffice as a central point to keep our
data?

You can send files to the FTP site..but not connect ms-access to a ftp site
(it don't work that way).

However, since the company has a FTP site..then presumably they have server
space. And, even when you rent, or purchase FTP space, 90% or more do also
provide some type of sql server (database) system and space also. So, you
might check what they offer.

And, if what you have now works..then I would not worry much. As mentioned,
moving to sql server would result in some additional administration and work
to maintain that database server. So, I would only justify the EXTRA time
and effort to maintain the database if you were realize some type of gain by
moving to sql server.

All in all, it seems your main worry was about JET and ms-access, and there
is not much to worry on that area...