G
gab1972
I am sending information from Excel to an Access db. In this series,
I have about 100 columns of information to send. The info is on a
sheet from Range(AN,2) to (EI,2). I am opening the recordset and
updating information. Instead of writing the same line of code 100
times for each cell, is there an easier way to program the column to
go to the next column?? If that makes sense. Below is my code so
far...
as always, everyone on here rocks and thanks for the help in advance.
'open permit.lifecycle recordset
Set rs = New ADODB.Recordset
str = "SELECT * FROM permit_life WHERE permit_num = '" & strPnum &
"'"
rs.Open str, cn, adOpenKeyset, adLockOptimistic ', adCmdTable
' all records in a table
r = 2 ' the start row in the worksheet
' repeat until first empty cell in column A
With rs
.Fields("PLFrm1") = Range("AN" & r).Value
.Fields("PLTo1") = Range("AO & r).Value
' 98 more fields to update up to column EI...
.Update ' stores the new record
End With
'close permit_info recordset and empty memory
rs.Close
Set rs = Nothing
I have about 100 columns of information to send. The info is on a
sheet from Range(AN,2) to (EI,2). I am opening the recordset and
updating information. Instead of writing the same line of code 100
times for each cell, is there an easier way to program the column to
go to the next column?? If that makes sense. Below is my code so
far...
as always, everyone on here rocks and thanks for the help in advance.
'open permit.lifecycle recordset
Set rs = New ADODB.Recordset
str = "SELECT * FROM permit_life WHERE permit_num = '" & strPnum &
"'"
rs.Open str, cn, adOpenKeyset, adLockOptimistic ', adCmdTable
' all records in a table
r = 2 ' the start row in the worksheet
' repeat until first empty cell in column A
With rs
.Fields("PLFrm1") = Range("AN" & r).Value
.Fields("PLTo1") = Range("AO & r).Value
' 98 more fields to update up to column EI...
.Update ' stores the new record
End With
'close permit_info recordset and empty memory
rs.Close
Set rs = Nothing