G
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
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