Confused about MSDE vs Jet

[David]

Is the MSDE vs Jet decision made when installing Access or
is it a choice I can make for each Access database I
create. In other words can I have one Access 2000
application that uses Jet and another that uses MSDE.
Also, If I have Access 97 and Access 2000 installed, can I
still use Access 2000 with MSDEn and Access 97 with Jet?

None of this is explained clearly in the many Microsoft
articles I have consulted.

 

[Allen Browne]

You can choose to create some JET databases and some MSDE projects from the
same installation of Access 2000.

To create a new JET database, choose:
File | New | Database
To create a new MSDE project, choose:
File | New | Project
Because you can open both types, it makes it easy to experiment with them.

You can have Access 2000 and Access 97 on the same computer. The Access 2000
installation can open your Access 2000-format databases, and your MSDE
projects. The Access 97 installation can open your Access 97 databases.

Access 97 does not support MSDE by default.
Access 97 cannot open Access 2000 databases.

If you open an Access 97 database in Access 2000, you are offered two
choices:
- Convert to 2000, or
- Open
If you "open", you can modify the data, but you cannot change the objects
(data structure, queries, forms, reports...)
If you "convert", you get a new mdb file in A2000 format which cannot be
read by A97.

 

[Joe Fallon]

Also,
MSDE is simply a slightly limited version of SQL Server so if you develop
for it, you can upgrade to a full version of SQL Server without any code
changes.

A97 can use linked tables against MSDE (and SQL Server.)

 

[David]

I'm confused about MSDE. I only just found out it even
exists and no-one here has heard of it or used it. I read
the Microsoft documentation but its still not clear.

Is it used with Access 2000 or does it replace Access 2000?
If I use MSDE as the datrabse engine, what do I have to do
differently (apart from some SQL syntax)? Do I still use
forms, tables and queries. Do I have links to the tables
in SQL Server. Whats the difference between an Access
Project and an MDB split database with the tables linked
to SQL Server via ODBC?

Is MSDE an optionally installed component of Access? This
shop never installs optional components, we don't even
have the help files, wizards or linked table manager.

Sorry for all the questions.

- David

 

[David]

One more question.
If I already have an Access 2000 mdb I'm working on, is
there a way to convert it to an Access 2000 project and
continue working on it that way.
- David

 

[Sirocco]

You can upsize your mdb file using the upsizing wizard in Access 2000 and go
in either of 2 directions:
1. keep the mdb format and LINK to the tables that have been put on a SQL
(or MSDE) back end (the tables are "upsized" to the SQL back end)
2. use the upsizing wizard to convert the mdb to an adp file, but this is
really a different animal than an mdb file. The way you work with the
tables will be completely different!

Further reading is required to understand and appreciate the differences,
and advantages of each. The simpler route is to use option 1 above, in
which all your queries, forms, and reports work the SAME but you have to use
SQL Enterprise Manager to edit the tables (or use a separate adp file to
edit the tables).

 

[Allen Browne]

Answers inline.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Is it used with Access 2000 or does it replace Access 2000?

A database must provide a way to store, retrieve, and modify data.
Essentially that's tables and queries.

Access provides much more:
- user interface (forms);
- output generation tools (reports);
- a programming language (VBA modules).

Other databases such as SQL Server provide just the tables and queries. You
need other tools for the interface, reporting, and programming tools. Access
is designed to do that job for other database products as well.

The data engine built into Access is called JET (Joint Engine Technology).
Native Access tables and queries use JET.

Beginning with Access 2000, Microsoft also bundled MSDE with Access. This is
a version of SQL Server that is intentionally throttled when it reaches 5
simultaneous connections. Their intention is that you - as a developer - can
learn how to work with SQL Server. You begin with File | New | Project
instead of File | New | Database.

An Access project (based on MSDE) is a very different beast than an Access
database (based on JET). If you are familiar with JET and its native DAO
library, you will find that you have much to learn about how to work with
MSDE and the ADO and ADOX libraries. In our experience it takes several
times longer to develop an MSDE project than a JET database. Further, it
requires more computer resources and more knowledge to maintain a SQL Server
database. The added expense and maintenance issues are not justified for
most small businesses and not-for-profit groups.

If I use MSDE as the datrabse engine, what do I have to do
differently (apart from some SQL syntax)? Do I still use
forms, tables and queries. Do I have links to the tables
in SQL Server. Whats the difference between an Access
Project and an MDB split database with the tables linked
to SQL Server via ODBC?

Yes, you will have attached SQL Server tables, and you will build forms and
reports in Access based on that data. You will write VBA code using the ADO
library for recordsets and queries, and using ADOX for managaing the schema.

The split Access database has attached JET tables. Almost everything that
works in a stand-alone mdb works exactly the same with JET tables and the
DAO library, but ADO is a different story.

Is MSDE an optionally installed component of Access? This
shop never installs optional components, we don't even
have the help files, wizards or linked table manager.

From the File menu in Access, choose Project. If that works, you have just
created your first project based on the MSDE.

Have a go. Gain some experience with MSDE. That's what it's there for. There
is an MSDE version of the Northwind sample database for you to experiement
with, but that probably is an optional component of the Office install.

More information:
Microsoft's white paper on upsizing:
http://support.microsoft.com/default.aspx?scid=kb;en-us;241743&Product=acc2000

Use the Microsoft Access Upsizing Wizard:
http://support.microsoft.com/default.aspx?scid=kb;en-us;325017&Product=acc2000

Issues with the Access 2000 Upsizing Wizard:
http://support.microsoft.com/default.aspx?scid=kb;en-us;325019&Product=acc2000

SSW's Upsizing Pro tool:
http://www.ssw.com.au/ssw/UpsizingPRO/

 

[Albert D. Kallal]

You can upsize, but it is not seem less, or 100% automatic. Basically your
data gets moved for you, but parts of your code will still need to be
changed.

You are changing from a file based system to a true client/server based
system, and thus you will have to expect a big change in the architecture of
the system.

If you have an existing application that you want to convert to a true
server based system (like the MSDE), then I would consider using linked
tables via ODBC to the MDSE. It would still be work to convert the
application, but not near as much as converting to a ADP project. This
linked ODBC approach allows you to use/keep more of your existing code,
especially the DAO code.

If you are stating new project from scratch, then starting out with a ADP
project would be the best way to get a client to server based application.

So, some decision needs to be made if you really need, or want to use a
client to server based application. At least now ms-access gives you a
choice as to which way you want to develop your application.