Updating MakeTable Queries for a new version of the database

[snappyfridge]

Hello, all. I've got a complex MS Access database that I duplicate each year (e.g. 2023 to 2024, so that I can refer to the complete database as it existed the previous year (for looking up historical records, etc.). When I perform this duplication, I update all the data sources from one file path to another (e.g. from the /2023/ path to the /2024/ path) and that works fine. My problem is I use maketable queries extensively, with hardcoded table names, and although I update those as well, I always wind up missing one or two of them, after noticing data problems in the database. Does anyone have a suggestion for a better method? I'd like to make this yearly duplication as easy as possible.

 

[Gasman]

I would have thought one maketable query with parameters for source and target would be enough?
Perhaps a table that holds those values, so you can obtain them in a recordseta and just run the sql in vba?

Export query results into another db

Private Sub Command0_Click() Dim objAccess As Object Dim strdoc strdoc = C:\\2003 Deployment Data\" strdoc = strdoc & "2003 DA Data_be_v2.mdb" Set objAccess = CreateObject("Access.Application") With objAccess .OpenCurrentDatabase strdoc .Visible =...

www.access-programmers.co.uk

Why not have a template DB of all the tables etc required and just copy that each time?

 

[jdraw]

Tell us more about your application and the "set-up-for-new-year" process.
For consideration:
1-take an extra copy of your year-end data base, name the database New Year, delete all the data,
At this point yo have all tables--just no data--What am I missing?
=or=
2-List all the tables you need, make sure you have a make table for each. Create a routine to do the set up automatically.

We need more details/context of your process.

 

[XPS35]

The best method is not to duplicate your database. One database for all years is the best. Year (date) should be a field in your tables and should not be in the name of a database or a table.

 

[snappyfridge]

The best method is not to duplicate your database. One database for all years is the best. Year (date) should be a field in your tables and should not be in the name of a database or a table.

Agreed. But I'm beholden to data sources created by others, and each year there are tweeks to those sources (field name changes, fields deleted/added, etc.) I have no control over those sources, so all I can do is react to the changes. That's the reason for the complete duplication of the system from year to year.

 

[The_Doc_Man]

Agreed. But I'm beholden to data sources created by others, and each year there are tweeks to those sources (field name changes, fields deleted/added, etc.) I have no control over those sources, so all I can do is react to the changes. That's the reason for the complete duplication of the system from year to year.

Then the question has to include: does your problem have anything to do with the name-changing data sources rather than the replication process you use to prep for the new year?

As to replicating your DB, look on your ribbon in the DatabaseTools section for External data. You have the option to copy everything all at once to a new database AND if I remember correctly, you can choose to NOT export the data but only the structure. Seems like that would be a lot faster than a bunch of queries. Then you could immediately go about renaming the tables and such in the new copy.

In that same place on the ribbon you can find the Dependency tools that would at least identify every query, form, and report that relies on a given table so that you can make a list of places to change when you change a table name. You would have to make a project-wide search for any table names to make a list of code places that need attention.

I haven't used the autocorrect feature very much so if someone with experience in that feature can advise on using it to update table references, that would be useful. But if you do that, do it in a way to keep notes for next year.

 

[Pat Hartman]

all. I've got a complex MS Access database that I duplicate each yea

Therein lies your problem. It is bad enough to duplicate the database every year but there is absolutely no reason to ever use year in the name of any object. By definition, the ENTIRE database is all for a single year so naming objects to include year is like a painful stutter and you pay the price every year.

Just because your source changes their export format, doesn't mean that you can't modify your input format to keep in sync with their changes. Once the data is imported, there should never be a need to reimport a file from years ago. So, that isn't an excuse.

At this point, if you insist on sticking with your method of duplicating the database every year, make this the LAST year that you need to make any object name changes and this year fix all your object names to be generic and not include year.

Also, make tables are almost always unnecessary and all they do is cause bloat plus they force you to duplicate queries and reports rather than use arguments. So, instead of 1 query/1 report with an argument, you end up with a dozen copies. One for each month. Plus a 13th for good measure

 

[snappyfridge]

Therein lies your problem. It is bad enough to duplicate the database every year but there is absolutely no reason to ever use year in the name of any object. By definition, the ENTIRE database is all for a single year so naming objects to include year is like a painful stutter and you pay the price every year.

Just because your source changes their export format, doesn't mean that you can't modify your input format to keep in sync with their changes. Once the data is imported, there should never be a need to reimport a file from years ago. So, that isn't an excuse.

At this point, if you insist on sticking with your method of duplicating the database every year, make this the LAST year that you need to make any object name changes and this year fix all your object names to be generic and not include year.

Also, make tables are almost always unnecessary and all they do is cause bloat plus they force you to duplicate queries and reports rather than use arguments. So, instead of 1 query/1 report with an argument, you end up with a dozen copies. One for each month. Plus a 13th for good measure

Thanks so much for your reply. I'm going to review your comments and see if I can't make some headway.

 

[Pat Hartman]

If you buy MZ Tools, you get a better find and replace tool so that should help you to fix the database once and for all. Either fix it to hold all years and use arguments to control what gets selected by your queries or fix it to at least stop using year in all object names so you can simply create an empty template that you copy each year. Don't copy the database filled with last year's data which you have to delete and then rename all the objects. Create an actual empty template with proper genericly named objects. Copy the empty template and rename it with the new year

 

[ebs17]

each year there are tweeks to those sources (field name changes, fields deleted/added, etc.) I have no control over those sources, so all I can do is react to the changes.

You always have to react to unknown changes, regardless of whether you use an annual copy or an entire database. Where is there a significant difference?

What characterizes fields that change? Samples? These are probably bullet fields from poor data modeling. Importing new data is always an opportunity to restructure the new data at this point and then incorporate it into your own planned and well thought-out database structures. You also gain the improved ability to carry out data evaluations over several years.
Own planned and well thought-out database structure - this means that you have your own field names according to your own required functionality. If you let an external file dictate what your own names and structures look like, you are quite haphazard and a flag in the wind.

I use maketable queries extensively

These take on structure and data, but not indexes, and you should also think about relationships with referential integrity.
With a complex(!) database, this is a considerable effort, which will be no less than creating a qualified import of data into your own database.