Using an Access recordset to update SQL backend

Hello,

This is an Access2003 and SQLServer2005 question. I have a form for a
billing application that uses a temporary table to view and process
items. We have three buttons on this form that change the billing
action to one of three states:

Invoice
Hold (default)
Missing

If an item is ready to be invoiced, the status is "I" for invoiced. If
not, it is either "H" for hold or "M" for missing. When the Invoice
All button is clicked the code is:

Private Sub cmdInvoiceAll_Click()
Dim rs As DAO.Recordset
Dim cSQL As String

Set rs = Me!fsubBillingDetail.Form.RecordsetClone
With rs
.MoveFirst
Do While Not .EOF
If !Action <> "M" Then
.Edit
!Action = "I"
.Update
End If
.MoveNext
Loop
.MoveFirst
End With
End If

' change the field in SQL server
cSQL = "UPDATE tblBilling SET BillingAction = 'I'" & pstrUpdate
cSQL = cSQL & " and tblBilling.BillingAction <> 'M'"
conn.Execute cSQL
Me![fsubAggregate].Requery
End Sub

This sets the action for all the temp records to "I" (except 'M' ones)
and then writes this back to the SQLServer backend. It is pretty
straight ahead since all of the records are being updated. The
variable pstrUpdate contains a WHERE clause with the customer account
number (selected from a combo box).

What I would like to do is give the option of setting only a certain
number of records to "I" and then writing only those changes to the
SQLServer backend.
Here is what I have so far:

Private Sub cmdInvoiceRange_Click()

Dim rs As DAO.Recordset
Dim cSQL As String
Dim Message, Title, Default
Dim InputValue As Integer
Dim i As Integer
Dim rST As New ADODB.Recordset

i = 1
InputValue = 0
Set rs = Me!fsubBillingDetail.Form.RecordsetClone

Message = "Enter a value: "
Title = "Invoice Partial"
Default = "1"
InputValue = InputBox(Message, Title, Default)
With rs
.MoveFirst
For i = i To InputValue
If !Action <> "M" Then
.Edit
!Action = "I"
.Update
.MoveNext
End If
Next
End With
End If

' change the field in SQL server

cSQL = "SELECT * from tblBilling " & pstrUpdate
rST.Open cSQL, conn, adOpenDynamic, adLockOptimistic
If rs.BOF And rs.EOF Then
' do nothing
Else
With rST
!Action = fsubBillingDetail!txtAction
.Update
End With
End If
rs.Close
If rST.State = 1 Then rST.Close
End Sub

The record set is the entire temp table, but it only updates the
number of records the user inputs. So if there are 30 items on hold
and the user selects 5, only the first 5 will be changed to "I" and
they will be invoiced.

Now I need to take only the records that have been changed in the temp
table and write the new Action back to the SQLServer table. The
temporary table is called tblBillingTemp and the backend table is
called tblBilling. When I run this I get an error: Method 'Collect' of
object '_Recordset' failed. I need to create a new rs for just the
items that were changed and then use that rs to update the SQLServer
table. I am unsure how to do this.

Thanks in advance for any help!
Albert

Re: Using an Access recordset to update SQL backend

On Thu, 10 Apr 2008 15:40:32 -0700 (PDT), Albert
wrote:

All kinds of bad programming here. Hard to decide where to begin.

* Do not MoveFirst until you have established there is at least a
record to move to:
if rs.RecordCount>0 then
rs.MoveFirst
'etc.

* Way to much VBA code where the same could be accomplished with a
parameter query. E.g. your first code block can be replaced by an
Update query, similar to this pseudo-code:
update SomeTable
set Action="I"
where Action<> "M" and ForeignKeyValue=[parFkValue]

* Method 'Collect' error: None of this code is showing use of this
method - you're likely leaving some things out. Hit Ctrl+Break when
the error occurs and you should be able to find the line where it
happened.

* Dim Message, Title, Default: MUCH better to declare one variable per
line, and give it a data type. You have OPTION EXPLICIT on, right?

* rST.State = 1: there is a constant for that. Don't use magic values.

-Tom.


>Hello,
>
>This is an Access2003 and SQLServer2005 question. I have a form for a
>billing application that uses a temporary table to view and process
>items. We have three buttons on this form that change the billing
>action to one of three states:
>
>Invoice
>Hold (default)
>Missing
>
>If an item is ready to be invoiced, the status is "I" for invoiced. If
>not, it is either "H" for hold or "M" for missing. When the Invoice
>All button is clicked the code is:
>
>Private Sub cmdInvoiceAll_Click()
>Dim rs As DAO.Recordset
>Dim cSQL As String
>
> Set rs = Me!fsubBillingDetail.Form.RecordsetClone
> With rs
> .MoveFirst
> Do While Not .EOF
> If !Action <> "M" Then
> .Edit
> !Action = "I"
> .Update
> End If
> .MoveNext
> Loop
> .MoveFirst
> End With
> End If
>
> ' change the field in SQL server
> cSQL = "UPDATE tblBilling SET BillingAction = 'I'" & pstrUpdate
> cSQL = cSQL & " and tblBilling.BillingAction <> 'M'"
> conn.Execute cSQL
> Me![fsubAggregate].Requery
>End Sub
>
>This sets the action for all the temp records to "I" (except 'M' ones)
>and then writes this back to the SQLServer backend. It is pretty
>straight ahead since all of the records are being updated. The
>variable pstrUpdate contains a WHERE clause with the customer account
>number (selected from a combo box).
>
>What I would like to do is give the option of setting only a certain
>number of records to "I" and then writing only those changes to the
>SQLServer backend.
>Here is what I have so far:
>
>Private Sub cmdInvoiceRange_Click()
>
> Dim rs As DAO.Recordset
> Dim cSQL As String
> Dim Message, Title, Default
> Dim InputValue As Integer
> Dim i As Integer
> Dim rST As New ADODB.Recordset
>
> i = 1
> InputValue = 0
> Set rs = Me!fsubBillingDetail.Form.RecordsetClone
>
> Message = "Enter a value: "
> Title = "Invoice Partial"
> Default = "1"
> InputValue = InputBox(Message, Title, Default)
> With rs
> .MoveFirst
> For i = i To InputValue
> If !Action <> "M" Then
> .Edit
> !Action = "I"
> .Update
> .MoveNext
> End If
> Next
> End With
> End If
>
> ' change the field in SQL server
>
> cSQL = "SELECT * from tblBilling " & pstrUpdate
> rST.Open cSQL, conn, adOpenDynamic, adLockOptimistic
> If rs.BOF And rs.EOF Then
> ' do nothing
> Else
> With rST
> !Action = fsubBillingDetail!txtAction
> .Update
> End With
> End If
> rs.Close
> If rST.State = 1 Then rST.Close
>End Sub
>
>The record set is the entire temp table, but it only updates the
>number of records the user inputs. So if there are 30 items on hold
>and the user selects 5, only the first 5 will be changed to "I" and
>they will be invoiced.
>
>Now I need to take only the records that have been changed in the temp
>table and write the new Action back to the SQLServer table. The
>temporary table is called tblBillingTemp and the backend table is
>called tblBilling. When I run this I get an error: Method 'Collect' of
>object '_Recordset' failed. I need to create a new rs for just the
>items that were changed and then use that rs to update the SQLServer
>table. I am unsure how to do this.
>
>Thanks in advance for any help!
>Albert

Re: Using an Access recordset to update SQL backend

On Apr 10, 8:49=A0pm, Tom van Stiphout wrote:
> On Thu, 10 Apr 2008 15:40:32 -0700 (PDT), Albert
>
> wrote:
>
> All kinds of bad programming here. Hard to decide where to begin.
>
> * Do not MoveFirst until you have established there is at least a
> record to move to:
> if rs.RecordCount>0 then
> =A0 rs.MoveFirst
> =A0 'etc.
>
> * Way to much VBA code where the same could be accomplished with a
> parameter query. E.g. your first code block can be replaced by an
> Update query, similar to this pseudo-code:
> update SomeTable
> =A0 set Action=3D"I"
> =A0 where Action<> "M" and ForeignKeyValue=3D[parFkValue]
>
> * Method 'Collect' error: None of this code is showing use of this
> method - you're likely leaving some things out. Hit Ctrl+Break when
> the error occurs and you should be able to find the line where it
> happened.
>
> * Dim Message, Title, Default: MUCH better to declare one variable per
> line, and give it a data type. You have OPTION EXPLICIT on, right?
>
> * rST.State =3D 1: there is a constant for that. Don't use magic values.
>
> -Tom.
>
>

OK, I will try to your suggestions and clean it up...

Thanks,
Albert