DSN - less ODBC connection to DB via MS Access DAO

Posted by 121steuart on 22-Sep-2018 12:25

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

Posted by Tinco on 24-Sep-2018 02:59

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

Posted by Tinco on 24-Sep-2018 02:59

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

Posted by 121steuart on 27-Sep-2018 11:17

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

Posted by Brian K. Maher on 27-Sep-2018 11:27

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

Posted by 121steuart on 27-Sep-2018 11:34

Okay - great.  

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

Posted by 121steuart on 27-Sep-2018 15:33

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

Posted by 121steuart on 27-Sep-2018 15:35

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

Posted by Brian K. Maher on 28-Sep-2018 06:00

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+
 
 

This thread is closed