Update Multiple Access Databases VBA

[Nicole24]

Hello,

I am looking to create a macro in one main access database that will update other access databases and then return data to my main database based on the parameters I put in. Below is an example of a macro I currently use that has been converted over to VBA. What I want is for the Date Range which is an update query, and the Append 2021 Test Table to live inside another database. The appended table is set to update the table in my main database when run, but I'm not sure how to write that into the VBA. Also, and this is more of a bonus question but I'd like to include it in my macro if possible, is there a way to remove all of the notifications of deleting and appending queries using VBA? I'm still relatively new to VBA, and am self taught so please bear with me. Thank you!

Function Test1()

    DoCmd.OpenQuery "Delete Test Table", acViewNormal, acEdit
    DoCmd.OpenQuery "Date Range", acViewNormal, acEdit
    DoCmd.OpenQuery "Append 2021 Test Table", acViewNormal, acEdit
    DoCmd.OpenQuery "Test Query", acViewNormal, acEdit
    Exit Function

End Function

 

[xenou]

For the last question look at docmd.setwarnings, e.g.

docmd.setwarnings false
'//query stuff
docmd.setwarnings true

For the first, one solution is to have the other dbs linked then you can use those tables as if they are in the same database. This would be very easy and probably a common solution especially if the other tables are all in the same other database.

Another solution (less widely known I suspect) is to use a remote query, such as:

SELECT
    [Orders Qry].*
FROM [Orders Qry] IN 'C:\msoffice97\Office\Samples\Northwind.mdb';

https://vb123.com/remote-queries-in-access/

Also if you get into DAO or ADO programming you can also open connections to other databases in code with these libraries also.

 

[Nicole24]

The docmd.setwarnings worked like a charm! Thank you!

The Remote Queries is very cool, thanks for the link. Unfortunately, it appears to return all data. I need to be able to select specific data based on certain dates. My current setup (first image) has 10 linked tables, which works fine. However, the linked tables max out of space after inputting about a years worth of data. So when I need to pull data for a time period that isn't in the same year (i.e. 12/05/2020 - 02/02/2021) I have to open two separate databases, run a report, and then put the two reports together. I could link all of my tables to my main database for every year, but what I have currently is already a lot to look at. Ideally, I'd like to have all of the original data of a similar data type linked to one database, and then that database kick back to my reporting database.

Example:

Focusing on Data Table 1. Data Table 1 in the first image is for 2021 data. I would like a database of only Table 1 links (second image). When I run a report that needs Table 1 data, I want to run the Date Range query and then run all the Append queries (third image) that are in my Table 1 database. So for my 12/05/2020 - 02/02/2021 date range Table 1 2018 = 0 appends, Table 1 2019 = 0 appends, Table 1 2020 = 100 appends, Table 1 2021 = 50 appends. The appends are already linked to update a table in my main database when ran. I just don't know how, if at all possible, to trigger the running of the Date Range and Appends in my Table 1 database from my reporting database.

 

[Nicole24]

xenou,

I spoke too soon. I took a look at DAO and ADO programming that you mentioned in your response and I figured it out! Posting an example of what I did below in case it'll help anyone else.

I'll have several "Database 2" setups that will all append back to tables Database 1. After the initial setup of these databases (aside from adding on new years), I will only have to open up Database 1 to run data!

Function Test1()

    DoCmd.SetWarnings True
    'Notifications are on
    DoCmd.OpenQuery "Date Range", acViewNormal, acEdit
    'Run update query "Date Range" which allows me to choose specific dates. The table that gets updated by this query is linked to the second database and is apart of the append queries similar to the third screenshot in my last response. 
    DoCmd.SetWarnings False
    'Notifications are off
    Dim db As DAO.Database
    'db = DAO.Database
    Set db = DBEngine.Workspaces(0).OpenDatabase("*full file path and file name of other database")
    'Link to the Database I want to get the data from
    DoCmd.OpenQuery "Delete Test Table", acViewNormal, acEdit
    'Removing old data from the table I'm appending to
    db.Execute "Append 2018", dbFailOnError
    'If any of the dates from my "Date Range" are found here it will append to a table in my reporting database
    db.Execute "Append 2019", dbFailOnError
    'If any of the dates from my "Date Range" are found here it will append to a table in my reporting database
    db.Execute "Append 2020", dbFailOnError
    'If any of the dates from my "Date Range" are found here it will append to a table in my reporting database
    db.Execute "Append 2021", dbFailOnError
    'If any of the dates from my "Date Range" are found here it will append to a table in my reporting database
    DoCmd.OpenQuery "Test Query", acViewNormal, acEdit
    'Opens the query with all of the specified data I am going to report on
    Set db = Nothing
    'Release db
    
End Function