Store ODBC Connection With Database

[biggcc]

Really don't know the answer to this question and I thought I'd come to the experts. I have created an Access database that I use to push & pull data from an SQL database through linked tables. I created an ODBC connection on my PC and set it so the links keep the username and password for the ODBC connection. All that works great...Now my question - Is it possible to somehow "store" the ODBC connection within the Access database so that I don't have to go to every PC that is going to be running this database and create an ODBC connection? The Access database and the SQL database both exist on our network and all those that will be using it have access to both. If someone has the answer I would really appreciate it.

Thanks

 

[boblarson]

You can create a FILE DSN which can be on a network drive accessible by all and then connect via that one. Then, as long as they have access to that folder that the file dsn is in, they should be able to not have an ODBC connection installed on their machine.

 

[biggcc]

File DSN

Thanks Bob - I will definitely give this a try. Hopefully it will work and save me a lot of time.

 

[pbaldy]

It should definitely work. That's how I have most of mine set up, and I don't have to do anything at the PC level other than point them to my version control utility to start the program.

 

[biggcc]

Kudos Bob

Worked like a charm. This is definitely something good to know - I appreciate your help.

One additional question - So if I put a File DSN out on the network where I know everyone has access does that mean everyone should be able to open any database that I create with linked SQL tables? What about users on remote desktop sessions?

Thanks again,
Chester Campbell

 

[boblarson]

Yes, if you use that to create the connections, they should be able to access it, even using remote sessions.

 

[pbaldy]

I agree with a caveat. If you mean remote session like using RDP or PCAnywhere to connect to a PC on the network, they will work fine. If you mean remote with a VPN type connection, I've not been able to get the File DSN to work.

 

[boblarson]

good to know

 

[pbaldy]

I'd love to find out I'm wrong, because I'm designing an app right now that will be run over a VPN from an outlying office as well as by regular network users. The performance is fine, but I had to use a System DSN to get it to work in testing from the remote office. It would not work with the file DSN.