You do like to find the hardest way to do something
I'm wanting to export ( tables, queries, etc.) to another .accdb file for testing, and then roll back into the production .accdb.
just use filecopy to copy the .accdb to somewhere else. To take individual tables/forms etc make changes and copy back is a route to breaking things, particularly to a production db. tables in particular you cannot send back otherwise any data input into production since you took your copy will be lost. And rather than exporting to, consider importing from
Investigate the use of DDE queries to make changes to a production table
2. If the object is a table:
a). Specify a criteria (optional)
b) Specific records (optional)
c) Specify “and” or “or” for the above two conditions
Presume you have found out how to link tables. Failing that you can just write a query. There are 3 methods
Basic
Code:
SELECT *
FROM myTable IN 'C:\Path\mydb.accdb';
if password involved
Code:
SELECT *
FROM [MS Access;PWD=password;DATABASE=C:\Path\mydb.accdb].mytable
where multiple tables involved
Code:
SELECT *
FROM myTable1 INNER JOIN myTable2 ON myTable1.PK = myTable2.FK IN '' [ms access;pwd=;Database=C:\Path\mydb.accdb];
these can be converted to action queries as required.
All available in the access query GUI, just complete the appropriate properties. Recommend use the sql as above, modified to something real in your world, return to the gui and view properties rather than guess what goes where
What’s the best way to tackle a project like this, with macros, code or something else?
personally wouldn't touch macros. 'Best way' depends on what you are trying to achieve. There is often more than one way, I'm a believer in doing as much as possible in SQL with regards data, leaving code for navigation and presentation. there are others here who like to do as much as possible in code. You might want to take a look at my free version of Access Studio, downloadable from here https://www.accessforums.net/showthread.php?t=86393
probably won't meet your requirements, but one way of working
The recommended method of working is to have three environments, Development, Testing and Production. Testing should mimic the production environment as closely as possible in terms of network connections so it will reflect real life. And I presume you develop testing scripts (or to be more precise, your users should) based on the specified requirements of the app and its modifications