Import txt file, update or append related tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I sent a request yesterday in another newsgroup, but didn't quite get the
answer I was looking for or maybe I didn't explain myself very well (I
constantly do that). I have several laptops/tabletPCs that contains a copy
of my DB that is also maintained on a main PC. These laptops are not on a
network so to update the main PC or to update the laptops the user must
export about 8-10 txt files. Then the laptops or main PC must then import
the txt files. I created temp tables with information deleted before any
import is performed. Now the problem I see and need help on is since my
regular tables have a relationship how do I update the regular tables if the
information has changed and append the ones that are new? For instance say I
have 3 tables and 3 temp tables.

tblShopCode
ShopID autonumber
ShopName
etc

tblStudents
StuID autonumber
PayNumber(this can't be the PK for other reasons)
FName
LName
etc.

tblTest
TestID autonumber
StuID
etc.

The temp tables contain the same info except the tables name would be like
tblTestTemp.

Now since each laptop can export different or same ID autonumbers how do I
update the correct student with the correct ShopID and append the ones that
are new? Then update the test if changes were made or add new tests if
needed. I hope someone here can help me on this. Thanks in advance.
 
I was thinking maybe do something like the following for the tables that
don't have a foreign key.

CurrentDb.Execute "DELETE * FROM tblShopCodeTemp"
DoCmd.TransferText acImportDelim, "", "tblShopCodeTemp", fDrive1 & "\" &
"ShopCode.txt", True, ""
DoCmd.OpenQuery "qryUpdatetblShopCode"
CurrentDb.Execute "DELETE * FROM tblShopCodeTemp WHERE ID=" & tblShopCode.ID
DoCmd.OpenQuery "qryAppendtblShopCode"

But now how I do do the same for tblStudents since the ShopID could be
different for a student? In other words I created a student and test on the
laptop for that student. Now that I am trying to import the student and test
to the main PC, I will have to append this record to tblStudent and tblTest.
Since I created the student a autonumber was generated and related to tblTest
for the student. Now I import the information. However, when I append the
student to tblStudents his Stuid(autonumber) could change, because I don't
append the ID, but everything else since other laptops could generate the
same Stuid. How do I get the right Stuid (autonumber) in the tblTest to
match the right student? Is there a better way of doing this?
 
You have to set things up so that every Student - and Shop and every
other entitity - is guaranteed to have a unique, stable primary key
(unique across and stable across all copies of this database) regardless
of which computer it was created on.

At present it sounds as if you are thinking of somehow letting
Autonumber fields to generate new primary key values each time you
re-populate a table. This means that the primary key values won't be
stable - and therefore that you lose the only link between related
records in different tables.

I still think that multi-field primary keys are the way to go, with one
field to identify the computer where the record was created and the
other(s) constituting a key value that will be unique among the records
created on that computer.
 
Hey thanks for your response agin John. I am taking your advice but I think
I need to do it a little differently, please tell me if I am on the wrong
track. Instead of using the computer name I am think of just combining a few
fields and also using the autonumber as the primary key. The reason I think
I have to do this, which I didn't mention before is that each main PC then
has to forward their info to another main pc (master). So I could have a
15-20 places who are not connected who then download to 5-10 laptops. Then
when the tests are completed they inturn import to their main pc, which in
turns imports to the master. This is such a nightmare. Or do you think
combining the field that I mentioned and use the computer name as the primary
key and do away with the autonumber altogether? Thanks for any input. I
can't even imagine how the reports are going to sift through all this.
 
Back
Top