Sage 100

For the Access experts: When you do a parallel upgrade, what is the best way to get the Access linked tables to update to the new install of Sage 100?

Are you using pass through queries or linked tables via ODBC?

If you are using the SOTAMAS ODBC connection be sure to open up Sage 100 first and then I find that a lot of times the link will work. If not right click on one of the linked tables and select linked table manager and then you can re-link them.

LInked Table Manager - select all of the tables and then click prompt for new location check box. Unless you are using pass through queries. Those can be a bear.

I personally prefer pass through queries. Yes, the changing of the path is a bear. I had a programmer write a macro that will change all the PTQ paths with one click. No more bear!

This is the KnowledgeSync Link.mdb, which is apparently still in Access 97 format, so I guess I will just recreate it with KLF. It does not appear any actual queries have been added. (this is for an orphan - I didn't do the original). Has anyone determined if you really hose the mdb for KS if you edit it in Access 2007+ to add queries?

KLF, ugh..... Had my first experience with back in February. What a pain. So much easier using an Access DB with just the PTQ's that are needed. The canned events are somewhat comical. We are in the process of switching client from the linked tables to the PTQ's.

In Access 97, you may not have the Linked Table Manager, but it's not difficult to delete the links and re-link them. However, you will not be able to make design changes to the database with 2007. While @ScottHickman 's solution is the correct answer to your question I would add that using an Access linked table may be a dis-service to your client. When drawing from the ODBC with a link every fields is transferred through the ODBC interface. With a pass through query one can isolate only the necessary fields which in most cases results in a substantial reduction in data traffic and time. User's of my Shadow database can update all pass through queries by changing the path in 'Company Setup' and clicking 'Refresh All Pass Through Queries' in the 'Utility' dialog.

Thanks all. I know there is a better way to do it, but for right now the goal is get the alerts working today.

Shameless plug for DSD SQL Mirror...... Stored Procedures and Views are the bomb!