Re-linking Microsoft Access Databases to Softbase Evolution Advanced Data Access (ADA)

Overview

This document outlines the process of connecting Microsoft Access databases to Softbase Evolution ADA to access dealer-specific data based on standard Softbase database tables.

Requirements

• ODBC 18 Driver for SQL Server
  Install on each machine running the Access reports: Download ODBC Driver for SQL Server

• Microsoft Access 365

• ADA Credentials
  Provided by Softbase.

• Connection String
  Enter the provided username (UID=) and password (PWD=) in the following connection string and leave the semicolon following each value:

  Sample:

  DRIVER=ODBC Driver 18 for SQL Server;SERVER=evo1-sql-replica.database.windows.net;UID=EnterUsernameHere;PWD=EnterPasswordHere;Trusted_Connection=No;APP=Microsoft Office;DATABASE=EVO;

  
  

• Whitelisted IP Addresses
  Ensure that the IP addresses from which you will access the data are whitelisted in ADA.
  
  

Steps to Re-link Tables

1. Open the Access Database
   Open the Access database that you wish to create or relink tables to Softbase ADA.

2. Access the Linked Table Manager
   Go to External Data and choose Linked Table Manager. This is used to link the tables in the Access database to the Evolution database tables.

   • The table names remain the same between Softbase 3.0 and Evolution but are in different schemas.  
   • Any custom database tables used in Softbase 3.0 that are referenced in the Access database must be reviewed with Softbase to add them to ADA or add in other sources where the dealer maintains and gathers the data, ensuring your reports run correctly.

3. Relink Data Sources
   For each data source connected to Softbase 3.0 tables:

   • Select the data source, which selects all tables in that data source connection.
   • Choose the Relink option.
   • Update the connection string to the Softbase Evolution ADA connection string (provided above).

4. Update Table References
   For each table in the data source connection:

   • Remove the "DBO." schema prefix from the table name (e.g., change "dbo.Customers" to "Customers") when prompted during the relinking process.
   • If tables do not match, they may either not be used in Evolution or could be custom tables. Denote these tables and contact Softbase with any questions.
     • Example: The "Secure" table is no longer used in Evolution due to a new permission structure.

5. Save the Updated Database
   Once all data source connections and tables are relinked, save the database in your preferred Access database format under a different name to keep the Evolution and 3.0 Access databases separate.

Notes

• Custom tables or views outside of the standard Softbase 3.0 tables that exist in Softbase Evolution are the dealer's responsibility. Dealers should review these with Softbase to determine if they can be added to the ADA setup.
• Custom SQL and Microsoft Access-specific query language used against an on-premise SQL Server may need to be updated to work with Microsoft Azure SQL. This is the dealer's responsibility.
• Access queries may need to be reviewed for performance enhancements, especially when pulling data over the internet versus within a dealer-specific network.
  • Example: Querying all data from GL for the last 20 years when only a financial report for the current year is needed may run slowly and require adjustments by the dealer to pull data in a timely manner.