ODBC connection questions

[sschrupp]

Hi everyone,

I've made a database that has a couple linked tables connected to our SQL servers. I had to create an ODBC connection on my PC in order to access them. When I sent a copy of the database to my co-worker to check out he can't access the ODBC because he doesn't have the same connections set up on his PC like I do.

I've never really dealt with ODBC connections before so I'm a total beginner when it comes to this. I'm assuming there's a way to have other users access a database on their own PCs without having to set up identical connections on each PC.

So what can you all tell me to help me out?

Thanks!
Shane

 

[Joe4]

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.

 

[SydneyGeek]

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

 

[Joe4]

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?

 

[sschrupp]

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

 

[Joe4]

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?

 

[SydneyGeek]

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

ODBC;Driver={SQL Server}; Server=Your_Server_Name; Database=Database_Name; Uid=A_Valid_UserID; Pwd=That_users_password

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

 

[emady]

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:

ODBC;Driver={SQL Server}; Server=Your_Server_Name; Database=Database_Name; SSPI

Elia

 

[SydneyGeek]

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

 

[sschrupp]

Hey great, thanks for the help everyone!