sorry I forgot the code:

Option Explicit

Sub AddRecordsToDB()

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''
'Setup:
'Access: An MS Access file: NewTestDB.mdb in same folder as This Excel
Workbook
' The mdb has one Table: MyTable
' This table has 3 Fields: Staff_Num, Salary, LastName
'Excel: On Sheet1:
' Range("A1:A100") is named MyRange
' Range("A1") is named MyCol1 value: Staff_Num
' Range("B1") is named MyCol2 value: Salary
' Range("C1") is named MyCol3 value: LastName
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''

Dim con As Object
Dim sPath As String
Dim sDbFile As String
Dim sMyTable As String
Dim MyCol1, MyCol2, MyCol3
Dim sThisWbk As String
Dim sBkName As String
Dim Rng1 As Range


sBkName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)
sPath = ThisWorkbook.Path
sThisWbk = sPath & "\" & sBkName

sDbFile = sPath & "\NewTestDB.mdb"
sMyTable = "MyDbTable"

'Get Field names
MyCol1 = Range("MyCol1").Value 'Field names only-no data ????
MyCol2 = Range("MyCol2").Value
MyCol3 = Range("MyCol3").Value
''''Perhaps the above ranges should include data ????????

'Locate MyRange in Excel (A1:C100)
'This has both data and Field name headers
'which match the .mdb table fields
''''''''''''''''''''''
'This perhaps should exclude headers ?????
'and start with row 2 ????????
''''''''''''''''''
Set Rng1 = Range("MyRange")

Set con = CreateObject("ADODB.Connection")

con.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sDbFile & ""

con.Execute _
"INSERT INTO " & sMyTable & " (" & MyCol1 & "," & MyCol2 & "," & MyCol3
& "," & MyCol4 & "," & MyCol5 & "," & MyCol6 & ")" & _
" SELECT " & MyCol1 & "," & MyCol2 & "," & MyCol3 & "," & MyCol4 & "," &
MyCol5 & "," & MyCol6 & " FROM" & _
" [Excel 8.0;HDR=YES;Database=" & ThisWbk & ";]." & Rng1 & ";"

''''''' Perhaps the above Excel 8.0 is not correct for my Excel 2000
???????
End Sub



"Jamie Collins" wrote:


gocush wrote:
Has someone written a macro to run in Excel that will append a
fixed range of
data into an existing Access table.

The following is code that I got from Andy Wiggins some time ago
<snip

That's a lot of code <g (and anyhow doesn't do what the OP asked i.e.
creates a new table rather than inserting into an existing table). I
think this could be a job for 'ADO in just four lines':

Sub JustFourBygLines()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
con.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\MyJetDB.mdb"
con.Execute _
"INSERT INTO MyNewtable (MyCol1, MyCol2, MyCol2)" & _
" SELECT MyCol1, MyCol2, MyCol3 FROM" & _
" [Excel 8.0;HDR=YES;Database=C:\MyWorkbook;].MyRange;"
End Sub

Jamie.

--