I received a task today to update a demo / WIP Access system which will be upgrading an existing Access system.

Many parts of the system are the same, the upgrade is meant to address importing data from a new datasource. The customer is currently using both systems and comparing them prior to finalizing the switchover to the new datasource.

Ok, so with this situation in mind. There are some tables that are manually updated by the customer and they are wondering if I can quickly capture those changes into the upgraded system. They aren’t pointing at the same tables because I wanted there to be no crossover and avoid potentially confusing problems of one system updating another while testing.

In this case, I decided that a one way copy on a schedule of the data from the existing system to the upgraded system will be the best way to handle it. All the data in question are stored as identical tables in two different backend files. I’ll refer to them as a target backend and a source backend.

Now comes the decisions about copying the data. A couple of thoughts:

1. I could empty the tables in the target system and fill them with the data from the source system.
2. I could delete the tables in the target system and copy the tables from the source system.
3. It is probably a good idea to backup the tables prior to emptying or deleting them, although I wouldn’t want to waste too much space.
4. In terms of options 1 and 2, I could use VBA and the DAO object. Or I could use VBA and OLE and use SQL statements.

I already have a skeleton Backend Upgrade module I use to store structural changes as SQL statements. It is using OLE connections. So it probably makes sense to do that. Since it is connecting to files, I could easily create backup Access files and transfer tables to them. And working with multiple files in multiple tables is much easier with OLE vs DAO, so I think I’ll use that technique.

Oh boy, a new Access adventure. Let’s see where it leads in the next message!