Dynamic reading of list

[MattH1]

Good afternoon,

I am looking to create a VBA code that will run append Excel data to the database to which it fits. Below is sample code to help me explain what I would like:

Date	Unique ID	Bundle	Company
10.16	123	555	00112
11.16	124	444	00113
10.16	125	333	00114
12.16	126	555	00115
10.16	127	666	00113
11.16	128	888	00117
10.16	129	999	00112
12.16	130	444	00112
10.16	131	555	00117
11.16	132	333	00113
10.16	133	123	00112
12.16	134	456	00117
10.16	135	789	00117
11.16	136	321	00115
10.16	137	654	00117
12.16	138	987	00113
10.16	139	456	00115
11.16	140	987	00112
10.16	141	123	00115
12.16	142	555	00113

<tbody>
</tbody>

This data contains different months (as shown by 10.16, 11.16, 12.16 currently). These months are subject to change, as the next set of data that we get could include 01.17 and so on. We have created a MASTER database for each month's data. We would like to take this new data and append it to the master database for that month.

Currently, I run an Excel macro that creates a new tab containing all of the months/years that need to be updated. Then, it filters the data by month and imports each month into its own worksheet. The new tab is just a list in column A of the tab "Dates":

Dates

10.16

11.16

12.16

<tbody>
</tbody>

Is there any way I can have Access read this tab and know to run an unmatching/append query to the corresponding Master Access Database? All of the tabs have the same columns so it should make this an easier feat, but I'm not sure how to approach it. I know how to make unmatching/append queries but I'm struggling on making it read the list and make it dynamic to update based on the new months/years that the data has.

Let me know if I can clarify anything, all help and comments are appreciated!!

 

[MattH1]

I hope this clears up anything people don't understand or makes it easier for some:
I have a bunch of databases linked to a master database, each displaying a month/year (10.16, 11.16, 12.16, 01.17 etc...) When I get new data, I would like to do an unmatching and append query between the correlated database and the new data (Master would be called Master1016 and the import file would be called (1016) that I bring in, but the data I bring in can be any set of months. Can I make an SQL or VBA code that will dynamically run the query no matter which dataset I use? They all have the exact same columns.

For example: Run this program 3 times because there are 3 updated sheets, namely 10.16, 11.16, 12.16. For each one, open their respective master database and do an unmatching/append query for each. The queries themselves only vary by database/import name (and even come from the same folder when imported so it would just be changing the name), everything else is exactly the same. Let me know if there's any way I can do this, any and all help is super appreciated and this is semi-time sensitive but I understand that things like this take time, I'm still very new to Access (let alone SQL).

 

[Micron]

I think I get it, but I've been wrong before. Some things are fuzzy, like having queries in a folder??
If you're creating a db for each month (you must have a PILE of data every month!) then maybe name the db file the same as the corresponding worksheet and from the db side, import the worksheet data whose name matches the db? After you create the new db each month, you would not have to tell the db anything from month to month, it just looks for the data file with the same name (without the extension of course). As for the query side, I think an unmatched query is not required if you're running it against the worksheets and they haven't been edited. If they do get edited, then OK, but it might be easier to run two steps - Update and Append. Turn off warnings and get the values from the worksheets. If they are the same, they won't change. For the append, if the table record PK already exists, you won't be notified of the conflict but new records will be added. In short, I prefer to import data into Access so I lean that way.

Each month you have about 2GB of data?

 

[MattH1]

I think I get it, but I've been wrong before. Some things are fuzzy, like having queries in a folder??
If you're creating a db for each month (you must have a PILE of data every month!) then maybe name the db file the same as the corresponding worksheet and from the db side, import the worksheet data whose name matches the db? After you create the new db each month, you would not have to tell the db anything from month to month, it just looks for the data file with the same name (without the extension of course). As for the query side, I think an unmatched query is not required if you're running it against the worksheets and they haven't been edited. If they do get edited, then OK, but it might be easier to run two steps - Update and Append. Turn off warnings and get the values from the worksheets. If they are the same, they won't change. For the append, if the table record PK already exists, you won't be notified of the conflict but new records will be added. In short, I prefer to import data into Access so I lean that way.

Each month you have about 2GB of data?

Hey Micron thanks for taking a look at all of this! Each Month we have close to 300,000 rows of Data and each Month needs to accessible as its own database (with each month being automatically linked to the master database I'll be running the queries from.)

After countless hours of looking at this, I believe I need to use an Unmatching and an Append Query. I have old and new cells in my incoming data, so I have to use Unmatching to find new cells and Append to add them to the database.

I have a VBA code that is currently looking at my DATES tab and reading the values and opening those databases. How would I create the dynamic query that would do Unmatching and/or Append based on the value of that table? I'm not sure I understand your solution, I'm looking for SQL or VBA (or both) code that would allow me to run a singular query with changing tables/databases (the month/year would change meaning it's different sheets, not the columns themselves.)

 

[Micron]

I've had to re-read and re-re-read your posts to see if I can get it all to gel.

You have a db for each month of one or more years, which you call a MASTER (it's really a Monthly Master?).
You have one workbook with a Dates worksheet. A macro updates this worksheet in a month/year column
Values in that column identify which month/years, such as 10/16, need updating somewhere
That macro also creates a sheet in the (same?) workbook with month/year name. There would be a worksheet corresponding to each value in that column. The data in that worksheet is what needs to be uploaded to the db which has the same name as the worksheet.

If that's correct, then what I don't understand is, what are the possibilities after that?
What if updates are run again in the same month?
- Is another worksheet created somewhere? - Is the existing one over-written?
Or do updates never happen twice in the same month/year?

I think you're asking if an Access db can work with the workbook and run these queries in other Access databases (one db for each workbook dates column value).

 

[MattH1]

I've had to re-read and re-re-read your posts to see if I can get it all to gel.

You have a db for each month of one or more years, which you call a MASTER (it's really a Monthly Master?).
You have one workbook with a Dates worksheet. A macro updates this worksheet in a month/year column
Values in that column identify which month/years, such as 10/16, need updating somewhere
That macro also creates a sheet in the (same?) workbook with month/year name. There would be a worksheet corresponding to each value in that column. The data in that worksheet is what needs to be uploaded to the db which has the same name as the worksheet.

If that's correct, then what I don't understand is, what are the possibilities after that?
What if updates are run again in the same month?
- Is another worksheet created somewhere? - Is the existing one over-written?
Or do updates never happen twice in the same month/year?

I think you're asking if an Access db can work with the workbook and run these queries in other Access databases (one db for each workbook dates column value).

You're spot on with what I have to do at the beginning.
Once the months/years are identified for updating, it should import those new sheets (I make those sheets with a macro after filtering the incoming data and put it in a folder with today's date).

To answer your questions: These sheets are created weekly and stored by today's date, which is how I am finding and importing the correctly updated sheet into access (column data will be changing and data will be added to previous month data that needs to be updated/appended. I know it sounds weird, but it's because it only adds the data once the process for it has been completed, which may not be done for 3-4 months.)

Each Month MASTER file has all of the data for that month. As the updates/new data comes in on the weekly sheet, I need the info updated/appended. This is where I'm having trouble, as I have no problem making an append/update query but I have trouble understanding how to make the query run for each month in the dates column and know which MASTER to update/append and which Excel file to use to update/append.

For example: In folder C:\Exports\01.12.17\ I will have run my Excel macro and created the files: 10.16, 11.16, 12.16, Dates. The dates file contains one column (A) that has, in A2-A4, 10.16,11.16,12.16 (and could have more if more months needed to be updated/appended. This means that the data in C:\Exports\01.12.17\10.16 needs to be imported into Access, the database 10.16 needs to be opened (I can have it as a linked database) and the MASTER needs to be updated and have new info appended.

I know I repeat myself a lot here but I'm trying to state it different ways to make it crystal clear.

I want to once again thank you for taking the time to read and go through this entire wall of text. I know it's a lot and I'm eternally grateful that you're taking this time out of your day! Thank you!!!

 

[Micron]

See if this sounds like what you want to have happen:
- a master db (not a monthly db) would read the dates column in one workbook (e.g. first date is 10/16) (not sure of the wb name)
- for each date, the master db would open a monthly db by the same name as the first date (e.g. 1016.accdb)
- the master db would cause the monthly db to run an unmatched query against the 10/16 worksheet in 10.16.xlsx, comparing that sheet to a table in 1016.accdb.
- **the records returned by the unmatched query would be the basis for an append query, which would get those rows from worksheet 1016 in 10.16.xlsxand append that to the table in 1016.accdb
- the master db would close 1016.accdb and close 10.16.xlsx and get the next date in the dates column of the original workbook.
- rinse and repeat

**the append query in this scenario will try to append records to a table even if the record differs by only one field, assuming you can include enough fields in the unmatched query to detect any changes. If the target table has any unique indexes, you would likely not be able to perform the append for those records.

If that's not the goal, I'm afraid I'm just too thick to grasp the whole picture. Either way, without a lot of user interaction such as opening monthly db's and running queries against linked sheets, I can only see this being possible via Automation. This would be the means by which to have one db open other db's and cause them to access specific worksheets and run queries, all based on values in one worksheet.