Export and import data back into DB using XML

[Chandrajit]

Hi everyone,
I need to know if its possible to
1. export data residing in all tables as xml
2. make changes to the DB and release it to the user
3. import the data back into the DB

objective is
1. if the user requests changes, I want to make the changes to objects (forms, reports and queries and modules) remotely
2. release the modified DB back to user
3. let the user import all his data to the new DB

also there are tables with one to many relationships, with referential integrity. But to autonumber fields

 

[Frothingslosh]

You need to use a split database. That way, the data is in one file, and your forms, queries, reports, and modules are in another. That lets you make changes to the objects without having to mess with the data.

Google 'How to split an Access xxxx database', replacing xxxx with the version year of your copy. You'll find a number of links coming up showing you how to do it. (The locations of what you need are slightly different, depending on your version.)

Also do some google research on how to use linked tables. (For the most part, they work just like tables in the front end.)

To answer the question you asked, yes, it is possible, but it is a GIANT pain in the ass and can take up an immense amount of time.

 

[isladogs]

As Frothy said, the way to do this is using a split database.
That will allow you to update the forms, reports etc in the front end without affecting the users' data in the BE.

As you haven't done that, you could do one of the following as a work round

1. Before updating save a copy of the existing app to another specified location.
Release the update with no tables.
After updating import all the tables into the new version.

2. As above but link the tables from the old version so you now have a split database. Then remove all objects from the old version (which is now the backend) except for the tables.

Both could be problematic so make backups first.
Doing method 1 means you will still have unsplit databases
Method 2 will be better in the long run as it's one off operation.

 

[Frothingslosh]

Oh, number 2 is a much better solution than going through the split database wizard!

Sorry, been working on fixing a hideously complicated 12 year-old form today. This was supposed to be a quick breather, but apparently I'm more frazzled than I thought!

Oops!

Anyway, whichever you do, don't forget to do a compact and repair once you're done.

 

[Cronk]

While not mentioned by the OP, sometimes I've had to modify data structures in the BE remotely eg add a new table, add a new field to accommodate new functionality.


This can be done with data definition SQL and/or vba in a start up routine which tests if mods have been done yet and resets a flag when done.

 

[isladogs]

Oh, number 2 is a much better solution than going through the split database wizard!

@Frothy
This was suggested so the OP could arrange for the conversion of an unsplit database to a split one at remote sites without the client(s) having to manage the process themselves.

However, of course I agree that using the wizard to split it before updating would be easier if the OP is able to attend each site and supervise the process.

But in post #1, the OP stated this needed to be done remotely

@cronk
I've done the same as you to update Access BE tables as part of the relink code when the FE is updated. It works well
For SQL BEs the process is even easier. I use a SQL script for which SSMS provides almost all you need.

 

[Chandrajit]

Dear Frothingslosh, ridders, Cronk

Thank you very much for the insights. I am wondering why the split database idea did not occur to me.

however I would like to ask
1. if the DB is split into Front end and back end would the updated DB or the front end retain the referential integrity?
2. IF my DB had autonumbers how would the updated Front End (released to users post updates) handle such fields?

I actually converted some autonumber fields to number fields fearing updates wont be able to handle them!

 

[Frothingslosh]

The back end has the data, while the front end has everything else. Data integrity remains unchanged.

Because the front end only has links to the tables in the back end and not the tables themselves, nothing in your data would change.

 

[Chandrajit]

Lovely!

Thanks so much all !!!!