Can Access 2000 talk to 97?

[KevinS]

BLUF: Can I have Access 2000 databases feed from an Access 97 database?

I have a legacy app written in a multiuser version of Access 97. I know it
is old but it's what we got.

Anyhow I need to create newer databases in Access 2000 that will reference
data in the 97 databases to provide consistency. When I create a field in
the new database I want to reference a field in Access 97.

This will help to keep the data consistent until we get a new application.

Can this be done? If so, how?
Thank you,
KevinS

 

[John W. Vinson]

BLUF: Can I have Access 2000 databases feed from an Access 97 database?

I have a legacy app written in a multiuser version of Access 97. I know it
is old but it's what we got.

Best version of Access ever released. A2003 is close. Access2000 wasn't
*quite* as bad as the late unlamented Access95 but it's got LOTS of problems.
Any chance of upgrading to at least 2003? (2007 is a MUCH bigger step since it
has a drastically changed user interface).

Anyhow I need to create newer databases in Access 2000 that will reference
data in the 97 databases to provide consistency. When I create a field in
the new database I want to reference a field in Access 97.

This will help to keep the data consistent until we get a new application.

Can this be done? If so, how?
Thank you,
KevinS

I'd go with a split database (in ANY case, even absent this requirement!). You
can keep all of the tables in an Access97 backend. These can be read and
written from either an A97 or an A2000 (or 2003 or 2007) frontend.

 

[KevinS]

What is a split database? What kind of data consistency problems can it cause?
K

 

[David W. Fenton]

You
can keep all of the tables in an Access97 backend. These can be
read and written from either an A97 or an A2000 (or 2003 or 2007)
frontend.

The only exception to this is if the back end is replicated. Only
Jet 3.5 can write to a Jet 3.5 back end (though the data remains
readable).

 

[John W. Vinson]

What is a split database?

Standard practice for any multiuser Access database: the single database is
split into a "Backend" database containing only the tables, and a "Frontend"
containing links to those tables, along with queries, forms, reports, macros
and code. Each user gets their own copy of the frontend, and all the frontends
share the same backend.

What kind of data consistency problems can it cause?

It can and will RELIEVE AND PREVENT data consistancy problems, as well as
preventing most user-user contention and lockouts.

See http://www.granite.ab.ca/access/splitapp.htm for a thorough discussion.

 

[Noëlla Gabriël]

Hi,

you can easily link the Access 97 tables in your Access 2000 database.
Access 2000 can read all data from access 97 but not the other way round.

 

[KevinS]

Sounds like a client / server application.
Thanks,
Kevin S

 

[John W. Vinson]

Sounds like a client / server application.
Thanks,
Kevin S

It's sort of halfway there. The difference is that in a true client-server app
most of the computer processing takes place on the backend (your frontend
passes a SQL string to SQL/Server, the server does the search, and passes back
a result set). In a split .mdb operation the backend stores the data but the
processing takes place on the frontend. It's smart enough to use indexes and
to analyze queries to return only the indexes and the query hits, rather than
the entire table, if that's possible.

 

[Armen Stein]

It's sort of halfway there. The difference is that in a true client-server app
most of the computer processing takes place on the backend (your frontend
passes a SQL string to SQL/Server, the server does the search, and passes back
a result set). In a split .mdb operation the backend stores the data but the
processing takes place on the frontend. It's smart enough to use indexes and
to analyze queries to return only the indexes and the query hits, rather than
the entire table, if that's possible.

Another way to look at it is that the PC where the back-end resides is
really just acting as another hard drive. It doesn't need Access
installed. As John says, all the work is being done on the front-end
PC.

Also, if you are new to split Access applications, you might want a
good table relinker. You're welcome to use our free one at
www.JStreetTech.com/downloads.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com