Programmatically importing tables

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

Guest

Hi

I have made a database to store my students' assessment. My teaching partner
also uses this. However, we would both like to take a copy of the database
home and then sync it the next day.

I have been reading much info on how to do this. There seems so many
different methods that I thought I'd ask for some help to pick the right one.

The database has a number of tables (eg) tblStudents, tblResults etc. There
are a number of relationships.

So, what is the easiest way to import tables from the copy of the database
with minimum fuse to the user?
 
Hi Leonard,

You're actually going to want to avoid importing/exporting tables for this
purpose. To synch database data, you should really use a collection of
queries. One should check for records that exist in your database that don't
in the "archive". Another should update records that you edited. Perhaps
another should delete records that you removed from your copy.

One thing you'll want to be cognizant of is if you and your colleague edit
the same record--which update and which synch should take priority? What if
you deleted a record and synched first, but your colleague edited the record
and synched second? Should the record be re-added, or should he/she receive
an error?

Just a few thoughts to get you started...
 
Hi Jim

Thanks for the reply. You make some very interesting comments that have
really got me thinking about how to do this.

So far I have written the following:

'Import Data

Dim strTable As String

strTable = "tblLookupSkills"

Dim strFrom As String
strFrom = "c:\teacherdesk.mdb"

DoCmd.TransferDatabase acImport, "Microsoft Access", strFrom, acTable,
strTable, "tblNew"

'Append Data

Dim db As Database

Set db = CurrentDb

Dim strSQL As String

strSQL = "INSERT INTO tblLookupSkills ( skill ) SELECT tblnew.skill FROM
tblNew"

db.Execute strSQL
=============================================
This works fine for importing data with no relationships, however, how do
you update a table with a number of relationships?

Thanks greatly

Leonard
 
Back
Top