Results 1 to 5 of 5

Thread: ODBC (A2000)

  1. #1
    Star Lounger
    Join Date
    Oct 2001
    Location
    Western Massachusetts, USA
    Posts
    99
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ODBC (A2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ODBC (A2000)

    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.
    Charlotte

  3. #3
    Star Lounger
    Join Date
    Oct 2001
    Location
    Western Massachusetts, USA
    Posts
    99
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC (A2000)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ODBC (A2000)

    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
    Charlotte

  5. #5
    Star Lounger
    Join Date
    Oct 2001
    Location
    Western Massachusetts, USA
    Posts
    99
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC (A2000)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •