Transfering data from Excel to linked table in Access

[Guest]

I'm having a few difficulties exporting data from an Excel spreadsheet to a
table in an Access database.

I've used this code successfully on native tables;

.....................................................................................

Sub ExcelToAccess()

Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\FolderName\DataBaseName.mdb")
Set rs = db.OpenRecordset("TableName", dbOpenTable)
r = 3
Do While Len(Range("A" & r).Formula) > 0

With rs
.AddNew
.Fields("FieldName1") = Range("A" & r).Value
.Fields("FieldName2") = Range("B" & r).Value
.Fields("FieldNameN") = Range("C" & r).Value
.Update
End With
r = r + 1
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub
..............................................................................

The problem is that it won't work when I'm trying to open a linked table.
What code do I use so I can export data to a linked table?


Cheers

 

[Ken Snell [MVP]]

Change this line of code:
Set rs = db.OpenRecordset("TableName", dbOpenTable)

to this line of code:
Set rs = db.OpenRecordset("TableName", dbOpenDynaset)


The dbOpenTable option does not work for linked tables.