Excel data appending to 2 existing tables that need to be linked

[Guest]

Hello,

I have 8,000 records from a spreadsheet, where half the information per
record needs to go into 1 table while the other 50% needs to be stored in
another table. There is an auto reference number from the first table that
needs to be associated with the data that is saved in the second table so
that a link is created. The data in the Excel spreadsheet is one to one
entries.

I have looked at the append query but only seems to do an append to one
table without being able to generate a reference in the second table.

Any help would be greatly appreciated.

Thanks

 

[Guest]

Hi

You could write some sql that would do it for you but the simplest method
would be to create 2 append querys.
In the 1st put >0 in the criteria row for the auto reference number row
In the 2nd put Is Null in the criteria row for the auto reference number row

always go for the simplest method - less to go wrong.

You may want to create a temp table to bring the date into the access DB and
then run the 2 appends on that. Something like

Private Sub ImportNew_Click()
Dim IMPfile As TableDef
For Each IMPfile In CurrentDb.TableDefs
If IMPfile.Name = "NameOfTemptTable" Then
CurrentDb.TableDefs.Delete IMPfile.Name
End If
Next
DoCmd.TransferDatabase acImport, "Microsoft Excel", "PathToSpeadSheet",
acTable, "Results", "NameOfTempTable"
DoCmd.SetWarnings False
DoCmd.OpenQuery "The1stAppendQuery"
DoCmd.OpenQuery "The2ndAppendQuery"
DoCmd.SetWarnings True
CurrentDb.TableDefs.Delete "NameOfTempTable"
'Use this bit if you want - just in case there are no records to import'
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records imported", vbInformation, " "
Cancel = True
End If
End Sub


Hope this helps

 

[Peter Hibbs]

If you are using Office 2003 and your spreadsheet has less than 200
columns I may be able to help. I have written a database application
which will automatically convert a spreadsheet file into properly
normalised tables in Access. If you are interested in having a copy
email me at (e-mail address removed)_SPAM (remove the NO_SPAM bit
first).

Peter Hibbs.