Export PO Header and Lines to MS Access Sage 100 Advanced 2021- Lines wont export.

Question

I am having difficulty exporting PO's from Sage 100 2021 Advanced to MS Access via the ODBC connection. I can export headers just fine but I can't seem to export lines. I know I can get the data via linked tables, but it doesn't allow me to do what I need after the fact. I have tried to find any documentation that explains the requirements and might address any misconceptions I have on the configuration but I can't find anything. I contacted support and got nothing but a reminder that I needed to remember the 32 bit/64 bit rules and that the database needs to be correctly configured to accept data Since I can export the headers just fine, I have to assume that this issue is more complex than those 2 considerations. Has anyone ever done something similar or know where there might be some documentation ?

ChrisFix · Accepted Answer

Sorry, I should have been more clear, in how I was doing it. I am using V/I to export the data via the ODBC. Unfortunately the linked table or import to access via access natively wasn't an option in my use case. I did through trial and error solve my issue. I started over and setup a new table with all fields as short text, limited the field names to 8 characters or less and limited the number of fields I was pulling from both PO header and detail, where originally I was pulling all fields from both in case they wound up being needed later. My gut tells me it was a combination of the 3 things, but once I had what I needed it didn't feel like testing the number of fields, but in case someone reads this later, I figured I would include the information. Thanks to all who tried to help!.

Dan Riddle · Answer

If you are trying to IMPORT the data from Sage 100 to MS Access (using the ODBC connection) which sounds like what you mean? or are you trying to Export a Report to MS Access via a Sage 100 "export to" option of some kind?...have you referred to Knowledgebase article " How to import data into Microsoft Access or Excel from Sage 100" (article # 51308)? a short summary of steps for MS Access would be: 
 Microsoft Access: 
 
 Select File, Get External Data, Link Tables (or 'Import'). Note: If 'Link Tables' is selected, the data can be viewed but not changed. 'Import' will create a new table within Access that can be edited. 
 Select ODBC Databases() from the 'File of Types' drop-down box (Usually the last item listed). 
 The Select Datasources window should appear and default to Data Sources in the 'Look in' field. Note: If 'ODBC Databases()' is not listed, reinstall MS Access with ODBC Functionality selected. 
 Select SOTAMAS90.DSN and click OK 
 The SOTAMAS90 'User Login' dialog box should display, log on to the Sage 100 ERP database 
 Select the desired table and click OK. The table will appear in the Tables section of MS Access. 
 
 This only works in Sage 100 Standard/Advanced as Premium does not use that SOTAMAS90 ODBC connection. If you link to detail table you should see data. You may also want to try importing same data into Excel (also in the Knowledgebase article above) to see if it is only an issue with MS Access. note: if 32-bit Access and 32-bit Sage 100 then it should work, if 32-bit Sage 100 and 64-bit Access you would need to install 64-bit ODBC driver for Sage 100, and if 64-bit Sage 100 then it should work with 64-bit Access - if not there could be an issue there that needs to be tested further.

Sage 100

Export PO Header and Lines to MS Access Sage 100 Advanced 2021- Lines wont export.

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING