I'm investigating my options as far as having a SQL Server backend with an Access frontend.

I know Access can connect via an ODBC connection to SQL Server backend, but are there other, better ways to do so?

SQL Server allows connecting to an existing instance of SQL Server using Named Pipes. Can MS Access use Named Pipes to connect to SQL Server?

Or, given the specific connection string that SQL Server uses to connect to the database, can Access use that to access the data? I know that in VB you could create an ADODB object with the connection string. Is that just forcing an ODBC connection? Or is that something else? If so, is it more efficient than an ODBC connection?

In general, I'd recommend ODBC generally.

There are times when you want to use ADO, and you are not restricted from choosing only one or other - you can use both and in fact I do just that - I tend to end up maybe 90% DAO (ODBC) and 10% ADO (OLEDB).

Named Pipes is a protocol that's handled by the SQL Server's driver/provider. If you pass in a connection string that specifies Named Pipes, then it'll use just that. In general, I tend to use TCP/IP only because it's simple, is compatible and works just about anywhere.

Efficiency is more likely to be impacted by how you write the queries -- the differences in speed / performance between DAO and ADO for a equivalent query is likely too small to be of any significance. I'd be more concerned with what functionality you need -- DAO (ODBC) gives you a simple object model with high level of operation and thus makes your job simple while ADO allows for more interesting stuff but can be tricky and has surprises. Use what is the right tool for the job, I'd say.

Thanks for the speedy reply. Sorry, I'm new to these forums, how do I mark a topic as answered? Or is that done automatically, or by a moderator?

I'm just in the process of doing exactly what your considering.

I've had some help from another programmer here to provide some guidance but for the most part, I've just got the SQL server set up with our IT with the back end tables and have just linked them to the FE.

From what I've read, this is a good first step before getting more fancy with stuff.

So far, so good. Some of the qrys I use through the ODBC links are somewhat slower so I'm working on getting educated on pass through qrys to start.

I also use the DAO model with the linked tables since that is what I understand. Haven't done any ADO yet.

If you want, I would think those two articles are great starting point and they both have additional links that should be of help.

Beginning SQL Server Development
Beginner's Guide to ODBC