Importing/exporting tables

[Blackwidow]

I am creating a nightmare of an assessment database and the leadership group at work's latest request has had me a little worried and so I am seeking some advice on how best to achieve it. The assessment database as such isnt the problem but what they want is for everyone to have a copy of the database at home which they can work on.

I think I need to have import/export type button on the database which would allow them to export their class information from the main database at school to work on at home.. e.g. through transfering it to a file on the hard drive or direct to a floppy or memory stick and then when they come back into school they can simly import their data back into the database...

Going in through the database design isnt really the option I am hoping for... idealling just want a simple (or not so simple!) Import and Export Button..

Suggestion Advice any help would be greatly appreciated! Oh and incase I didnt mention the leadership team want me to come back to work on Monday and tell them yes this is possible or no not a chance (They like to give me a nervous breakdown!)

 

[doulostheou]

You might look into replicating the database. I have never actually setup a database for replication, but I think it was designed to what you want. The drawback would be that the whole database would be copied and depending on how large it is, that could be problematic.

The only problem I see with the importing/exporting button you proposed is that I am assuming that they will be able to edit entries as well as add new ones. Creating the code so that it detects the newest changes to an entry that already exists could be hairy.

 

[The_Doc_Man]

As is usually the case for managerial requests, your problem is soluble but with a cost. Usually, you have to define the costs and benefits in order for a manager to decide something.

Here is your "cost" of doing business: To have a shared, take-home copy of a DB, you have to establish the rules by which entries can be updated, because you are going to have to do a real nasty action later to RECONCILE those entries. It ain't the split that's gonna cost you. It's the re-merge.

If a manager gave me the task you described, my immediate answer would be to suggest that the issue isn't designing the DB, it is designing the process of conflict resolution. What happens when Fred and Bob update the same record in their private copies - and they do so with different answers?

Without (1) defining the rules of reconciliation and (2) defining what happens to folks who blantantly ignore the rules, this problem IS insoluble. But not because of technology. It is because of the "old programmer's rule." What, you don't KNOW that rule? "If you can't do it on paper, you won't do it in Access."

In this case, without the reconciliation rules, you DON'T KNOW whether you can do it on paper. So you can't answer the question. Suppose the bosses say, "OK, we will define the rules." Well, that answer ain't good enough 'cause you don't yet know how many hoops we are talkin' about jumpin' through.

Here's how you enlighten them: For any complex system, Access is a business modeling tool in a sense. A good DB design contains objects that correspond to elements of your business. It also contains queries and code that reflect your business rules. Until you have a full understanding of the applicable business rules, you cannot evaluate the project because you are shooting in the dark with the business model.

Now, suppose they actually had a model or a set of simple rules in mind for reconciliation. I can only imagine two ways to do this that make sense for automation...

First way: Assign an OWNER to each record. I.e. a support rep who is the only person who has the right to change that record in the "home" copy. That must be part of the primary record. Include a date in the table to show the date of last change for the record. You can even decide that your users can only take home records they "own", not the whole table. Of course, that means no "global" reports from individual home copies. This could be good OR bad.

Now you can import the changed data (or the data owned by the rep) to temp tables. From there you can do your updates. You can look for changes by finding those records in the "weekend" table that no longer match the critical values of the main table. Then just update changed data.

Second way: Import the changed data to temp tables qualified by whose DB you got it from. (In other words, the temp table has an extra field showing whose take-home copy was the record's source.) Now sort by the prime key of the original table, which will be PART of the prime key of the import table. (The other part of the prime key will be who made the change.) You can again identify changes. Only this time, you would probably have to manually choose which value to keep and which value to toss. So you'll need ANOTHER field, probably just a simple Yes/No box, to keep the "Keep This One" flag. Then only keep the keepers. And have a business rule that says you can have only one keeper for any record.

 

[RV]

Another thought on this one.
Split your databsse in a frontend and backend.
Put the frontend on eash client, put the backend on your company netwerk.
See to it that every user's got access to the netwerk folder holding the backend file.

Now see to it that each user has access to the Internet using a fast connection such as broadband.
And see to it that each user's got a VPN client installed enabling him or her to connect to your companies netwerk.

In theory, that should enable you all users having access to your applic without bothering 'bout local copies.
I guess you would have to consider setting up user priviliges and accounts in Access and would have to consider a phenonemon called "record locking".

Just a guess as I'm not too familiar with these last 2 topics.

Apart from that and whatever approach you decide upon, you should take The Doc Man's splendid advises regarding business procedures and processes and control sreiuosly and put them in place beforehand.

RV