Adding to existing VBA to connect Access DB to Excel sheet

[snuffnchess]

I am very new to VBA.... this is the first file that I have ever created that uses it.... so I know I have a lot to learn still... and am trying to learn as I go.

Im hoping that somebody can help me with this....

The first tab that opens in a worksheet directs users to select the location of an access DB.

The information inside of the DB updates once, maybe twice a year. So upon that first connection to the DB, before anything else is calculated in the workbook, I need a macro to check the Last Update "date" on the Surcharge tab. That date will reference a field inside the "Date" table in the access DB. If the dates do not match, then I need data in excel tabs DAS, UPS Tarriff and Surcharge to update.

If the dates match, then nothing needs to happen and we can proceed as normal.

DB Tables that would need to be referenced for updates are:

NDAS, NDA, 3DS, 2DA, GND, Surcharge, Date, DAS

Info from tables NDAS, NDA, 3DS, 2DA, GND would show up in the "UPS Tariff" worksheet
Info from table Surcharge would should up on the "Surcharge" worksheet
Info from Date table should show up on the "surcharge" worksheet
Info from the DAS table would show up on the "DAS" worksheet.

Thank you in advance for your help. Looking forward to figuring this out.

[pjwhitfield]

To check a single value from the database I would read the data into an array and then cross reference the 1st element of the array (alternatively you could drop it into a cell on a worksheet).

Whenever Im working with ADO I use a couple of different Functions to save rewriting the whole connection strings etc, below is my example one to read data into an Array

Please Login or Register  to view this content.

Then whenever I want to pull in data I simply call the Sub like so:

Please Login or Register  to view this content.

[snuffnchess]

Thank you for the response... that did go a little over my head, however.

If i am understanding, the above code would check to see if the "date" in the DB, matches the "date" on the excel sheet... is this correct??

[pjwhitfield]

Thank you for the response... that did go a little over my head, however.

If i am understanding, the above code would check to see if the "date" in the DB, matches the "date" on the excel sheet... is this correct??

No, the above code runs whatever SQL you give it and outputs the results to an array.

You would then compare the values using something like:

Please Login or Register  to view this content.

[snuffnchess]

So how would the code look like to Just have a check of

If DB table name Date does not equal Surcharge!B3

return message "need to update"

If you can help me figure out this part of it, I think i can learn from that code to make the rest of the tables update.