How to test writing to JC Master tables via ODBC
Applications such as Event 1 Forecast, Event 1 Integrator, and Sage 300 CRE Office Connector Write utilize the Sage 300 CRE ODBC Driver to create or update records in the Sage 300 CRE Job Cost database (specifically, the tables represented by Master.JCM). This article provides a set of steps that can be used to confirm whether the ODBC Driver is functioning as expected using tools that are available as part of Microsoft Office. If the conclusion of this test reveals that data cannot be written, you should consult with your Sage 300 CRE technical support representative to determine the cause and resolution.
This test will consist of executing a query that shows a list of JC Cost Code records. You will then make note of a specific value, change it, and then save the change. If the operation is successful, you will then change the value back to its original value and save it again.
- Create an ODBC Data Source that points to your Sage 300 CRE data folder by using the steps identified in the following article:
- Click Start and then Run, or while holding down the Windows key on the keyboard, press R. This will bring up the Run box.
- In the Open box, type one of these paths depending on your version of Microsoft Office and then click OK:
- Office 2007: C:\Program Files (x86)\Microsoft\ Office\Office12\MSQRY32.EXE
- Office 2010: C:\Program Files (x86)\Microsoft Office\Office14\MSQRY32.EXE
- Office 2013: C:\Program Files (x86)\Microsoft Office\Office15\MSQRY32.EXE
- Office 2016: C:\Program Files (x86)\Microsoft Office\Office16\MSQRY32.EXE
- The Microsoft Query window will appear. Click the File menu and then click New. The Choose Data Source window will appear.
- Scroll down the list and select your data source (named in step 1).
- Uncheck the box entitled Use the Query Wizard to create/edit queries.
- Click OK. The User Selection for ODBC window will appear. Enter your Sage 300 CRE login credentials and click OK.
- In the Add Tables windows, select MASTER_JCM_RECORD_3 from the table list and click Add.
- Click Close to dismiss the Add Tables window.
- In the list of columns shown in the MASTER_JCM_RECORD_3 box, double click on each of the following items:
- Click the Records menu and click Allow Editing.
- Make note of the value in the PPCTCP column of the first row of data. You will need to restore this value later if you are able to successfully change it.
- On the first row of data, enter a new value in the PPCTCP column. The new value must be in the range of 0 to 100.
- Press the down arrow on the keyboard to advance to the next row. This will cause the data on the row you changed to be written to the database. If it is successful, no message will be displayed. If unsuccessful, you will receive an error message that will be useful for diagnosing the problem.
- If the value was successfully updated in the prior step, press the up arrow to return to that row. Enter the original value and press the down arrow to save it.