Mike, thanks for this; I will try it out. I did not get very far with the
automation method as posted by Jake; I know a hell of a lot more about Access
than Excel (hence wanting to transfer the data there), this should get me
going again.
Cheers.
--
Nigel Legg


"Mike" wrote:

Option Explicit
Private Sub saveDataToAccess()
'Needs reference the Axtive X Library 2.0 or higher
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String, strConn
Dim r As Integer
r = 6
'Use for Access (Ace No Security)
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=C:\myFolder\myAccess2007file.accdb;" _
& "Persist Security Info=False;"
'Use for Access (Ace With Security)
'strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=C:\myFolder\myAccess2007file.accdb;" _
'& "Jet OLEDB:Database Password=MyDbPassword;"

'sSQL = Name Of Your Access table Change to your
'Table Name
sSQL = "TableName"

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open strConn

rs.Open sSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdTable
Do While Len(Range("A" & r).Formula) 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Field1") = Range("A" & r).Value
.Fields("Field2") = Range("E" & r).Value
.Fields("Field3") = Range("F" & r).Value
.Fields("Field4") = Range("G" & r).Value

' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
cnn.Close

End Sub

"Nigel Legg" wrote:

I have an Access 2007 database, which I want to write different sets of data
into. The datawill come from a web app in tab delimited or XML files, and
will require some reformatting in Excel prior to adding to Access, for
example the column headers in the tab delimited file become records in a
table. I therefore need to open my access database and the correct table
within it, and append the data, using VBA. How is this done? I can find
plenty of reference to taking data in the opposite direction, but nothing on
writing to Access.

--
Nigel Legg