ODBC connection questions

Shane,

I have run into the same problem you are discussing, and I don't know if there is any way around setting up an ODBC connection on each computer. That is what I ended up doing.

I would love to hear if there is a better way of doing it myself, but I don't know if there is.

You *should* be able to get IT to set up a System DSN for the database. That way, it's possible for all users to get it without creating a DSN on each machine.

Denis

Denis,

I am going to have to talk to my network guys here about that. What exactly is a System DSN? Is it something like a network level ODBC connection?

Thanks, Denis. I'll give that a try!

Denis,

I looked at it a little more, and that is how I currently create the ODBC connection for each computer (use System DSN). I do this buy going to Administrative Tools -> Date Sources (ODBC) -> System DSN..., but this still requires us to do it on each computer.

Is there a different way to set it up so it is available to all computers without having to do it on each computer?

Sorry, wild goose chase

I was under the impression that System DSN's could be pushed out to all PC's but that is not the case. The advantages of System DSN's are (1) better performance, and (2) you can use them with ADO. See... http://support.microsoft.com/kb/300596

Another option, if you are merely reading from the remote source and don't need to write back, is to create pass-through queries in Access. They also perform well, and there is no need to create a DSN because the connection details are stored with the query. Here's a tut on creating pass-through queries... http://www.techonthenet.com/access/tutorials/passthrough/basics01.php

Note: All the stuff that I have read on pass-through queries says that you need an ODBC connection first. I have *not* found that to be necessary.

Steps:
1. New query
2. SQL-specific > Pass-through
3. View > Properties, and create the connection string -- something like
4. Create the SQL for the query.

You lose some convenience because you have to build the SQL, but you gain some convenience in not having to create DSN's for every database that you want to connect to, and you gain a performance improvement because the processing that returns the records happens on teh server, not record-by-record on your PC.

Denis

Denis,

Thank you for this useful information on pass-through queries, I will be putting it to use very soon.

Just a small tip, if the SQL server you're connecting to uses Windows NT account data for logging in rather than SQL Server security then the connection string should be:


Elia

Thanks for the tip -- I have always used DBs with SQL Server Security so I haven't used the other form of connection string

Denis

Hey great, thanks for the help everyone!