1. Start the MS Access app by holding down the shift key while double clicking the Access.MBD file (bypasses any Macro or Switchboard Screen that could prevent access to the tables)
2. Select to access all tables only
3. Right click a MAX Table and rename it to (table name)_old
4. Click on External Data and select ODBC Data Source
a. Note: If you have not created an ODBC Data Source to SQL Server MAX DB then do it now. Use a 32 Bit Data Source.
b. Open ODBC Administrator
i. Windows Key > Search ODBC > 32 Bit ODBC Admin
ii. Select System DSN
5. Click Add and select SQL Server Native Client XX.X Depending on SQL Server Version
6. Enter Description and DB Server Name where SQL Server is installed.
7. Select Authentication Method either most have the standard SQL report user and report password.
8. Click through the remaining item taking all defaults – run the test to check you’ve connected and click ok to save the data source.
Now – go back to access and once you’ve click the External Data Tab and Click ODBC Database you’ll see something like this. Select Link. Click OK.
9. Select Data Source and Click OK
10. Enter the password “report” and select the DB click ok
11. Select the table and check Save Password, click OK. When prompted select to save the password.
12. MOST IMPORTANT STEP
The newly connected SQL table will be named “dbo_Code_Master”. YOU MUST CHANGE THE NAME TO MATCH THE OLD ONE SO YOUR APPS USE THAT NAME. Otherwise you have to change it in every query and macro.
So Change it to the name of the old one.
Before – WRONG.
Rename to the Old Name
Another important note is that many tables in MAX use a BIGINT data type. MS Access cannot handle these – in such a case you need to create a View in SQL server – connect to it and then rename it to the old name. This is also true of some date fields.