Installing ODBC Driver on WorkstationsKB0038785 Authored by Serge Bosque • 1 View • 7mo ago Print Issue What are my options when creating a DSN to connect to my Epicor 9.05 Progress database? or How can I use Crystal Reports Developer 32bit to connect to the Epicor 9.05 database? or I require a new PC to access the ERP9 database via DSN from MS Excel. Error undefined Resolution Please note that DSN connections can cause performance and locking issues, therefore it is recommended you double check whether you really need it. To help you decide please find below a description of the 3 ways (A, B and C) in which an ERP 9 client can connect to the ERP 9 database, started by the most recommended: OPTION A. Direct Connection (without using ODBC). e.g. when your Crystal Report uses the data source generated by ERP Report Data Definition (RDD). RDD can be customised to include tables and fields that were not originally included. Therefore most custom Crystal reports do not need a DSN connection. The only exception are - if you need to use table aliases - if you need to access non ERP9 databases For example if you have an existing custom Crystal report that was modified in an earlier version of ERP, when adding tables to the RDD was not possible. OPTION B. ODBC Connection String. Most softwares, such as Crystal Reports and MS Office allow documents to connect to ODBC using a Connection String instead of a local DSN. The main benefit is that you no longer need to setup/create local DSN on each client. For example if you have an existing custom Crystal report that uses a DSN you could modify it to use a Connection String. For more information please contact support, indicating your version of ERP 9, and requesting the corresponding "ODBC Kit". OPTION C. ODBC Connection via DSN. Install the driver locally then setup a DSN to use it pointing to the server. Below are several methods(C1, C2, C3 and C4) that you can use to setup the Progress OpenEdge 10.2A Driver for Epicor version 9.05, starting by the most common. Note: The ODBC Kit mentioned in C4 contains additional information including ODBC Users/Security, that can be used regardless of the method used. METHOD C1. - Using steps documented in the ERP 9 Help 1. Using Windows Explorer on your client, browse to your server \epicor\oe102a\netsetup folder. 2. Double click on the SETUP.EXE in the netsetup folder which will install the OpenEdge 10.2A Shared Network Setup. For the working directory, you can point to the shared folder on the server. You may need to create one, if one does not exist. For example: \\servername\epicor\oe102a_wrk. 3. Create and open a new Text document at a workstation. For example: ODBC.TXT 4. Copy and paste one of the following registry entries below into that new (ODBC.TXT) text file. The entry you select is based on whether you use an UNC path or a mapped drive to the server. a.) Use this section if you use an UNC path to your server: Windows Registry Editor Version 5.00 [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\Progress OpenEdge 10.2A driver] "APILevel"="1" "ConnectFunctions"="YYY" "CPTimeout"="60" "Driver"="\\\\\\epicor software\\oe102a\\bin\\pgoe1023.dll" "DriverODBCVer"="3.5" "FileUsage"="0" "Setup"="\\\\\\epicor software\\oe102a\\bin\\pgoe1023.dll" "SQLLevel"="1" "UsageCount"="1" [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers] "Progress OpenEdge 10.2A driver"="Installed" b.) Use this section if you use a mapped drive to your server: Windows Registry Editor Version 5.00 [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\Progress OpenEdge 10.2A driver] "APILevel"="1" "ConnectFunctions"="YYY" "CPTimeout"="60" "Driver"="V:\\oe102a\\bin\\pgoe1023.dll" "DriverODBCVer"="3.5" "FileUsage"="0" "Setup"="V:\\oe102a\\bin\\pgoe1023.dll" "SQLLevel"="1" "UsageCount"="1" [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers] "Progress OpenEdge 10.2A driver"="Installed" 5. Verify that the last line "Progress OpenEdge 10.2A driver"="Installed" is the last line and that it is not on the same line as the previous string value. 6. Edit two lines of the text document to reflect your servers name. The two lines are: Driver = Setup= Change the \\Servername to your server's actual name. Save the changes. 7. Rename the ODBC.TXT doc to ODBC.REG. This changes the appearance of the file and allow it to be added to the registry. 8. Double-click on the ODBC.REG file which automatically imports the information into the workstations registry. Setup the ODBC DSN for the new driver. 9. Go to Start - Settings - Control Panel - Administrative Tools - Data Sources (ODBC) 10. Click on the System tab, then the ADD button. 11. Scroll down and locate the Progress OpenEdge 10.2A driver driver, highlight and click FINISH. 12. Fill in the Properties for the ODBC Progress OpenEdge Driver Setup. - Data Source Name: OpenEdge102A (do not use spaces in the name) - Description: OpenEdge 10.2A - Host Name: - Port Number: 9450 (for the Live database), 9460 (for the Training database), 9470 (for test) and 9480 (for pilot). These are the default ports. You can verify your port numbers using Progress Explorer. - Database Name: mfgsys - User ID: sysprogress (or the name you have chosen and setup in your database.) 13. Click on Apply and then press Test Connect. 14. Fill in the password, click OK. You should get Connection Established. Set Up User Name and Password If this is a new setup meaning your database is either a new install or has never been setup to use ODBC, you need to add an administrative user and password to the database directly. Use these steps to setup the sysprogress user. 1. On the server or your Admin workstation if you have one setup, make a copy of your SchemaChange icon. 2. Rename the copied icon to ODBC Setup. 3. Right-click the ODBC Setup icon and select Properties. 4. On the ShortCut tab, Target line, change the last parameter on the line from: applydf.w to _admin.p. Save the changes and double-click. 5. In the Progress Database Administration utility, Select Admin - Security - Edit User List. 6. Click on ADD, and fill in the following fields: - User ID: sysprogress - User name: sysprogress - Password: 7. Click OK, and click OK again. Exit the Data Administration session. 8. You should now be able to go back and Test Connect your DSN with the User ID and Password you just entered. METHOD C2: Using the Epicor 9.05 DVD You can install the full version of 32bit OpenEdge 10.2a on the workstation that you are creating the DSN from the Epicor 9.05 for 32bit Windows DVD METHOD C3. Using a 32-bit admin workstation You may have a 32-bit admin workstation to run your database conversions, if so can go through the following process: 1. On your 32bit admin workstation, share the oe102a folder 2. On the machine that you want to create the DSN, navigate to \\32bit_admin_workstation\oe102a\install\odbc 3. Launch the sql-odbc-setup.exe 4. Click next until you see the 'Install Wizard Complete' window; click the finish button 5. Create your System DSNs in the Data Source Administrator PLEASE NOTE: If you are creating the DSN on a 64bit OS, you will need to launch the 32bit data source administrator by navigating to c:\windows\syswow64\odbcad32.exe METHOD C4. Using the ODBC KIT Obtain the ODBC kit for the version of Epicor you are currently running by contacting Epicor Support. It contains the full process on creating the DSN and adding ODBC users. Notes