How do I connect to the program using ODBC/OLEDB?

CAUTION: Sage support can't assist with third-party products, hardware, report customizations, or state and federal tax questions. Refer to our Scope of Support for more info. Contact your Sage business partner, network administrator, or accountant for assistance.

 NOTE: ODBC connections won’t work with 64-bit database programs or 64-bit versions of Microsoft Excel. You’ll need to use the 32-bit version. 

1. 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, and then press Change.
5. Enter a password and confirm it.
   • The password must be exactly seven characters in length and contain at least one letter and one number
6. Click Close to close the window. You're ready to use an ODBC or OLE DB connection for read-only access to your company data.

2. Set up a data source

ODBC data source

Before you can connect to the Sage 50 database via ODBC, add the Pervasive database engine as an ODBC data source:

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 User DSN tab.
5. Click the Add button on the right; then follow the appropriate steps below.

• 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 will 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. Enter a Data Source Name and an Address.
   • The Address is the IP Address or the name of the computer with your Sage 50 company data
3. Click Create.
4. Browse and select your individual company folder for the Dictionary Location. Click OK, enter something unique in the Database Name box, and then click OK on all screens.
5. The Data Source Name will now appear in the User Data Sources list. Select OK.
6. You’ll now be able to use the new ODBC connection in a third-party application.

• Using the ODBC connection to Sage 50

  Any application that supports ODBC connections can use the ODBC Data Source you created above. In this example, we’ll use Microsoft Access.

1. Open Microsoft Access, and select File, Open.
2. On the Open window, choose ODBC Databases() from the Files of type drop-down menu.
   • In Access 2016 click Browse and c ODBC Databases() from the Files of type drop-down menu, then skip to step 5
3. Select the External Data tab, and 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 you 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 and click OK.
   • Hold down the control key to select more than one table
9. You’ll now see the selected tables listed in the database window.

OLEDB connection

• Connecting to Sage 50 using OLEDB

Using the OLEDB connector, an application can connect directly to a database. We’ll use Microsoft Excel for this example:

1. In a new Excel spreadsheet select Data, From Other Sources, From Data Connection Wizard, ODBC DSN.
2. Click Next.
3. Select the Data Source Name you created.
4. Enter the User name and Password to access data from Outside of Sage 50 created in Section 1.
5. Select a table from the list, click Next, and then click Finish.
6. Click OK on the Import Data screen.
7. Enter the User name and Password to access data from Outside of Sage 50 and click OK.
8. Confirm that the data populates in the worksheet.

Can you create an ODBC connection with a 64-bit version of Excel?

Where can I find a list of the data files available for use with Crystal Reports, OLEDB, and ODBC?