Jacquie, it's exciting learning new stuff isn't it. This is due to a change that Microsoft made to MS Access. To get around this you have to go into the ODBC DSN and on the Options tab check the box that says Inforce Double.

Also why are you bringing in the AR Invoice Detail? This is for unposted invoices only.

Thank  you for the information. We had a custom report in crystal designed with a selection criteria of INENTORY PART NUMBER. We used the CI history file. This report is used everyday here - we would select an inventory part number and the report would show us everytime that part number was sold along with the customer name - ship to - cost and selling price. All was good. We upgraded to the 4.4 and when we used this report an issue with the last cost was identified. When I phoned our reseller I was informed that the last cost of the item no longer agrees betweens the CI History file and the AR history detail file that I needed to re-write my report using the AR History Detail file. Why these two history files do not agree is yet another issue? So we moved forward and re-wrote the report. However the reports now averages about 4 and a half minutes to run... this is far to long. So I am playing with Access to see how I can get this information must faster.

Jacquie, I meant you listed three files, history head and detail along with the invoice data entry detail which does not makes sense. From what you posted you would need only two files, History Header and History Detail.

Pulling the data into Access will allow the report to run instantly. You will be amazed.

WOW this does return the data very quickly. Now may I ask if there is a way to set up the access report to refresh the ODBC tables automatically each night? Instead of someone having to update the linked files between access and sage.

Yes, you can set the database to open and refresh using the Windows Scheduler but you have to know something about MS Access to be able to do it.  Basically you have a form that opens when the database is opened and after a set amount of time the code to refresh the database is run. When finished the database closes. Note that since Access will break when it hits 2 GB and size refreshing increases the size it is important to set the option to compact on close.

There are several steps:

1) Create as many Make Table queries as there are tables that you want to refresh

2) Place those queries into Macro

3) Create VB script file (.abs) to be called by Windows Task Scheduler.