DSN - less ODBC connection to DB via MS Access DAO - Forum - OpenEdge General - Progress Community

DSN - less ODBC connection to DB via MS Access DAO

 Forum

DSN - less ODBC connection to DB via MS Access DAO

This question is answered

I am creating an MS Access application that will be run off-hours, no human intervention needed.

Here's the code I'm using to open and test the connection.

Dim dbs As DAO.Database
Dim sConnStr As String
Dim qdf As DAO.QueryDef

sConnStr = "ODBC;Driver={Progress OpenEdge 10.2B Driver};SERVER=JCFFIMS01;DATABASE=FOUND;UID=sysprogress;PWD=password"
Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("")


qdf.Connect = sConnStr
             'actual driver file name pgoe1025.dll
qdf.SQL = "SELECT * from FOUND_sql_Fund_Rep"
qdf.ReturnsRecords = False
qdf.Execute dbFailOnError

When I run this code I receive an error message that states: "ODBC--connection to 'FoundSQL92' failed."  

The application will be querying many tables in the progress db, not just the one indicated in the test query.

I am using MS Access 2016

Progress DB version 10.2B

Verified Answer
  • Hi,

    The connection string used in the VBA code could be incorrect (using 'SERVER' instead of 'HOST'), it should most probably be either something like:

    sConnStr = "DRIVER={Progress OpenEdge 10.2B Driver};HOST=localhost;PORT=12345;DB=sports2000;UID=sysprogress;PWD=sysprogress;DIL=0"

    (for an ODBC connection without DSN defined as shown in the example VBA code provided)

    or

    sConnStr = "DSN=sports2000;Uid=sysprogress;Pwd=sysprogress;"

    (for an ODBC connection with a DSN defined in %windir%\system32\odbcad32.exe or %windir%\syswow64\odbcad32.exe)

    If you test the ODBC connection string from outside of the VBA code of your MS Access application using the instruction from the following article I wrote on:

    knowledgebase.progress.com/.../how-to-test-an-odbc-connection-dsn-on-windows-using-powershell

    are you then able to connect to the OpenEdge 10.2B database?

    Also MS Access exist in both a 32 and 64 bit version and will need to use the matching 32 or 64 bit OpenEdge ODBC driver to be able to connect to the OpenEdge database.

    (more info on knowledgebase.progress.com/.../P170775)

    Kind regards,

    Tinco

All Replies
  • Hi,

    The connection string used in the VBA code could be incorrect (using 'SERVER' instead of 'HOST'), it should most probably be either something like:

    sConnStr = "DRIVER={Progress OpenEdge 10.2B Driver};HOST=localhost;PORT=12345;DB=sports2000;UID=sysprogress;PWD=sysprogress;DIL=0"

    (for an ODBC connection without DSN defined as shown in the example VBA code provided)

    or

    sConnStr = "DSN=sports2000;Uid=sysprogress;Pwd=sysprogress;"

    (for an ODBC connection with a DSN defined in %windir%\system32\odbcad32.exe or %windir%\syswow64\odbcad32.exe)

    If you test the ODBC connection string from outside of the VBA code of your MS Access application using the instruction from the following article I wrote on:

    knowledgebase.progress.com/.../how-to-test-an-odbc-connection-dsn-on-windows-using-powershell

    are you then able to connect to the OpenEdge 10.2B database?

    Also MS Access exist in both a 32 and 64 bit version and will need to use the matching 32 or 64 bit OpenEdge ODBC driver to be able to connect to the OpenEdge database.

    (more info on knowledgebase.progress.com/.../P170775)

    Kind regards,

    Tinco

  • Thank you for your response, however, I'd like this to be a DSN-less connection. Which I believe means there is no information stored in ODBC apps (32 or 64 bit), nor in the registry.  

    I may be incorrect.

    Regarding testing the connection using powershell - I don't have rights to execute a powershell command file (.ps1).

    Any ideas about setting up a DSN-less connection are welcome.Sally

  • 121steuart,

    Tinco's reply does show how to specify a DSNless connection string. Check out the first 'sConnStr = <blah>' line.

    You will need to change the driver name to your version, the HOST name, the PORT number, the DB name, the UID (user id) and the PWS (password).

    Brian

  • Okay - great.  

    Thank you for clarifying -- I'll give it a go.

  • Now I get this error:  Reserved error (-7778); there is no message for this error.

    After Googling the error I have compacted and repaired the DB and made sure the linked table has an index.

    I made sure the driver name below is connected to the correct dll (in the ODBC interface, driver tab).

    Here's my latest code:

    Dim dbs As DAO.Database
    Dim tdf As TableDef
    Dim sConnStr As String
    Dim bRefreshLinks As Boolean

    Set dbs = CurrentDb
    bRefreshLinks = False

    sConnStr = "DRIVER={Progress OpenEdge 10.2B Driver};HOST=jcffims01;PORT=2600;DB=found;UID=sysprogress;PWD=password;DIL=0"

       For Each tdf In dbs.TableDefs

           If Left$(tdf.Name, 5) = "FOUND" Then

               Set tdf = dbs.TableDefs(tdf.Name)

               tdf.Connect = sConnStr

               tdf.RefreshLink ' Relink the table.

               If Err <> 0 Then

                     bRefreshLinks = False

                   Exit Function

               End If

           End If

       Next tdf

  • BTW  "FOUND" precedes the name of each of the files that I want to link to.

  • Turn on system wide ODBC tracing
     
     
    Brian Maher
    Principal Engineer, Technical Support
    Progress
    Progress
    14 Oak Park | Bedford, MA 01730 | USA
    phone
    +1 781 280 3075
     
     
    Twitter
    Facebook
    LinkedIn
    Google+