Dynamic Unmatching and Append Query VBA

[MatthewH]

Hi everyone,
First time poster, I've heard lots of great things! here's the problem I'm currently having, hopefully someone can provide assistance!

I have a folder containing MASTER databases for each month/year(MASTER0816, MASTER0916, etc) linked to one central Access database where I will be running my VBA coding.

Weekly I receive an excel sheet UDPATES that contains data that needs to be added to the MASTER databases. I take this data and separate it by month/year and make them their own spreadsheets (0816, 0916, 1016) and create a "Dates" excel sheet containing the unique month/years in the UPDATES sheet.

I am looking to import this data and then run an Unmatching + Append Query against the Master database. The problem is that the months that are being updated change every week and I want it to be dynamic as possible. I currently have it reading the Dates sheet and storing it in an array (which works perfectly). I don't know how to use this data to make the two necessary Dynamic Queries that will read the array (0816 for example) and know to use the table 0816 and MASTER0816 to run the unmatching/append queries before moving on to the next array.

Pseudo-Code: Read Dates sheet for unique months/years and store into array. Read first item in Array (0816) and run unmatching/append with MASTER0816 file. Go to next item in array.

Current Code: (This works fine but I don't know how to start with the queries, I'm a newbie when it comes to that sort of stuff).

Public Sub ImportExcels()
 Dim xl As Excel.Application, xlsht As Excel.Worksheet, xlWrkBk As Excel.Workbook, rng As Excel.Range, cell As Excel.Range
Dim RowCount As Long, TheMonths() As String, i As Integer, SQLUnmatching As String, SQLAppend As String
 Dim FilePath As String, DatesLoc As String, ImportPath As String
 FilePath = "H:\"
ImportPath = FilePath & Format(Date, "mm") & "." & Format(Date, "dd") & "." & Format(Date, "yy") & "\"
 Set xl = CreateObject("Excel.Application")
Set xlWrkBk = xl.Workbooks.Open(ImportPath & "Dates.xlsx")
Set xlsht = xlWrkBk.Worksheets(1)
 RowCount = xlsht.Cells(Rows.Count, "A").End(xlUp).Row
Set rng = xlsht.Range("A2:A" & RowCount)
 ReDim TheMonths(1 To rng.Rows.Count)
 i = 1
For Each cell In rng
    TheMonths(i) = cell.Value
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, TheMonths(i), ImportPath & TheMonths(i) & ".xlsx", True
    i = i + 1
Next cell
 xlWrkBk.Close False
xl.Quit
 Set xlsht = Nothing
Set xlWrkBk = Nothing
Set xl = Nothing
  
 End Sub

EDIT: I forgot to mention this as I thought it was just a minor detail in the grand scheme of what I'm trying to do, but the Unmatching/Append queries would be based on the Unique ID: "Job ID"

 

[MarkK]

It doesn't make any sense to me to have a date in the name of a database file. A date measures a quantity just like inches or dollars or kilometers. Imagine having a 5km database, a 10km database and a 15km database. Why would you ever do that?

A quantity measure should be, at most, a field in a table. Not the name of a table. Not the name of a file.

Hope this helps,

 

[MatthewH]

It doesn't make any sense to me to have a date in the name of a database file. A date measures a quantity just like inches or dollars or kilometers. Imagine having a 5km database, a 10km database and a 15km database. Why would you ever do that?

A quantity measure should be, at most, a field in a table. Not the name of a table. Not the name of a file.

Hope this helps,

The date as the name of the database is because it's the dump of all files that have been started in that month and are now completed. It's updated as more files are completed that started in that month, so it's the efficient measure for what I'm attempting to accomplish. I know it may not be the best or most efficient structure, but it's the structure I think that will work best for me.

Thank you for your input though, I do appreciate it!! I'm looking more for the Dynamic Query portion now, I just want to figure out how to only have a singular Unmatching and a singular Append query in which I can change the tables based on the value in the array at that time (it would be a loop in VBA going through each value of the array)

 

[Frothingslosh]

And Mark is telling you is that the database setup you've chosen is the cause of your problem. The data in those databases should all be in the same database, with the date added as a field. That would remove the need to generate this monstrosity you're asking us to create for you, instead allowing you to both access and update this data with regular queries. It would also DRASTICALLY speed up processing times.

What you have is basically a multi-file database designed the way you would handle an Excel file. It's a fairly common design approach (albeit to a greater degree than most) used by people used to Excel, and it, unfortunately, actually makes things harder. In this case, if you were to simply consolidate these databases into one, you could handle everything you want with one import, a single query, and almost no VBA, and the process would almost certainly complete in just a second or two.

Also, keep in mind that NOT fixing the setup is just going to cause more and more problems for you as time goes on. These things really do take on a life of their own, and the longer you let them go unfixed, the harder they become to work with.

 

[NauticalGent]

MattewH, I understand that you may be more interested in getting this particular problem solved quickly and move on the your next challenge. But if you do not heed MarkK and Frothingslosh's words now, you will be entering the Land of Suck with a one-way-ticket soon.

MUCH sooner than you think. Transitioning from the Spreadsheet to Database paradigm is very challenging and if you don't take time to do it right now, you are going to have to MAKE time to do it over.

Best advice to you at this point is to fall back, regroup and read what those two have told you and get a full understanding of why they said what they said.

Both of them are heavy-hitters in this forum and the fact that either of them are taking the time to steer you in the right direction means you are already winning...

My two cents.

 

[MatthewH]

Hey everyone,

Thank you so much for the input and advice. My fear of putting everything into one database is that there will be too much data. I'm going to have nearly a million rows by 20 columns and more will be added weekly (probably will be closer to 1.5 to 2 million rows in a years time).

Would you still follow the same steps given that information? I know I'll have to always compact/repair the database to avoid going over 2gb of file size but I'm worried it'll still be so large it'll go over the limit.

 

[MarkK]

What's in the columns? If you have 20 columns of dates, which are 8 bytes each, then you have 160 bytes per row, times 2 million is 320000000, which is 0.29 GB, +/- 25% for indexes = 0.36 GB, and you are still below 1/5th of the 2GB size limit on an Access file.

How do you know you will always have to compact and repair to stay under the 2GB limit? Not all data operations cause bloat. Also, there are ways to work around bloat.

 

[MatthewH]

What's in the columns? If you have 20 columns of dates, which are 8 bytes each, then you have 160 bytes per row, times 2 million is 320000000, which is 0.29 GB, +/- 25% for indexes = 0.36 GB, and you are still below 1/5th of the 2GB size limit on an Access file.

How do you know you will always have to compact and repair to stay under the 2GB limit? Not all data operations cause bloat. Also, there are ways to work around bloat.

Okay I will definitely take this path and trust you guys, you're all a lot more knowledgeable than I am.
Having this new setup, how would I create the query in VBA to run the newest update?
It would be under H:\01.13.17\UPDATES.xlsx and I know the VBA code to get it would be:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, Sheet1, ImportPath & "UPDATES.xlsx", True

How would I add to my code so that it automatically runs the singular unmatching and singular append query in the access program and unmatches/appends to my Master file? The Master file will most likely be linked to the database I run the VBA Module from, though I don't see that having any effect on the code necessary.

 

[MarkK]

It's impossible for me to advise you on an import process without knowing much, much more about the structure of your data source, and the structure of your data destination. One observation I can offer is that it's very common that you need to validate incoming data before trusting it enough to append it to its final destination. There are so many things that can go wrong when reading from an excel file that I would almost never recommend that you do your read-from-excel, and your write-to-access as a single operation.

I would expect a three step operation like...

1) read from excel to temp structure (table, spreadsheet)
    - solve filename, sheets, range, column names, and so on...
    - any/all data may be incorrect in the excel source file
2) validate/clean/process data from 1)
    - make sure data can be converted to strong types
    - find/mark/exclude dupes, damaged, incomplete data
    - apply normalization restructuring of data
3) write to access
    - execute the actual movement of data from temp to final locations
    - ensure operation succeeds, handle errors including possible rollbacks

That's what I would expect the scope of work to look like if I was going to import millions of rows from possibly thousands of excel files, and validate all the data.

 

[Frothingslosh]

Both of them are heavy-hitters in this forum

While I thank you for the vote of confidence, I would describe myself more as 'active' than 'heavy-hitter'. I may know the basics, and I do understand normalization pretty well, but there are still all sorts of things for me to learn, as Galaxiom takes special pains to remind me of any time I'm incorrect. In fact, that man seems to take some sort of unholy glee in it!