ExcelBanter - Access command from Excel

try this way
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 (jet)
'Assumes that the access database is in the same folders as thisworkbook
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
ThisWorkbook.Path & "\NameOfYourmdb.mdb;Persist Security Info=False"

'Use for jet
'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

"JT" wrote:

In my Excel macro, I am trying to import a spreadsheet into Access. I create
an Access object and am trying to use the "transfer spreadsheet" command. It
works but it is only importing the first line in the spreadsheet.

Below is the code I'm using:

AppAcc.DoCmd.TransferSpreadsheet acImport, 8, "Interest_Table",
"\\vs300\cost_center\ABC\UploadFile.xls", True

Any ideas or suggestions are greatly appreciated. Thanks for the help......
--
JT