I have used MS Access for 15 years to do various types of reporting and it is my defacto tool of choice with input data that looks like some form of database and a report as output, especially when it has multiple pages, requires calculations, or data to be joined together from multiple tables.
One thing I have been aware of over the years is that there are numerous ODBC drivers about that don’t like giving a linked live view of their data into queries that are joining multiple tables. I can’t quite explain this but it is not uncommon in a production situation to get problems when linking data via an ODBC connection to see error messages or missing data.
The three situations I have seen this in are:
-
A holiday job where I had to write reports to extract data out of an accounting system and display it.
-
Integris, customised reporting from data in the SMS
-
Excel, linking worksheets into Access for reporting
In all three cases working live with the source data (Access linked table) via ODBC seems to be the problem. The solution in all cases is to implement an intermediate step whereby data is imported into a table in the database, and then that table is used to provide the data for reports. This has proved necessary so many times that it should almost be considered routine.
The second part of this article is that I found that in the process of running make-table queries to import the data I would get an error that I was violating the range of values that could be put into a table. Now, for a make-table query, that doesn’t make sense. The table is being created from scratch to contain some data. This couldn’t possibly make sense unless Access thinks that somehow it knows what type of data is supposed to be in each field of your new table. Maybe it looked at the previous version of the table; the one you may have just deleted before you ran this make-table query. But the whole point of a make-table query is that it creates the table from scratch. If I wanted to update the records of an existing table I’d code the query as that. Access asks you to delete the table and then gives you the error message. If you delete the table manually and then run the query, you don’t get the error.
This unfortunate design “feature” of Access 2007 and Access 2010 cannot be turned off. I’ve read that there wasn’t a problem in previous versions of Access. The only change I was making was to increase the length of a text field and put more data in it. The default text field length is something like 255 if I remember rightly. But Access must have somehow locked that text field down to the exact length and in this case when the length changed it threw the error. Since it has already deleted the table and is creating it from scratch that shouldn’t actually be a problem. Note that here we are importing data from Excel linked sheets.
Further to the above: the whole Access importing/linking from Excel seems to be a complete crock. I think it’s a very sad situation when data can’t be linked from a product made by the same company without problems. Actually, there are problems all across different MS products, the whole linking/embedding thing seems to be overblown. Had to make even more changes to the report I was working on trying to get around all the issues, now I’m wondering if it is really ODBC or Access itself is at fault.