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?

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?

Thanks again Tom - just didn't spot that - works fine now - Andy.

"Tom Ogilvy" wrote:

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?