Only replicate the table structure, not the records (1 Viewer)

[ruudschiphorst]

Hello people,

Since I'm new at these forums, I suppose I'll introduce myself first. My name is Ruud, I'm Dutch and I've been developping in MS Access for a reasonable period now. I work in a small business with 3 co-workers.

I hope this is the right forum, if not; my sincere apologies.

As for my question, we've recently finished a database for a client of ours, who in turn sold it to 2 of his customers as a pilot for a bigger audience. The expectation is, that in about a year, our project will be used in about 20 companies.

As the product grows, we will want to change our basic table structure and relations between them, including the changing, adding and deleting of field names and relations for the sake of a more flexible database and other insights we missed or didn't have before.

At this point, we conluded that if we use the replica option of Access and provide an empty Master, the entire database at our client's customers will be flushed, as our master will be 'last edited'.

Our other option would be to write a quite extended procedure to make a copy of the current records (client side) to an empty database, overwrite their current backend with our new and improved one, then go right back to importing the old records from the new, temporary database. The problem with that, other than it will take a lot of time, would be we'd have to adjust our our script every time in case we rename FIELD_1 to FIELD_2 to exactly match to eachother. Also, this is very delicate and subject to import faults from our side due to spelling errors and such.

Short version: we only want to copy the table structure and relations, without editing or, heaven forbid, deleting the records at our clients.

Is there a method we're missing or overlooking?

Any insights are also very welcome.

Thank you in advance

 

[boblarson]

Replication is NOT a good thing for your purpose. I'm sure David Fention (our resident Replication expert) will find this post and chime in but until he does I will answer.

While it looks promising to you, I assure you that doing this with replication will turn out to bite you big time. It is for replicating DATA and, while it does do structural changes to tables and queries as well, it is not something to trifle with as it can cause you major headaches.

I do believe your second option is the best option in this case.

 

[boblarson]

Also, you may want to check out David's Wiki here:
http://dfenton.com/DFA/Replication/index.php?title=Main_Page

 

[DCrake]

What you actually need is a database comparison tool. I have a VB version that allows the user to select 2 mdb's and compare them against each other. It looks mainly at tables, fields in tables and queries. It tells the user that tables do not exist and or fields do not exist. it then allows you to automatically add the missing tables/fields to the tartget mdb. I thought I had an Access version but I don't sem to be able to find it at the moment. The VB one is not quite finished yet, it is constantly under development. It also permits the opening of password protected mdb's (as long as you know the password). I will have another look and se if I can find it.




David

 

[dfenton]

Replication has no role in your scenario.

And I'd suggest that you not be selling an app when you don't have the schema complete yet -- talk about an amateur mistake.

 

[ruudschiphorst]

First off, thanks for your replies.

We are going to stick with the second option and not use replicas. It just felt we were inventing the wheel for the second time around and wanted to explore our options. Any links to an example of the desired code will be very welcome.

As to not having our table scheme ready, I agree wholehearted that that is at the very least... undesirable... But this is an application that relies on test data from emloyees, like IQ scores, development in their company and such. As these test may change, so may our tables have to. We developped this using what data we had from the two customers of our client and added a few, as of yet unused, tables of what we figured would become necessary for at least the most generic (as far as those excist) companies, but the risk of missing tables for companies with very specific data to handle is very hard to avoid, hence my question.
We're actually working on a dynamic tablestructure wich allows the storage of all types of data while keeping it's integrity, but that is a very tricky and time consuming proces.

Again, my thanks

 

[dfenton]

As to not having our table scheme ready, I agree wholehearted that that is at the very least... undesirable... But this is an application that relies on test data from emloyees, like IQ scores, development in their company and such. As these test may change, so may our tables have to.

That sounds like you're making each test result a record in a table, with each field in the record being a test answer. If that's the case, then you have a schema error.

Tests and surveys should never be implemented in that format. The fact that you foresee schema changes in response to test changes is a red flag that your schema is denormalized.

Instead, there should be a test table, a table of test questions, a table joining the test and the questions, and an answer table that records the test taker name and the ID from the join table.

This has several advantages:

1. a question can be used on more than one test.

2. tests can be altered simply by adding a record to the questions table and then a record in the table joining that question to a particular test.

This means there is no need for schema changes at all.

The only difficulty is with data type enforcement in the answers table. There are two alternatives:

1. have a field in the question table that defines the data type of the answer and when you load a question, choose the control type on the fly (storing the result as text, the least-restrictive data type).

2. also have the data type defined in the question record, but in the answer record, have a field for each possible data type, and store it accordingly.

I'm not sure what the advantage would be to #2, as you still have to choose the control type on the fly.

Another issue:

If your answer is multiple choice, then you need to have a sub-table that defines the choices for each question. Assuming you'd like to have the option to sort the multiple choices answers in any order, you'd want to record an ID pointing to the question choices record (since if you record just a, b, c or d, you would be hardwiring the order and giving up the ability to produce copies of the test with different orders for the multiple choices).

You might extend the multiple choice answers table to enter data to populate a dropdown list, for instance, and not just for multiple choice tests.

You might find this sample app useful in modelling your schema:

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=3

 

[Singh400]

Short version: we only want to copy the table structure and relations, without editing or, heaven forbid, deleting the records at our clients.

Is there a method we're missing or overlooking?

Any insights are also very welcome.

Thank you in advance

I'm probably wrong (and this kind of topic, is wayyyy over my head), but why don't you just create a new DB and import the old DB into it. And tell it just to copy tables, queries and relationship etc, but no data.

 

[boblarson]

I'm probably wrong (and this kind of topic, is wayyyy over my head), but why don't you just create a new DB and import the old DB into it. And tell it just to copy tables, queries and relationship etc, but no data.

Yes, it does appear to be over your head and you are wrong. If you do that then the people using the database will lose their data. AND the NEW changes in the modified version would NOT be there. It is not just a case of a "simple" import or copy. If structural changes occur you need to figure out how to pull the data that was in a previous structure into one where the fields might not even be the same.

Therein lies the quandry.

But, also David Fenton's explanation also shows that the structure of a database needs to be designed so that DESIGN changes are very, very rare. And adding questions, etc. to a database is a matter of adding RECORDS, not fields.