Primary Key Issues

[rewalk]

Hello all,

I previously posted under this heading: Importing Data from One Excel Sheet into Multiple Access Tables Simultaneously, but the nature of the problem has changed so I decided to make a new discussion. Thanks to one of the members here I was able to successfully import the data into the tables from the excel spreadsheet and I greatly appreciate his help. However, I've run into a new problem. The first table assigns the unique key to the data, and I can't get this autonumber key to go to the other tables. Here is what I need to happen:

Table 1:
Data: 1, X, Y
2, X, Y
3, X, Y

Table 2:
Data: 1, A, B
2, A, B

Table 3:
Data: 3, C, D

Instead, what is happening is it imports that just the correct data into Table 1, but because of the relationship I have in the second table, with the ID's being linked, it won't import into the second table and instead I get an error message.

I need the tables to be set up this way because on the reporting level I might need to join tables together like this:

Report: 1, X, Y, A, B
3, X, Y, C, D

So access needs to be able to see what data matches up with what data in each table.

I've tried to set up code so when it transfers the data from the spreadsheet to the tables it automatically gives the same ID to each one, but I haven't been successful in getting it to work. I also thought of having the data go into a big staging table and then being broken down, but my admins here at work weren't crazy about that idea because they want to keep the database as small as possible.

I'm pretty new to the access scene and could really use some help. Thank you in advance for your time!

[rewalk]

Here is the code that I've made so far which succesfully imports the files. Special thanks to members ont his network that have helped me fix some quirks in this code in the last discussion I made:

Private Sub Done_Click()
 
Dim File As String
Dim LastDataColumn As String, LastDataRow As String
Dim LastDataCell As String
 
'Set File name
File = "C:\Sample Data.xls"
 
'Open the file
Workbooks.Open Filename:=File
 
'Unprotect the file
ActiveWorkbook.Unprotect
ActiveSheet.Unprotect
 
'Obtain the last cell that is used on the sheet
LastDataColumn = Chr(Selection.SpecialCells(xlLastCell).Column + 64)
LastDataRow = Selection.SpecialCells(xlLastCell).Row
LastDataCell = LastDataColumn & LastDataRow
 
'Import the data
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ExcelImport", File, True, "A1:M" & LastDataRow
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ExcelImport2", File, True, "N1:" & LastDataCell
 
'Clear contents of template
Worksheets("Sheet1").range("A2:" & LastDataCell).ClearContents
 
'Protect the file
ActiveWorkbook.Protect
ActiveSheet.Protect
 
'Save the file
ActiveWorkbook.Save
 
'Close the file
ActiveWorkbook.Close
 
End Sub
 

[Lysander]

Instead, what is happening is it imports that just the correct data into Table 1, but because of the relationship I have in the second table, with the ID's being linked, it won't import into the second table and instead I get an error message.

I need the tables to be set up this way because on the reporting level I might need to join tables together like this:

Report: 1, X, Y, A, B
3, X, Y, C, D

So access needs to be able to see what data matches up with what data in each table.

As I understand it, you are importing an excel spreadsheet and splitting the rows in the spreadsheet across two access tables and you want to maintain the links across each row.

You are importing A-M into one table, and N into another. Can you change the spreadsheet so that column N becomes column P and then in Column N put in the row numbers, i.e. 1,2,3,to the last row. Then import A-N and N-P into your tables. Column N would become the primary key in each table and you can then link them.

The only other thing I can think of is the temporay table that your admins dont like. If the data was deleted after the import, and the dbs compacted, size would only be an issue during the import.