Transferring table Design Changes

APD Toronto
APD Toronto used Ask the Experts™
on
Hello Experts,

I am wondering is there a way to easily transfer design changes from one mdb to another, where my mdb copy is a working copy and I want to transfer the table design changes on to the live database.

I know that SQL server you can save the table design changes into a SQL text and run it on the live database, but is there anything like this with Access?

Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Architect / Application Developer
Top Expert 2007
Commented:
Well, you used to be able to do that with Replication, but that is no longer available as A2007.

So, the only thing you can do is write your own vba code to do this, including a one time update routine to be executed on the remote db.  It can be done and I have done it, but I would not say it is trivial.

OR ... use something like www.teamviewer.com to log in and make the changes manually.

mx
APD TorontoSoftware Developer

Author

Commented:
I do have remote control software, but there are a tons of modifications, so i'll be all night doing them when all go home.

How would  vba code work in modifying fields and adding new.

By the way, i'm using A2007, but the actual file is 2003, and I have access ito 2003, if that helps?
Top Expert 2011
Commented:
I use VBA code to apply the changes.

Check out: Splitting your Access database into application and data (Click Here)
** Scroll down towards the bottom to the section: Tools to help deploy back end updates

Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Most Valuable Expert 2014
Commented:
I'm with mx on this.
It can be done, but how many fields, in how many tables, with how many properties set becomes the question.

I'd say that, by the time you coded and tested it, you'd need north of 100 fields and / or properties to make the effort to code it time effective.
The TableDefs collection, the Fields collection, and the intricacies of the Field.Properties collection are the things you'd need to use and become familiar with.

You'd probably be better off just coding up a loop to append all the records from each table in the live database to the working copy, and then replacing the live database.  You are working in Access, so I assume a small period of time to do the file copy exists.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007
Commented:
Well, I apologize ... but it's a significant amount of code, and would vary for each specific case ... and I really don't have the time to get into all of that, but ... someone else may.  IE ... it's not one simple, easy answer.

Sorry ...

mx
APD TorontoSoftware Developer

Author

Commented:
No, no, no, its not that huge, maybe 20 fields.  I just thought there was something automated like SQL Server
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007
Commented:
There is nothing automated per se.  Replication (also non trivial) was the closest thing to an automated process.

mx
Top Expert 2011
Commented:
I do it all the time to remote locations. 100"s of site at a time without any issues. I have been doing it for over 10 years now.

I have been developing my back end updates for years. It has grown to a lot of code.  I am able to point it at the Master Back End and it read the entire design. I deploy it and it will make the other back ends identical.

If you want to create you own then see this: DAO Programming Code Examples Click Here

Also:
Manage Remote Backend MS Access Database Programmatically With VB Code

I have seen commercial products that you can purchase to do it. I have not used them.
APD TorontoSoftware Developer

Author

Commented:
HiTech,

do you need to track your changes, or will it compare automatically?
Most Valuable Expert 2014
Commented:
Agreed.
It is doable.
The scale of the author's need was in question.
Coding this up for 20 fields is not going to be time effective

_____________________________________________

Attached is a function that will spit out the names of all local tables in a db.
From there, it's not hard to construct a loop that will append all records from one db table to another
Option Compare Database
Option Explicit

Function ReportSubDataSh()
On Error Resume Next
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim prp As DAO.Property
    Const conPropName = "SubdatasheetName"
    Const conPropValue = "[None]"
    
    Set db = DBEngine(0)(0)
    For Each tdf In db.TableDefs
        If (tdf.Attributes And dbSystemObject) = 0 Then
            If tdf.Connect = vbNullString And Asc(tdf.Name) <> 126 Then 'Not attached, or temp.
                    If tdf.Properties(conPropName) <> conPropValue Then
                        MsgBox tdf.Name
                    End If
            End If
        End If
    Next
    
    Set prp = Nothing
    Set tdf = Nothing
    Set db = Nothing
End Function

Open in new window

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007
Commented:
Right ... as I noted, it's not really a trivial subject ... but it can certainly be done with code.

mx
Top Expert 2011
Commented:
APD_Toronto

<<do you need to track your changes, or will it compare automatically?>>

Yes you should always document all your changes no matter what tool you use.  I do believe it is Best Practice  of a Professions Developer to document everything. All changes. I also use version control for all other objects.

As previously posted:

I am able to point it at the Master Back End and it read the entire design. I deploy it and it will make the other back ends identical.

As previously stated I do not believe this is not a substitute for documenting all your changes.  After I have my back end updater process the master back end I run a report that tells me all the changes it will make. I then compare this to my documentation to be sure that everything matches before deploying.
Top Expert 2011
Commented:
Nick67,

<<You'd probably be better off just coding up a loop to append all the records from each table in the live database to the working copy, and then replacing the live database. >>

It is table design changes that need copied, not data.

The original question stated:

I want to transfer the table design changes
Most Valuable Expert 2014
Commented:
@THTC
<It is table design changes that need copied, not data. >
True enough.
You tell me what the functional difference is between:
a) appending the live data into a completed but empty backend file and  a copy-and-paste operation, and
b) pushing table changes into a live file that has the data?

a) can be accomplish in 60 or so lines of VBA code and tested before it goes live for any errors by someone with the a moderate knowledge of VBA
b) involves learning a skill set that may or may not see use again, or third-party tools, and will require the backend to be taken offline to obtain a target for testing.

It's the author's call.
When I look at any question, though, I try to divine the author's intent.
Quite often, there are requests to do things for which far simpler alternatives exist, or requests that stem from deeper flaws in the author's approach
I offer the options.

The author's functional need is to have a live set of data working with a new set of table schema with the least amount of hassle possible.
My suggestions have spoken to that and not literally <I want to transfer the table design changes>

Now, the author may have circumstances that make my approach untenable --> the db may be on a web server some place and very large.
Or he may never have considered my approach.
But, in either case, I thought my advice was worth the time to type out.
Top Expert 2011
Commented:
Nick67,

I do agree that in appending the live data into a completed but empty backend file is definitely a great solution. One that should be given serious consideration  I have also use that method many times.

You do make a great point that it takes a lot less knowledge to write the code to copy the data between databases than it would be to learn to write the VBA code to create the database tables, fields, index, relationships, etc.  I have been writing code to create and modify databases with DAO for 10+ years. I have had lots of practice.

IMHO, I was worth the time to type out you solution.
APD TorontoSoftware Developer

Author

Commented:
Thank you, all.

I do document everything, but i think this question got too large than intended.  For larger dbs, i definitely use SQL

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial