Update Multiple Access Databases VBA

Nicole24

New Member
Joined
Jan 20, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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!

VBA Code:
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
For the last question look at docmd.setwarnings, e.g.

VBA Code:
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:
SQL:
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

New Member
Joined
Jan 20, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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.


1620842158102.png
1620843839355.png
1620844232682.png
 

Nicole24

New Member
Joined
Jan 20, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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! :LOL:

VBA Code:
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

1621380647533.png
 
Solution

Forum statistics

Threads
1,141,572
Messages
5,707,156
Members
421,494
Latest member
Chiggi_897

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top