Sending data from Excel to Access

[ahuffman24]

Hi,



I am using Access 2016 and Excel 2016. I have data that I would like to export from Excel to Access via macros. I need the data in Excel to be entered into their appropriate tables/fields in Access. I found some VBA online for exporting an Excel sheet to an Access table, but this code seems to only be for exporting an Excel sheet to ONE Access table. In my Access database, I have THREE tables that I need data with which the Excel sheet needs to correspond. Here is the code that I found online:

Sub SendToAccess()
Dim conn As Object
Const DATABASE = "G:\Inspection"
Const TABLE = "table1"
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DATABASE
With CreateObject("ADODB.Recordset")
.Open "[" & TABLE & "]", conn, 2, 3 'adOpenDynamic, adLockOptimistic
.AddNew
.Fields("my_value") = "foo"
.Update
.Close
.Open "SELECT * FROM [" & TABLE & "] WHERE [my_value]='foo'", conn, 2, 3 'adOpenDynamic, adLockOptimistic
If Not .EOF Then
.Fields("my_value") = "bar"
.Update
End If
.Close
End With
conn.Close
End Sub



I am not experieneced in VBA at all, so I am wondering if anyone could help me modify this code so that the Excel data will be exported to three different tables: Order_Number_Table , Item_Number_Table , and Serial_Number_Table. Here is a screenshot of the relationships between each of the three tables:






Also, just to clarify, the Excel sheet that I am exporting has data that contains fields from THREE different tables in my Access database. This is because of the way I need to track the data in my Access database. Thank you in advance for your help!

[ssanfu]

I don't use ADO, so I can't help you with the code, but for Importing/Exporting Access/Excel, you might check out the examples from Ken Snell's site:
http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

This site and the examples have helped me a lot.

[June7]

Would have to provide a lot more info for us to develop specific code tailored for your situation. For a start, example of data to export from Excel.

Why not do code from Access? Is Excel file structure simple enough to allow link? Why is Excel involved?

Process is complicated by using autonumber primary keys. Code has to grab new autonumber PK generated when record created and pass that into dependent records of related table.

If you want to provide files, follow instructions at bottom of my post.