Importing data from mde

[BobNTN]

I have my sister using the db (in mde form).
When I make design changes to the mdb, I need to import (freshen) the data from her mde. (We're in different states) Obviously, when I do this, I get a duplicate table renamed.
If I do a query to freshen the data, will an update query just copy the changed/new data without the unchanged ?
Or is it best to just delete everything in the table and append the data from the mde table ?
Or is there a better way to do this that I'm not thinking about ?

 

[pbaldy]

Actually the best thing would be to split the app:

http://allenbrowne.com/ser-01.html

http://granite.ab.ca/access/splitapp/index.htm

If you and she put the back end in the same place, you don't have to worry about relinking the tables.

 

[WayneRyan]

Bob,

Clearly Paul is in a different state today. We shouldn't have him working
on a holiday ... Just kidding Paul.

I don't think that you and your sister are candidates for Replication here.

Is your sister the only one who changes the data?
You don't change data, but maintain the database?
If you're both modifying data, who has precedence?

Need more information anyway.

btw,
Just kidding Paul, didn't think you noticed the "We're in different states" thing.

Wayne

 

[pbaldy]

No offense taken Wayne, but I didn't suggest replication, I suggested splitting the app. I think it's perfect, as he can just email her a new front end; no moving of data necessary.

 

[boblarson]

And if Paul's suggestion is followed, he can also use my backend auto updater to add/delete fields, add/delete tables if there are changes that don't require merging of data. If changes occur to existing tables, I haven't got that one worked out in the tool yet.

The tool is here (be sure to download the correct tool as there is a frontend update tool as well as the backend auto update tool there. Also download the instructions and read them BEFORE opening the tool:

http://www.btabdevelopment.com/main/AccessTools/tabid/78/Default.aspx

 

[BobNTN]

aw come on guys, you're getting way above this ole redneck's head!

One db, she is the only one who uses it.
I (along with you guys) built it and will be making some minor changes to the design for a little while.
It is only 3mb so I just zip it and email it to her.
She zips the mde and emails to me for any design changes.
Just wondering what would be the best method to refresh the data in my design copy so I can just make a new mde and send it back to her with current data.

I don't foresee this going on for long. Seems to be working. She wants some way of typing in a CID or last name and finding that record rather than scrolling thru 3000 records to input someone's check. Stuff like that.

 

[BobNTN]

Ok, I believe the split db is the way to go.
So all I have to do is send her the mde after I make any changes and it will auto link with the 'be' ?
Then if i want to freshen the 'data' on my end, she can send me the be ?

If I got that correct, fairly simple.

 

[boblarson]

it will auto link with the 'be' ?

If you want that to work just make sure you and her both use the same path to the backend. I like to use something like:

C:\MyDb

Then it will work.

 

[BobNTN]

I was curious about that.
I usually work on the design in one directory but when I send it to her, I make sure the correct path on her pc is set in the options.
Is there a problem with doing that ?

 

[boblarson]

The options are not something you can set for a linked table. You will need to either use code to relink to the location when she opens the database, or manually set them which you can't do in an MDE file, or you can make it real easy and just have both of you have the same path. I'd go for the last option there as it is the most simple and easiest because you don't need to do anything.

 

[BobNTN]

No problem

Now if I can figure how to change a main form with a subform to a combo box with the same subform, everything looks great.

 

[dfenton]

I would suggest that the easiest way to set up automatic relinking is to have the front end look for the back end in the same directory that the back end is in. Then you don't have to hardwire the path to the back end.

 

[darksniper]

The idea of splitting db and form is interesting, I would definitely implement it in the future.

But at the moment, I need a way so that I could import the data back-ups from a coworker in another department. I have a similar issue as the person that have started the post, but I don't have the permission to split the db at the moment, nor write queries to do data import and update on duplicate(as stated in other post, "I have over 200 tables, with about 15-50 fields per table"), there are too many tables and fields. Ms access has a feature for db backup, and db import. Is there any way to make db import to append data and update on duplicate using the tool that ms access provides, because on the first import it told me I need to specify option on what to do on duplicate fields, and then after that all it did was just duplicate the tables.

 

[dfenton]

The idea of splitting db and form is interesting

No, it is a *REQUIREMENT* for any properly-designed Access application.

Every Access application with more than one user should be split. NO
EXCEPTIONS.

Every Access application where the user needs to get updates to the
forms/reports from someone else should be split, even if it has only
one user. NO EXCEPTIONS.

Every replicated Access app should be split. NO EXCEPTIONS.

 

[boblarson]

Well stated David!

 

[darksniper]

No, it is a *REQUIREMENT* for any properly-designed Access application.

Every Access application with more than one user should be split. NO
EXCEPTIONS.

Every Access application where the user needs to get updates to the
forms/reports from someone else should be split, even if it has only
one user. NO EXCEPTIONS.

Every replicated Access app should be split. NO EXCEPTIONS.

I would love to do it, but at the moment I have to merge all of the db that people have been working on with an updated version of the form I have been developing, after the merge, I will work on splitting them form and db, thats why for now I need a way to import db and update on duplicate. So I am looking for the best and easiest way, since I have mentioned earlier I have over 200 tables and I just dont have the chance to write manual queries for all of them.