Skip to main content

How do I setup my ODBC data source to Sage 50 for the sync

Updated

EDITION: Standard, Enterprise

RESOLUTION

Click here for video example

Section I: Allow Data Access from your Sage 50 Company

  1. Open Sage 50 and your company.

  2. Select Maintain, Users, Set up security

  3. Select the Crystal Reports/Data Access tab on the User Security window.

  4. Under Access from Outside Sage 50, Select With the following login information, then press Change.

  5. Enter a password and confirm it. Note: The password must be 7 characters in length and contain at least one letter and one number. Example: Sage50!

  6. Click Close to close the window and you will be ready to use and ODBC or OLE DB connection for read-only access to your company data.

Section II: ODBC Connections - Setting up the ODBC data source:

Before you can connect to the Sage 50 database via ODBC, it is necessary to add the Pervasive database engine as an ODBC data source using these steps:

  1. Select Start, Run

  2. In the Open field, type odbcad32 and click OK. Note: For 64-bit versions of Microsoft Windows, users must type the following instead: “%systemdrive%\Windows\SysWow64\odbcad32.exe” and click OK.

  3. The ODBC Data Source Administrator window appears.

  4. Select the System DSN tab

  5. Click the Add button on the right to add the Pervasive database as a user data source.

Single user/Single computer environments:

  1. In the Create New Data Source window, select Pervasive ODBC Engine Interface and click Finish.

  2. The Pervasive ODBC Engine DSN Setup window appears. Enter a Data Source Name and select the Database Name from the drop-down list. The Database Name will appear as the company name in Sage 50. Click OK.

  3. The Data Source Name should now appear in the User Data Sources list. Select OK.

Network/Multi-user environments:

  1. In the Create New Data Source window, select Pervasive ODBC Client Interface and click Finish.

  2. The Pervasive ODBC Client DSN Setup window appears. Enter a Data Source Name and an Address. The Address will be the IP Address or the computer name to the server where the Sage 50 data is located. Click Create.

  3. Browse and select your individual company folder for the Dictionary Location, click OK, enter something unique in the Database Name box and click OK on all screens.

  4. The Data Source Name should now appear in the User Data Sources list. Select OK.

  5. You will now be able to use the new ODBC connection in a 3rd party application of your choice that supports ODBC.

Using the ODBC Connection to Sage 50:

Any application that support ODBC connections can use the ODBC Data Source you created above. In this example, we will use Microsoft Access to retrieve data from Sage 50 via the ODBC connection you created above.

  1. Open Microsoft Access and select File, Open.

  2. On the Open window, choose ODBC Databases() from the Files of type drop-down menu.

  3. Select the External Data tab, then select ODBC Database

  4. Select OK

  5. On the Select Data Source window, click the Machine Data Source tab.

  6. Choose the ODBC data source that was created in the steps above and select OK.

  7. Enter the User Name and Password on the login window.

  8. Select one or more tables from the list (hold down the control key to select more than one table) and click OK.

  9. You will now see the selected tables listed in the database window.

Related articles