Checked in Word – so hope the English is OK
***************
Rather than me trying to write your “stuff†for you – which will go wrong as
I can’t really understand what is it your trying to do. I’ll give you one
method – there are others.
To get the TutorID in 3 tables on import
(I will leave the “what – why – when†to you)
Once you have the Tutor ID accross you can run as many proceeses as you like
- don't forget I have only given the TutorID as an example - you can bring in
to the whole table or just some fields
There is another method - which you will know - just do Get Extral Data -
import the table using the wizard
***************
Try this - as a method to help you understand how it works
Create a new blank database (this represents your normal DB – but best to
use a copy – just in case)
Create 3 tables
tblA
ID = Autonumber
TutorID
tblC
ID = Autonumber
TutorID
tblC
ID = Autonumber
TutorID
Paste a copy of you Tutor_Import table (structure ONLY)
Call this new table tblImportAppend
Paste a copy of you Tutor_Import table (structure ONLY)
Call this new table tblInternalNewRecords
You now have 2 empty copies of the table (structure only)
Create a new query (based on tblImportAppend)
DoubleClick each field (don’t use the *) “except the TutorID) so that you
have one column for each field
Change the query type to Append
From the dropdown select tblInternalNewRecords
The Update to rows will fill in (if you have followed the above)
OR forget all the above and use the 3 new tables you have just created -
something like this in the update to row
Create a calculated column - A_Append: [tblImportAppend]![TutorID]
Create a calculated column - B_Append: [tblImportAppend]![TutorID]
Create a calculated column - C_Append: [tblImportAppend]![TutorID]
That’s 3 columns
A_Append goes to [tblA]![ TutorID]
B_Append goes to [tblB]![ TutorID]
C_Append goes to [tblC]![ TutorID]
But I would not do that until you are happy with the whole process
Save the query as qryImportAppend
Create one form (save as frmImport)
Create a button (call it butImport) on the form and put this OnClick
Private Sub butImport _Click()
Dim KirkIMPfile As TableDef
For Each KirkIMPfile In CurrentDb.TableDefs
If KirkIMPfile.Name = "tblImportAppend " Then
CurrentDb.TableDefs.Delete KirkIMPfile.Name
End If
Next
DoCmd.TransferDatabase acImport, "Microsoft Access", "Path to
ExternalDatabase.mdb", acTable, " tblOldExport ", " tblImportAppend"
DoCmd.SetWarnings False
DoCmd.OpenQuery " qryImportAppend "
DoCmd.SetWarnings True
CurrentDb.TableDefs.Delete "tblImportAppend"
End Sub
________________________
Next
Create another blank database
Copy you’re the Tutor_Import table from wherever it is now into this new DB
Paste it structure AND contents (so you have a complete copy of the table)
Save the table as tblOldExport
Save the database MyNewDB.mdb
Close database
________________________
Next
Click Start
Click Run
Click Browse
Select FileType = All Files
Browse to your 2nd database that you just created (MyNewDB.mdb)
Click OK
Cut the path from the run box
It should look something like this (if you saved the DB into My Docs
"C:\Documents and Settings\My Documents\MyNewDB.mdb"
If it is somewhere else it will look different
Open the 1st database you created
Past the path into the OnClick code (on form frmImport) like this
DoCmd.TransferDatabase acImport, "Microsoft Access", "Path to
ExternalDatabase.mdb", acTable, "tblOldExport", "tblImportAppend"
Will now look like this
DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\Documents and
Settings\My Documents\MyNewDB.mdb" , acTable, " tblOldExport ", "
tblImportAppend "
NOTE – all this is on “one†line
Save the form and click the button
Your records will now go from the old table into your new one(s)
You can simply cut out most of this and just use the import and append to
the 3 new tables – BUT I would use the tblImportAppend to store the data for
while so that you have a copy of the imports (useful for creating reports)
I have not really given much details on the actual append.
If you need more information about append querrys – report another question
and someone will give advice.
Good luck
--
Wayne
Manchester, England.
IKMD66 said:
Wayne,
The table name that we import the information to is Tutor_import. All the
fields are listed below. I have separated the fields by indicating at the top
of each group where the fields will be migrated to.
I am completely open to suggestions here….
In the table [Tutor - Basic Data] I use the Primary Key is [Tutor ID], which
is Autonumbered. In the other two tables there is a field, [Ref Tutor ID],
that relates directly to [Tutor ID] in the Basic Data table (join type 2 and
Referential Integrity Enforced).
I can build an Append query to move the data to the respective tables,
however I do not understand currently how I can ensure the field [Ref Tutor
ID] gets populated with the correct value during the simultaneous append to
all three tables.
Table: [Tutor - Basic Data]
Full_Name
Address_1
Address_2
Address_3
Address_4
Post_Code
Home_Telephone
Mobile_Number
Email_Address_1
Email_Address_2
Travel_1
Travel_2
Travel_3
Question_1
Additional_Information
Signed
Signature_Date
Group_1
Group_2
Group_3
Table: [Tutor - Additional Data]
Subject_Choice_1
Subject_level_1
Subject_level_2
Subject_level_3
Subject_level_4
Subject_level_5
Subject_Choice_2
Subject_level_6
Subject_level_7
Subject_level_8
Subject_level_9
Subject_level_10
Subject_Choice_3
Subject_level_11
Subject_level_12
Subject_level_13
Subject_level_14
Subject_level_15
Table: [Tutor - Availability for Week]
Hours_per_week
Hours_1
Hours_2
Hours_3
Hours_4
If you need anything further please let m know.
Thanks again.
Regards,
Kirk
:
The import will import anything you tell it to. So if the Tutor ID is in the
tble you're importing it will be available to append into your 3 tables.
The "very" basics are
Import your external table data into your intenral database into a temp table
Use this temp table to provide the data for an append query
Use the append query to add data to any tables in your databse that have
field o the correct format
Lastly delete the temp table (it is created each time you run the import)
The difficult "bit" is your decision making. Where do you want the data to
go in your database - do you want the tutor ID to by past to all 3 tables, do
you need student details to append, what if the data is already in the tables
(someone else may have mistakenly imported it already), do you want to append
to alter anything (change numbers to currency - add fields of number -
reformat text to lwer case - etc -etc).
None of these are difficult to code but it is the processes you want to run
and in what order that is the problem. Only you know what you want it to do
- and there is not enough information in your post for me to write the import
code or the append sql for you.
Let me (and anyone else in the forum) know if you're having problems as many
people can write the few lines of code needed. If you do, you will need to
provide an example of the data and (most important) the table names in your
external and internal DB's
Good luck
--
Wayne
Manchester, England.
:
Wayne,
I will attempt this tomorrow - just to be clear though using the append
query will automatically determine the Reference Tutor ID in the Subject and
Availability tables? Or do I have to do to something in the append Query to
determine this as the new record is added to the Basic Data table?
Thanks in advance for the clarification.
Kirk
:
Hi
The best method would be not to import the table but to import the data into
an append query via a tempt table. Th append can split the data into your
three tables (and do a lot more). This way you will not disturb te
relationships you have created
