DAO Excel to Access
Shouldn't adding a new record be above your loop:
Private Sub CommandButton1_Click()
Dim ma(6, 1) As Long
Dim c As Long
Dim fn As String
Dim db As Database
Dim rs As Recordset
ma(1, 1) = Cells(4, 2).Value
ma(2, 1) = Cells(5, 2).Value
ma(3, 1) = Cells(6, 2).Value
Set db = OpenDatabase("F:\AJR Dev\ExceltoAccess.mdb")
' open the database
Set rs = db.OpenRecordset("Table1", dbOpenTable)
c = 1
With rs
.AddNew ' create a new record
Do While c < 4
' add values to each field in the record
fn = "Field" & c
.Fields(fn) = ma(c, 1)
c = c + 1 ' next value
Loop
.Update ' store the new record
End With
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub
--
Regards,
Tom Ogilvy
"Andy" wrote in message
...
I'm trying to store 3 values held in range (B4:B6) in an Acces table using
DAO.
Here is the code in Excel :-
Private Sub CommandButton1_Click()
Dim ma(6, 1) As Long
Dim c As Long
Dim fn As String
Dim db As Database
Dim rs As Recordset
ma(1, 1) = Cells(4, 2).Value
ma(2, 1) = Cells(5, 2).Value
ma(3, 1) = Cells(6, 2).Value
Set db = OpenDatabase("F:\AJR Dev\ExceltoAccess.mdb")
' open the database
Set rs = db.OpenRecordset("Table1", dbOpenTable)
c = 1
With rs
Do While c < 4
.AddNew ' create a new record
' add values to each field in the record
fn = "Field" & c
.Fields(fn) = ma(c, 1)
c = c + 1 ' next value
Loop
.Update ' store the new record
End With
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub
It works fine BUT only stores Field3 value (B6) each time - any ideas?
|