Services Partners Company
Knowledge Base


Article

How to connect to the Microsoft Access database using DataServer for ODBC

« Go Back

Information

 
EnvironmentProduct: Progress
Version: 8.x, 9.x
OS: All supported platforms
Other: DataServer for ODBC
Question/Problem Description
How to connect to the Microsoft Access Database using DataServer for ODBC
How to create a schema holder for MS Access Database using the ODBC DataServer
How to create a schema holder from MS Access database
How to configure DataServer for ODBC to connect a Microsoft Access database
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement NumberPSC00345775
Cause
Resolution
** From OpenEdge 10.x onward, Microsoft Access is not a supported datasource for use with DataServer for ODBC. **

Here are the steps to accomplish this, it is assumed:
ODBC DataServer license is installed on the machine where MS Access Database is installed and the schema holder is going to be created on this machine, no DataServer broker is required.

1. Create an ODBC DSN under Control Panel > Administrative Tools > Data Sources (ODBC) > System DSN. Select the driver:

Microsoft Access Driver (*.mdb) 

Test the ODBC DSN to verify that the connection is working by using any SQL client.

3. Create a Progress empty database (schema holder) with any identifiable name, and connect to it in single user mode.

4. Start Data Administration and go to DataServer > ODBC Utilities > Create DataServer Schema...

5. Supply "Logical Database Name". This also could be any identifiable name but not the same as the schema holder name).

6. If the MS Access Database has user security configured, add the -U <user_name> and -P <password> parameters to Connection Parameters. Leave this empty otherwise.

7. Enter the name of the ODBC DSN name that was created in step 2, and click OK.

8. If the MS Access Database has user security configured, then enter the required credentials. Otherwise click OK on the next screen without entering the user name and the password.

9. In the "Pre-Selection Criteria For Schema Pull", either leave stars in all of the input text box, or enter the appropriate name for any one of the criteria. If all the tables are required to pull into the schema holder, only the owner name is required.

10. Use a single mouse click to choose a table or another single mouse click to select a table or use "Select Some..." and "Deselect Some..." buttons to select and deselect the table(s).

11. Click OK and it should bring all the selected tables' references to the schema holder.

12. After schema holder is created, use following connection parameters to connect to schema holder and MS Access database

prowin32 odbholder -RO -db dsn_demo -dt ODBC -ld demo -H host1 -S oserviceA -U bob -P bobpass

odbholder - schema holder name
dsn_demo - ODBC DSN name
demo - logical name

 
Workaround
Notes
MS Access datasource is no longer supported, however loegacy support remains and the , "MS Access" option is still present when it comes to selecting a foreign datasource in the ODBC Dataserver migration utility in OpenEdge 10.x and later releases. By design, the ODBC DataServer design is supposed to be open to any target whether it is "supported" or not. The documentation will be updated to provide better guidance regarding this unsupported datasource and limitations when it comes to legacy support (PSC00345775).

References to other documentation:

OpenEdge Data Management: DataServer for ODBC

Progress articles:
000001149, "Which data sources does Progress Dataserver for ODBC support?"
Attachment 
Disclaimer

The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.



Feedback
 
Was this article helpful?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful.



Characters Remaining: 255