3305 Main Street Ste 019 Vancouver, WA 98663

How to test writing to JC Master tables via ODBC

Background
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.

Summary
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.

Steps

  1. Create an ODBC Data Source that points to your Sage 300 CRE data folder by using the steps identified in the following article:
    https://event1software.com/e1/how-to-setup-sage-300-cre-odbc-data-source/
  2. Open Windows File Explorer and then navigate to the folder where your Microsoft Office executable files are located. Click here for information about how to determine the correct folder path.
  3. Locate the MSQRY32.EXE file and double-click it to launch it.
  4. The Microsoft Query window will appear. Click the File menu and then click New. The Choose Data Source window will appear.
  5. Scroll down the list and select your data source (named in step 1).
  6. Uncheck the box entitled Use the Query Wizard to create/edit queries.
  7. Click OK. The User Selection for ODBC window will appear. Enter your Sage 300 CRE login credentials and click OK.
  8. In the Add Tables windows, select MASTER_JCM_RECORD_3 from the table list and click Add.
  9. Click Close to dismiss the Add Tables window.
  10. In the list of columns shown in the MASTER_JCM_RECORD_3 box, double click on each of the following items:
    • PJOB
    • PEXTRA
    • PHASE
    • PPCTCP
  11. Click the Records menu and click Allow Editing.
  12. 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.
  13. 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.
  14. 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.
  15. 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.