ODBC (A2000)

Topic

New Reply

I use the following line making an ODBC connection.
Set conPhyPlant = wrkODBC.OpenConnection(“Connection1”, , , “ODBC;DSN=”)
The question I have concerns the DSN. If I run the code as is I’m prompted to select a DSN from the list. If I do, everything runs smoothly. If I change that line to
“ODBC;DSN = MyDataSource”)
I get an error ODBC – Call Failed. I can’t quite figure out why I can’t enter my DSN directly into the code. Anyone have any thoughts.
Thanks
Paul

Reply | Quote

Replies

Does MyDataSource represent the name of a DSN or a database? I believe when you specify a DSN, you also have to specify the Database, i.e, “DSN=MyDataSource, DATABASE=MyDataSource”. I’m assuming the connection SQL Server or some such, since Access doesn’t like you to try and establish an ODBC link to Jet.

Reply | Quote

Using all the arugments in the Connect string was my next thought. This is a connection from an Access Db to another Access Db. Originally, what I was trying to find out was related to connecting an Access Db to a RAD interface on our FirstClass e-mail server. In the RAD application, I can see the Db, but I can’t see the Tables so I thought I would back up a few steps and start with an Access to Access connection to see what type of DSN connection worked. You can select a UserDSN, SystemDSN and FileDSN. I can’t find a lot of info related to which is the best. So far the FileDSN is the one I’ve used in my question. This is just for learning and not a permenant type of connection. Unfortunately, FirstClass has a lot of people happy to come do the work for you but not much information if you want to try doing it in house (budgets in schools these days are tight, but Administration still wants progress). Thanks for the pointers. I’ll see what happens and go from there.
Paul

Reply | Quote

You don’t normally try to make an ODBC connection to another Jet database. The connection string for a linked Jet table is different from the string for an ODBC connection, which may be why you’re having problems. With a Jet database, you set the connect property of the tabledef (assuming you’re using DAO) to “;Database=” & strPath & strDatabasename

Reply | Quote

This works, selecting MyDataSource from the DSN dialog box:
Set conPubs = wrkODBC.OpenConnection(“Connection1”, , , “ODBC;DATABASE = SCHOOLHOUSEPhyPlantPhysical Plant 2000Physical Plant Work Order 2000.mdb;UID=;PWD=;DSN=”)
This doesn’t work,
Set conPubs = wrkODBC.OpenConnection(“Connection1”, , , “ODBC;DATABASE = SCHOOLHOUSEPhyPlantPhysical Plant 2000Physical Plant Work Order 2000.mdb;UID=;PWD=;DSN=MyDataSource”)
Now from what you say, part of the issue is probably Jet so if Jet doesn’t like this I won’t push it. As I say, this is just practice so if it doesn’t work completely, it’s no big loss. I’m just trying to familiarize myself with the process a little better before I get back to bigger issue.
Thanks a lot.
Paul

Reply | Quote