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
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
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.
|
||||
Brian Maher
|
||||
Principal Engineer, Technical Support
|
||||
Progress
|
||||
14 Oak Park | Bedford, MA 01730 | USA
|
||||
|
||||
|