W
Wayne-I-M
This gets asked quite a bit so I thought I would give one method. (sorry if
the english is not too good)
To import data from a table in an external database into (append) to a table
in your internal database.
This is not the only way to do this – there are other methods – the “cheatsâ€
I have given here you can either or not – I use them as I’m lazy and – in
this case – the end justifies the means (especially when the means are delete
after the end is accomplished?)
Names – In this I have used….
External Database - This is where the records are now
The records are in a table called tblOldExport
Internal Database - This is where I want them to be
tblInternalNewRecords - This the table in Internal Database where I want
the records go
-----------------------------------------
1 This is the 1st cheat – go to External Database and copy the table
containing the data you want to bring in.
2 Go to Internal Database and paste the table (structure only). Call it
tblImportAppend
3 Past the same table again (structure only) Call it tblInternalNewRecords
So you now have 2 empty copies of the table tblOldExport (but in your
Intenal Database) with new names.
4 Create a new query in your internal database (call it qryImportAppend).
Base this new query on tblImportAppend. Individually click each of the fields
so they all shown in the design grid.
5 Change the query to an Append Query. In the drop down list select
tblInternalNewRecords. If you have copied the tables correctly the fields in
the “Append To†row will fill in automatically. (You can change these update
to fields)
6 Create a new form in Internal Database.
7 Create a new button (call it cboImport)
8 Put this code on the OnClick event
Private Sub cboImport _Click()
Dim WayneIMPfile As TableDef
For Each WayneIMPfile In CurrentDb.TableDefs
If WayneIMPfile.Name = "tblImportAppend " Then
CurrentDb.TableDefs.Delete WayneIMPfile.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
Notes on this code
1 Change the names to whatever you want. - I wouldn’t have WayneIMPFile –
unless ….
2 The 1st section just looks for and deletes tblImportAppend if it’s there
3 The next bit DoCmd.TransferDatabase is a standard way to bring in tables
to a DB (press F1 for more information)
4 The path to the MDB can be found by (another cheat).
Start – Run – Browse - Navigate to DB - Select open - Cut the path from the
run box and paste into code - Close run
5 Turn of warning
6 DoCmd.OpenQuery Runs the append query
7 Turn the warnings back on
8 Delete the table the code created (tblImportAppend)
9 End Sub – go and have a coffee
Note – if you look at the design of the Append Query it will look wrong - as
there is no table to base it on. Don’t worry this is normal. The code will
create the table, run the query then delete the table again (I tend to do
this as it’s just extra disk space).
Of course you can append the data to other tables and you don’t need to
append all fields. If you play around with the code and the Append you will
get it to do want you need.
I use this method quite often and have the validation process in the append
query – is text formatted correctly, are numbers right, are postcodes
formated so the post office will acept them, etc, etc. But the most
important validation I use is not importing records that are already in the
table. You can use a time/date stamp (field) in the external table and in
the append simple put something like
Good luck
the english is not too good)
To import data from a table in an external database into (append) to a table
in your internal database.
This is not the only way to do this – there are other methods – the “cheatsâ€
I have given here you can either or not – I use them as I’m lazy and – in
this case – the end justifies the means (especially when the means are delete
after the end is accomplished?)
Names – In this I have used….
External Database - This is where the records are now
The records are in a table called tblOldExport
Internal Database - This is where I want them to be
tblInternalNewRecords - This the table in Internal Database where I want
the records go
-----------------------------------------
1 This is the 1st cheat – go to External Database and copy the table
containing the data you want to bring in.
2 Go to Internal Database and paste the table (structure only). Call it
tblImportAppend
3 Past the same table again (structure only) Call it tblInternalNewRecords
So you now have 2 empty copies of the table tblOldExport (but in your
Intenal Database) with new names.
4 Create a new query in your internal database (call it qryImportAppend).
Base this new query on tblImportAppend. Individually click each of the fields
so they all shown in the design grid.
5 Change the query to an Append Query. In the drop down list select
tblInternalNewRecords. If you have copied the tables correctly the fields in
the “Append To†row will fill in automatically. (You can change these update
to fields)
6 Create a new form in Internal Database.
7 Create a new button (call it cboImport)
8 Put this code on the OnClick event
Private Sub cboImport _Click()
Dim WayneIMPfile As TableDef
For Each WayneIMPfile In CurrentDb.TableDefs
If WayneIMPfile.Name = "tblImportAppend " Then
CurrentDb.TableDefs.Delete WayneIMPfile.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
Notes on this code
1 Change the names to whatever you want. - I wouldn’t have WayneIMPFile –
unless ….
2 The 1st section just looks for and deletes tblImportAppend if it’s there
3 The next bit DoCmd.TransferDatabase is a standard way to bring in tables
to a DB (press F1 for more information)
4 The path to the MDB can be found by (another cheat).
Start – Run – Browse - Navigate to DB - Select open - Cut the path from the
run box and paste into code - Close run
5 Turn of warning
6 DoCmd.OpenQuery Runs the append query
7 Turn the warnings back on
8 Delete the table the code created (tblImportAppend)
9 End Sub – go and have a coffee
Note – if you look at the design of the Append Query it will look wrong - as
there is no table to base it on. Don’t worry this is normal. The code will
create the table, run the query then delete the table again (I tend to do
this as it’s just extra disk space).
Of course you can append the data to other tables and you don’t need to
append all fields. If you play around with the code and the Append you will
get it to do want you need.
I use this method quite often and have the validation process in the append
query – is text formatted correctly, are numbers right, are postcodes
formated so the post office will acept them, etc, etc. But the most
important validation I use is not importing records that are already in the
table. You can use a time/date stamp (field) in the external table and in
the append simple put something like
Good luck