Replies

In order to conserve space on our AS/400 system, we recently pulled 4 years of accounts receivable history into an Access (2000) database. Total records ~ 3.5 to 4 million, 20+ fields, indexed on 4 of the fields. After all the conversion work was done, the database was burned into a CD-ROM, taking up about 450MB. A separate database was created to hold all the queries and reports, linking back to the AR History database – which will allow us to insert different AR History CD’s with other years’ data without having to revise our data retrieval DB.

Now for performance:

The whole system was set up on a 400Mhz Celeron PC, 64MB RAM, Win98A. While creating the history database, updates were understandably slow due to the indexes. Compacting the database was a 20 minute operation (so the “compact on close” option had to be disabled). Accessing the data was extremely fast, since each possible search criteria was indexed. Report data could be printed in its entirety, as was the only option previously, but now we could also print specified portions of the report, or just view the information on-screen.

Splitting the DB into the two components gave us more desirable performance. Accessing the history DB through the link instead of from within the same database was marginally slower, but still quite fast. This also would allow us to load the 150K query & report DB on the user’s PC, set to access the 450 MB history DB remotely through the network or more directly through their own PC’s CD-ROM.

As an extra benefit, we were also able to attain much more flexibility and speed in accessing data than was previously possible when this information was being accessed from the AS/400. The only way to access the information online was through running an interactive query (as opposed to sending the query out for batch processing). Any request for information had the potential to drag the whole system to a near-standstill. Overcoming this problem was another consideration when we were converting to an Access-based system.

Standard “Diet Ad” disclaimer — individual results may vary.

I think a more elaborate explanation of what we are trying to accomplish with the dbf files is needed…

We have a number of distribution centers, each with a different number of delivery drivers. Each delivery route is assigned a unique route number, which serves as the basis for the filename for their weekly delivery data. The route numbers are grouped by DC, so that route numbers within a given range will always be assigned to the same DC. As routes are added & deleted, merged & split, the route numbers within each DC may develop gaps in the numbering; existing routes are never renumbered.

Each set of weekly data files are saved in a different folder, WK01 to WK52. When the new data files are to be loaded into Access, the current week’s files are copied to a transfer directory. The TransferDatabase macro commands are intended to create links to this stationary target directory, linking each file that _may_be_in_use_ for the week being transferred into the permanent data table.

I feel linking is the better option over importing, if only to keep the overall size of the database from ballooning out of control. The “TransferDatabase / AppendQuery / Delete Linked Table” macro works, but whenever it runs up against a file that does not exist the error message boxes start popping up. Convert the macro into a module, and error handling controls enable the whole process to run uninterrupted.

We need the process to be push-button simple, since it will be loaded up at each distribution center and used by non-Access’ers. It must also be flexible enough to handle the range of route numbers needed without having to be re-programmed continuously.

I havn’t had a chance to try creating the “button” yet to run the module as a function, but on the surface it does look do-able. (I’m very much a novice when it comes to VBA programming, and most of the rest of what I do with Access is self-taught.)

Thanks for the help so far.