Connecting Access MDBs to SQL databases

[Bob Alston]

With all the recent discussions on ADPs and their lack of success, can
someone point me to some GOOD reading on what is required to link MDBs
to SQL Databases / Microsoft SQL Server 2000 Desktop Engine.

Also are their any good, concise summaries of key differences that
someone with Access/Jet experience would need to understand?

Thanks

Bob

[Rick Brandt]

Bob Alston wrote:

With all the recent discussions on ADPs and their lack of success, can
someone point me to some GOOD reading on what is required to link MDBs
to SQL Databases / Microsoft SQL Server 2000 Desktop Engine.

Also are their any good, concise summaries of key differences that
someone with Access/Jet experience would need to understand?

In Control Panel you set up a DSN that points to the SQL Server instance and
database that you want to use. Then in Access...

File - Get External Data - Link Tables

Choose ODBC Data Source as the type, choose the DSN you created in step one, and
when the list of tables is displayed select those you want and press [OK].

Once you have links created you can largely work with them in the same manner as
you would Access tables. Now...doing this exclusively might not be the
"optimum" way to use a SQL Server database from a client, but it will work to
get you started. From there you simply find the places that need performance
enhancement and that is where you spend your time doing things differently.

A lot of the "re-design" that moving to client-server involves comes from people
who didn't have a very good multi-user (networked) design in the first place.
If your "all Access" application is using all of the best practices for
efficient data handling then it might need very little re-work when you move the
back end to SQL Server.

The problem comes from instances where Access allows you to get away with some
pretty awful design habits and then when you move to a server back end it
performs like crap. The notion that all your DAO has to be re-written and all
queries changed into Stored Procedures and Views is simply wrong. Some of them
will, yes. Many others will be fine as plain old Access queries.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[David W. Fenton]

"Rick Brandt" <ri*********@hotmail.com> wrote in
news:An*****************@newssvr11.news.prodigy.co m:

Bob Alston wrote:

With all the recent discussions on ADPs and their lack of
success, can someone point me to some GOOD reading on what is
required to link MDBs to SQL Databases / Microsoft SQL Server
2000 Desktop Engine.

Also are their any good, concise summaries of key differences
that someone with Access/Jet experience would need to understand?

In Control Panel you set up a DSN that points to the SQL Server
instance and database that you want to use. Then in Access...

Is a DSN required? Or just to create linked tables? Can't you save a
DSN-less connect string in the IN clause of a saved querydef and use
that as your interface to SQL Server via ODBC?

And what about ADO? Can't it connect to SQL Server directly, using
OLEDB instead of ODBC?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Ananda Sim]

Bob Alston wrote:

With all the recent discussions on ADPs and their lack of success, can
someone point me to some GOOD reading on what is required to link MDBs
to SQL Databases / Microsoft SQL Server 2000 Desktop Engine.

Also are their any good, concise summaries of key differences that
someone with Access/Jet experience would need to understand?

Thanks

Bob

Attaching remote sql databases to Access's database container is not
difficult. Making the system perform to satisfaction is another
question. Some random thoughts:

http://accesscoach.wikispaces.com/Access+ODBC

[Steve Jorgensen]

On Sat, 15 Oct 2005 20:47:38 -0500, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

"Rick Brandt" <ri*********@hotmail.com> wrote in
news:An*****************@newssvr11.news.prodigy.c om:

Bob Alston wrote:

With all the recent discussions on ADPs and their lack of
success, can someone point me to some GOOD reading on what is
required to link MDBs to SQL Databases / Microsoft SQL Server
2000 Desktop Engine.

Also are their any good, concise summaries of key differences
that someone with Access/Jet experience would need to understand?
In Control Panel you set up a DSN that points to the SQL Server
instance and database that you want to use. Then in Access...

Is a DSN required? Or just to create linked tables? Can't you save a
DSN-less connect string in the IN clause of a saved querydef and use
that as your interface to SQL Server via ODBC?

Using a connect string in an IN clause works, but is very inefficient, and
it's not necessary. Table links can be made with a DSNless connection, it
just has to be done in code. What I usually do is make the links with the
DSN, then write a procedure to loop through the tabledefs collection, and
replace all the connect strings.
And what about ADO? Can't it connect to SQL Server directly, using
OLEDB instead of ODBC?

Yes, but the only reliable way to bind forms that way is to use an ADP, and
that's what we don't want to do.

[Steve Jorgensen]

On Sat, 15 Oct 2005 21:19:28 GMT, "Rick Brandt" <ri*********@hotmail.com>
wrote:

....

The problem comes from instances where Access allows you to get away with some
pretty awful design habits and then when you move to a server back end it
performs like crap. The notion that all your DAO has to be re-written and all
queries changed into Stored Procedures and Views is simply wrong. Some of them
will, yes. Many others will be fine as plain old Access queries.

It's such a relief not to be the only one shouting this from the rooftops.

Everybody - don't rewrite all your access queries as stored procedured and
views when you upsize!!!!!!! You probably won't help your application
performance, and you'll have to implement an entirely new set of add/edit
forms to be able to update data on the server.

[Rick Brandt]

David W. Fenton wrote:

"Rick Brandt" <ri*********@hotmail.com> wrote in
news:An*****************@newssvr11.news.prodigy.co m:

Bob Alston wrote:

With all the recent discussions on ADPs and their lack of
success, can someone point me to some GOOD reading on what is
required to link MDBs to SQL Databases / Microsoft SQL Server
2000 Desktop Engine.

Also are their any good, concise summaries of key differences
that someone with Access/Jet experience would need to understand?
In Control Panel you set up a DSN that points to the SQL Server
instance and database that you want to use. Then in Access...

Is a DSN required? Or just to create linked tables? Can't you save a
DSN-less connect string in the IN clause of a saved querydef and use
that as your interface to SQL Server via ODBC?

Never tried the IN clause against ODBC. My first thought is would it be
editable? If not then I would just use a passthrough and a DSN.

DSNLess has some advantages, though mostly they are administrative. Someone has
to create the DSN. That is fairly easily handled in code or by the installer of
the app. Also as I have stated before, many of my tables are links to an IBM
ISeries box and I have never been able to get DSNLess to work against those.
That has prevented me from really spending much time on a DSNLess app.

DSNLess also breaks the Linked Table Manager in Access 97 which is what I still
develop in. I know I could write a replacement for that, but that's a side
project that I have just never gotten around to.
And what about ADO? Can't it connect to SQL Server directly, using
OLEDB instead of ODBC?

Never touched ADO. In code I suppose what you're saying is correct, but as with
changing everything to Stored Procedures and Passthroughs I see no reason to do
that unless I have a performance problem to solve. If DAO against a link gives
me instantaneous response times, why complicate things further?

[Rick Brandt]

Steve Jorgensen wrote:

On Sat, 15 Oct 2005 21:19:28 GMT, "Rick Brandt"
<ri*********@hotmail.com> wrote:

...

The problem comes from instances where Access allows you to get
away with some pretty awful design habits and then when you move to
a server back end it performs like crap. The notion that all your
DAO has to be re-written and all queries changed into Stored
Procedures and Views is simply wrong. Some of them will, yes.
Many others will be fine as plain old Access queries.

It's such a relief not to be the only one shouting this from the
rooftops.

Everybody - don't rewrite all your access queries as stored
procedured and views when you upsize!!!!!!! You probably won't help
your application performance, and you'll have to implement an
entirely new set of add/edit forms to be able to update data on the
server.

I have had more than one occassion where changing to a View or SP was actually
slower than just using a local query against a link. I don't understand that,
but it does happen.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Bob Alston]

Thank you for the many helpful replies!

Bob