...more repetitive copying
 

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

...more repetitive copying
 

I haven't tested but this is a direction i'd try out:

Dim ndxFlds As Integer 'Index for looping through fields
Dim ndxCols As Integer 'Index for looping through .xls columns

ndxCols = 40 'i.e., column AN

With rs
For ndxFlds = 1 To .Fields.Count
.Fields(ndxFlds) = Cells(r, ndxCols)
.Update
ndxCols = ndxCols + 1
Next
End With

This, of course, presumes that the field and column order is the same
and they correspond.


On Jul 15, 1:21*pm, gab1972 wrote:
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

...more repetitive copying
 

This would be perfect...one caveat though...can I start the ndxFlds
count to start on the second field? I have information going from
Excel to 4 different Access DB's. This is just one section of of the
ws going to a specific db. The common denominator for all the db's is
the permit_num - which is the primary key in each db. So, in each db,
the first record is the permit number then all the other information.
So, I don't want to update that. So if I use the .Fields.Count...I
need for it to start counting on the second record. Make sense? Any
ideas? Thanks in advance.

AB wrote:
I haven't tested but this is a direction i'd try out:

Dim ndxFlds As Integer 'Index for looping through fields
Dim ndxCols As Integer 'Index for looping through .xls columns

ndxCols = 40 'i.e., column AN

With rs
For ndxFlds = 1 To .Fields.Count
.Fields(ndxFlds) = Cells(r, ndxCols)
.Update
ndxCols = ndxCols + 1
Next
End With

This, of course, presumes that the field and column order is the same
and they correspond.


On Jul 15, 1:21*pm, gab1972 wrote:
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

...more repetitive copying
 

As i said, i haven't tested the code but if it works then you can
start the ndx at any number less than or equal to the final number,
i.e. the only condition for this line

For ndxFlds = 1 To .Fields.Count

is that the first number ('1' in this instance) needs to be less or
equal to field.count, so, you can sart at
For ndxFlds = 1
or
For ndxFlds = 2
or
For ndxFlds = 3
....
Doesn't matter. The same way as i started the Column Index at 40 -
it's just a number that you need.



On Jul 15, 1:57*pm, gab1972 wrote:
This would be perfect...one caveat though...can I start the ndxFlds
count to start on the second field? *I have information going from
Excel to 4 different Access DB's. *This is just one section of of the
ws going to a specific db. *The common denominator for all the db's is
the permit_num - which is the primary key in each db. *So, in each db,
the first record is the permit number then all the other information.
So, I don't want to update that. *So if I use the .Fields.Count...I
need for it to start counting on the second record. *Make sense? *Any
ideas? *Thanks in advance.



AB wrote:
I haven't tested but this is a direction i'd try out:

Dim ndxFlds As Integer 'Index for looping through fields
Dim ndxCols As Integer 'Index for looping through .xls columns

ndxCols = 40 'i.e., column AN

With rs
* * For ndxFlds = 1 To .Fields.Count
* * * * .Fields(ndxFlds) = Cells(r, ndxCols)
* * * * .Update
* * * * ndxCols = ndxCols + 1
* * Next
End With

This, of course, presumes that the field and column order is the same
and they correspond.

On Jul 15, 1:21*pm, gab1972 wrote:
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- Hide quoted text -

- Show quoted text -

...more repetitive copying
 

wow...thanks!!! That makes complete sense. I'm going to give it a
try. I really appreciate this. You just saved me tons of copying,
pasting, referring to what cell is what.

AB wrote:
As i said, i haven't tested the code but if it works then you can
start the ndx at any number less than or equal to the final number,
i.e. the only condition for this line

For ndxFlds = 1 To .Fields.Count

is that the first number ('1' in this instance) needs to be less or
equal to field.count, so, you can sart at
For ndxFlds = 1
or
For ndxFlds = 2
or
For ndxFlds = 3
...
Doesn't matter. The same way as i started the Column Index at 40 -
it's just a number that you need.



On Jul 15, 1:57*pm, gab1972 wrote:
This would be perfect...one caveat though...can I start the ndxFlds
count to start on the second field? *I have information going from
Excel to 4 different Access DB's. *This is just one section of of the
ws going to a specific db. *The common denominator for all the db's is
the permit_num - which is the primary key in each db. *So, in each db,
the first record is the permit number then all the other information.
So, I don't want to update that. *So if I use the .Fields.Count...I
need for it to start counting on the second record. *Make sense? *Any
ideas? *Thanks in advance.



AB wrote:
I haven't tested but this is a direction i'd try out:

Dim ndxFlds As Integer 'Index for looping through fields
Dim ndxCols As Integer 'Index for looping through .xls columns

ndxCols = 40 'i.e., column AN

With rs
* * For ndxFlds = 1 To .Fields.Count
* * * * .Fields(ndxFlds) = Cells(r, ndxCols)
* * * * .Update
* * * * ndxCols = ndxCols + 1
* * Next
End With

This, of course, presumes that the field and column order is the same
and they correspond.

On Jul 15, 1:21*pm, gab1972 wrote:
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- Hide quoted text -

- Show quoted text -

...more repetitive copying
 

Thanks for the feedback.
Post back if didn't work - will try figuring out something else.

On Jul 15, 2:09*pm, gab1972 wrote:
wow...thanks!!! *That makes complete sense. *I'm going to give it a
try. *I really appreciate this. *You just saved me tons of copying,
pasting, referring to what cell is what.



AB wrote:
As i said, i haven't tested the code but if it works then you can
start the ndx at any number less than or equal to the final number,
i.e. the only condition for this line

For ndxFlds = 1 To .Fields.Count

is that the first number ('1' in this instance) needs to be less or
equal to field.count, so, you can sart at
For ndxFlds = 1
or
For ndxFlds = 2
or
For ndxFlds = 3
...
Doesn't matter. The same way as i started the Column Index at 40 -
it's just a number that you need.

On Jul 15, 1:57*pm, gab1972 wrote:
This would be perfect...one caveat though...can I start the ndxFlds
count to start on the second field? *I have information going from
Excel to 4 different Access DB's. *This is just one section of of the
ws going to a specific db. *The common denominator for all the db's is
the permit_num - which is the primary key in each db. *So, in each db,
the first record is the permit number then all the other information.
So, I don't want to update that. *So if I use the .Fields.Count...I
need for it to start counting on the second record. *Make sense? *Any
ideas? *Thanks in advance.

AB wrote:
I haven't tested but this is a direction i'd try out:

Dim ndxFlds As Integer 'Index for looping through fields
Dim ndxCols As Integer 'Index for looping through .xls columns

ndxCols = 40 'i.e., column AN

With rs
* * For ndxFlds = 1 To .Fields.Count
* * * * .Fields(ndxFlds) = Cells(r, ndxCols)
* * * * .Update
* * * * ndxCols = ndxCols + 1
* * Next
End With

This, of course, presumes that the field and column order is the same
and they correspond.

On Jul 15, 1:21*pm, gab1972 wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -