Engine Options?

[Guest]

Is there an alternative "Option" to the Access Jet engine (within Access)? I
read, somewhere that you can use an engine option that emulates SQL Server's
engine but with limited Access (5 users).

I can't find that article so now I don't know if I was dreaming or if you
can do this.

If you can, how do you do it?

Thanks you

Ross

 

[DAVID]

Access stores an Access Project in a JET mdb database.
Alternatively, it can store an Access Project in a
SQL Server database: this is called an Access Data
Project, the client file has a .adp extension.

When you go to create a new database application,
just choose Access Data Project from the options.

The Access Data Project was a new idea with Access
2000, but has gradually withered: first the table
design interface was disconnected (so you had to
use the SQL Server design tool instead of Access),
now Access 2007 no longer has the option to create
new ADP's (although it will still run ADP's created
with Access 2000-2003).

Also, DAO has a bypass mode for use with ODBC
databases. This is called ODBC direct. Access
cannot use ODBC direct for storing an Access
Project, nor for linked tables.

ADO was another technology like DAO, but
internally based on .com connections instead
of API (IISAM) connections. Like DAO, it has
modes other than OLEDB, and data storage options
other than JET and SQL Server, but it cannot
be used for storing an Access Project, nor for
linked tables.

(david)

 

[Larry Linson]

Is there an alternative "Option" to the Access Jet
engine (within Access)? I read, somewhere that
you can use an engine option that emulates SQL
Server's engine but with limited Access (5 users).

The database engine you describe was distributed along with Access on the
installation CDs. It was called the Microsoft Data Engine (MSDE) and was,
indeed, a version of SQL Server without the bells and whistles and without
the very nice adminstrative software tools that accompany SQL Server.
However, for _developing_ the SQL Server database to use with MSDE, there
was an inexpensive Developer Edition of SQL Server that did include the
administrative tools.

The current version, 2005, of SQL Server, has a free "SQL Server 2005
Express Edition" that has improved capabilities, fewer limitations, and
better administrative tools. There have been some posts in this newsgroup,
and in the sponsored access "client-server" newsgroup, as well as in the
USENET newsgroup comp.databases.ms-access. You should be able to find [
some | most | all ] of them by searching the archives at groups.google.com.

You do NOT have to use ADP, nor ADO, to use SQL Server, nor to use MSDE...
you can use ODBC drivers and link the tables from a standard MDB or MDE
file. You can also link tables from a number of other server databases,
some of which have free or inexpensive "desktop" versions for a few users,
or for some free server DBs, such as MySQL or PostgreSQL.

Also, though ADP is no longer the always-recommended approach to using
Access with SQL Server, it IS still supported in Access 2007, for creation
and maintenance, as well as execution. I believe the other responder had in
mind Data Access Pages (DAP), a somewhat limited and never very well
received attempt at using Access to create web based database
applications -- you can no longer create nor modify DAPs with Access 2007,
but you can still run them.

My work with SQL Server was never with MSDE, but always with full SQL
Server. I loaded the Developer Edition on my workstation and did some
testing, but never did anything "serious" with it. Most of my Access client
to server DB work was using ODBC to connect to non-Microsoft servers from
Sybase and Informix -- and, in those cases, almost all the server work,
connections, etc., was done by the Server DB's Database Administrator (DBA)
so I can't give you details at the "how-to" level.

Larry Linson
Microsoft Access MVP

 

[Guest]

Larry,

Your dissertation was beautiful and answered all of my questions. Thank you.

I am a striclly an Access developer who is always looking for ways to
improve performance of that little jet engine. I, like you, also use ODBC
connections. Additionally, I have gotten great results with some (but not
all) passthru queries.

I have toyed with SQL server just enough to be dangerous. I didn't know
about the limited versions and will check it out.

Once again, thank you for your effort!

Ross

Is there an alternative "Option" to the Access Jet
engine (within Access)? I read, somewhere that
you can use an engine option that emulates SQL
Server's engine but with limited Access (5 users).

The database engine you describe was distributed along with Access on the
installation CDs. It was called the Microsoft Data Engine (MSDE) and was,
indeed, a version of SQL Server without the bells and whistles and without
the very nice adminstrative software tools that accompany SQL Server.
However, for _developing_ the SQL Server database to use with MSDE, there
was an inexpensive Developer Edition of SQL Server that did include the
administrative tools.

The current version, 2005, of SQL Server, has a free "SQL Server 2005
Express Edition" that has improved capabilities, fewer limitations, and
better administrative tools. There have been some posts in this newsgroup,
and in the sponsored access "client-server" newsgroup, as well as in the
USENET newsgroup comp.databases.ms-access. You should be able to find [
some | most | all ] of them by searching the archives at groups.google.com.

You do NOT have to use ADP, nor ADO, to use SQL Server, nor to use MSDE...
you can use ODBC drivers and link the tables from a standard MDB or MDE
file. You can also link tables from a number of other server databases,
some of which have free or inexpensive "desktop" versions for a few users,
or for some free server DBs, such as MySQL or PostgreSQL.

Also, though ADP is no longer the always-recommended approach to using
Access with SQL Server, it IS still supported in Access 2007, for creation
and maintenance, as well as execution. I believe the other responder had in
mind Data Access Pages (DAP), a somewhat limited and never very well
received attempt at using Access to create web based database
applications -- you can no longer create nor modify DAPs with Access 2007,
but you can still run them.

My work with SQL Server was never with MSDE, but always with full SQL
Server. I loaded the Developer Edition on my workstation and did some
testing, but never did anything "serious" with it. Most of my Access client
to server DB work was using ODBC to connect to non-Microsoft servers from
Sybase and Informix -- and, in those cases, almost all the server work,
connections, etc., was done by the Server DB's Database Administrator (DBA)
so I can't give you details at the "how-to" level.

Larry Linson
Microsoft Access MVP