appending data to table from a dynamic (database) table

[jrtaylor]

I've attached a workbook which shows two tables. The "Database Table" is a query table, always showing 100 rows. It is connected to a live mysql database.

It gets updated whenever the query is refreshed.

The Master Table is partially static. Any data it has collected prior to the first row of the Database Table needs to remain intact.

Any help with VBA code to make this happen?

Thanks.

[dflak]

I have a clarifying question: if a date exists in the master table and that date also exists in the query table, you want to replace the information for that date in the master table with the information from the query table and append any new dates from the query table. Did I understand the requirement correctly?

[dflak]

One more question, is it OK if I add a helper column to the permanent table?

[jrtaylor]

Thanks dflak, honestly I don't think it is necessary to overwrite existing data in the Master Table. What I want to be sure of is that if I don't update the workbook for 3 weeks, then update it, the Master Table will "know" not just to look back a day... but as far back as necessary to complete itself. I hope I'm making sense.

[jrtaylor]

sure.. helper columns are fine

[dflak]

Are you looking to pass some kind of parameter to the query based on the latest date in the master table, or are you merely interested in concatenating the two tables regardless of when the query is run?

[jrtaylor]

Hmm... the ultimate goal is for the Master Table to contain every row of data that the Database Table "produced", in order of dates, ascending, without duplication. And I'd like it to do this regardless of when the database table is updated.

I have a manual constraint in that I can't "lapse" for more than 100 "days" of data, otherwise the Master Table will never contain all the possible rows once available to me. I put "days' in quotes here because you will see that the database data skips certain days (i.e. holidays and weekends).

Let me know if this makes sense.

[jrtaylor]

And it is fine to use helper cells on either worksheet

[jrtaylor]

PS the database query itself returns "the bottom 100 rows" of whatever is in the mysql database.

[dflak]

OK, I think I got it. The query is getting the last 100 days of data. If you wait 10 days then only 90 new days are added and the oldest 10 are ignored.

[jrtaylor]

Correct... except we need the last 100 rows of data, not really the last 100 days, since the database is not going to have any data at all for weekends and holidays.

[jrtaylor]

I hope this is enough. I have to run out for a while and will check back. I really appreciate your help!

[dflak]

I based this on a similar report I have.

First a couple setup items:

I converted the master table into an actual Excel Table. The main reason is because tables copy down formulas automatically and I need the formula in the helper column copied down as I add rows. The helper column looks at the Database Table and is true if the date in column A is found in that table.

I made a static named range Date_List ='Database Table'!$A$4:$A$103. If the database table doesn't actually start in row 4, change the value to reflect the new range.

I also have a count of the number true in cell J3. The code tries to delete rows, but will have an error if there are no rows to delete. I check this cell to see if there are rows to be deleted.

How the code works: When the query is run, the helper column shows where there are duplicate dates in the Master Table. The code then deletes these rows and copies in the whole database table.

Below is the code. Take a look at the constants DB1Range and DB2Range to make sure they line up with the actual database table.

Please Login or Register  to view this content.

[jrtaylor]

Thanks dflack. I appreciate this. I'll take a look later today and get back. I forgot that I have another simple macro in the workbook already, which simply does a "refresh all" for all data connections.

Please Login or Register  to view this content.

Think it would hurt to just add ActiveWorkbook.RefreshAll somewhere at the beginning of your macro? It could take 10 seconds to make the db connections and populate the database so I don't know if also a "wait" function in the db is also required.

[jrtaylor]

Thanks dflak, it works great. I appreciate your help. I'm going to try to insert the ActiveWorkbook.RefreshAll code (even though I don't know what I'm doing!) and see if it all works together. But for now I'll mark the thread solved. Thanks so much!