Importing specific records tables (1 Viewer)

Hi Everyone
I know this is not best practice for an access DB, however until i can find a better solution i'm a bit stuck

So my main DB has auto gen PK - i also have other related sub tables with PK and FK to my Main db.

i also have FE and BE

i have had to take a copy of my DB away from the server to work on - i set myself up with a range of main DB PK numbers from 1154 to 2000 with "do not use" on their descriptions so anyone on the server DB will know not to use them.

Now i have done my work to the main DB and all its sub Tables, how do i now import the Main db and sub Tables back to the Server DB.

i hope this makes sense and i hope you can help as my scripting is not brill

cheers munkey

you would run update queries.
update the master from your copy,
then update or append the subtables from your copy.

You should not be importing the data from your test database. You need to create DDL or use DAO to modify the schema. So if you add a new column, you would need an Alter statement to add the new column.

Depending on the extensiveness of my schema changes, I might just stay late one night, make a backup of the database, and then change the schema directly. This works as long as it won't break existing functionality and things like adding columns and tables are generally benign as long as you use good practice when developing queries. Then I take a copy of the production database with the schema already changed. When I apply the changes to the FE and finish testing, all I have to do is distribute a new FE.

I have one app that is sold to the public. Therefore I cannot directly modify their schema. The BE can be either ODBC or ACE depending on the client's preference. So, I found a SQL Server tool (SQL Examiner 2018 is the one i use) that compares two databases and generates the DDL to convert db1 to db2. If the client uses SQL Server, I send this generated script to him. For the ACE users, I take the script and break it up into individual DDL queries. I then create an Access app that checks versions (you need to be certain you know what version of the BE the user has to safely run the code) and then run the queries.

I haven't found a similar tool for Jet/ACE so when I have more extensive changes that I cannot make directly, I convert both the old and the new versions of the BE to SQL Server and then run the Examiner tool and convert the script to individual DDL queries. It's not too big a deal although it is harder now that Access no longer includes an upsizing wizard since I have to use SSMA which I hate.