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
|
Similar Threads
