Sage 100

Client is running Sage 100 5.30P1 with DSD SOSI S/O Shipping Integration. Client installed new version FedEx Ship Manager v.32.11.1066 which apparently uses a new Integration Wizard. When click MATCH, which reads the Sage 100 tables, 'Acuiring Tables and Views'. the read times out after nine (9) minutes after reading 1078 of 2496 tables. FedEx says the database is too big, and use a smaller database. Is there a way to create a new MAS 980 ODBC Driver which limits access to only the Sales Order files? We did try creating a new Sage 100 User with access only to Sales Order Maintenance and Inquiry (using that as the DSN Login. But it still failed after 9 minutes.

What we used to have to do with UPS Worldship was create an Access passthru to Sage and connect WS to the Access files.

Beth beat me to this. Access PTQ has always saved the day for me. You don't need a version of Access on the server. Create the PTQ and copy to server. You don't need a new ODBC if you imbed the login credentials in the PTQ.

@JeffSchwenk - dang, didn't mean to steal your thunder. :-(

The least of my worries today. So good to see others carrying the PTQ banner!!

Here is a basic MDB with the SO header table as a PTQ. Edit the connection string with a Sage username, PW, company code and a valid path to the MAS 90 folder.

Ken using the technique you mentioned would only work if ODBC Security was enabled and then on ODBC tab in Role Maint you limited the tables. But I suggest instead you do what Beth and Jeff said. I've done it several times too. Don't tell Jeff but a simple Access SELECT query will only be a millisecond slower than a PTQ. The advantage with PTQ is you don't need Access installed and he provided a great example just now. Either way the idea is FedEx Ship Mgr will now see only the tables you added to the query which are typically SO_SalesOrderHeader and SO_SalesOrderDetail. IOW seeing 2 tables is better than 2,496 tables. Another thing you can do but with less bang for the buck is on your Silent Connect DSN (should be System DSN), in the Options tab you can blank out the _Path to Views DLL_. That fields exposes all the pvx views for the BI Reporter. Now perhaps 500 less tables (views) will be read. I've also observed it's safe to have DirtyReads, BurstMode, and CacheSize=4 checked instead of leaving them blank. Back in the day you remember with the older ODBC driver it was not.

Great post everyone

Thank you, I'll try it out

We actually create select statements pulling from the PTQ when writing involved Crystal reports. Crystal is a lot faster. The SELECT * FROM SO_SalesOrderHeader in the PTQ statement displays all the fields in the header table. You can just as easily write the statement to include only the fields desired. Access PTQ's is also our default method for other third party solutions (Sage Alerts, bypass the need for KLF's and also gets around the need for a special multi database license) and bar code solutions to name a few uses.

Access does NOT need to be installed on user's workstations, but just for setup on one machine. It's also possible to setup on a different network if the UNC to Sage 100 can be accurately entered. Silent PTQ'S are a one one-click feature of my Shadow Access database. Combinations of PTQ and shadowed tables are also possible.

I ran into this also. Initially I was told it had to do with the size of the Sage db/files, but then the FedEx rep essentially said it had to do with the ""software improvements"". Sounds like Sage, lol. I suggested they revert back to the prior FedEx version until they fix the issue, although we did discuss the possibility of creating a new DSN/View with just the needed files.

...I'm curious how you make out, @KennethPhillips.