Recordsetclone on SQL backend

S

Steve

I have upconverted my Access 2003 database backend to SQLExpress 2005. Most
of the functions work with a few minor glitches. The biggest is the fact that
I can not duplicate records anymore. Here is the VBA Code I have been using
with Access backend...

Private Sub Command57_Click()
On Error GoTo Err_Command57_Click
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim f As Form

Set dbs = CurrentDb
Set rst = Me.RecordsetClone

On Error GoTo Err_Command57_Click
Me.Tag = Me![PurorderID]

With rst
.AddNew
!SupplierID = Me!SupplierID
!Address = Me!Address
!City = Me!City
!StateOrProvince = Me!StateOrProvince
!PostalCode = Me!PostalCode
!PhoneNumber = Me!PhoneNumber
!PoDate = Now()
!EmployeeID = intuser
!PoStatus = "creating"
!ShipViaID = Me!ShipViaID
!Payment = Me!Payment
!ShipToName = Me!ShipToName
!ShiptoAddress = Me!ShiptoAddress
!ShipToCity = Me!ShipToCity
!ShipToState = Me!ShipToState
!ShipToZip = Me!ShipToZip
!jobname = Me!jobname
!PoNotes = Me!PoNotes
!Spec = Me!Spec
!InHouseNotes = Me!InHouseNotes
!fsc = 1

..Update
..Move 0, .LastModified
End With
Me.Bookmark = rst.Bookmark

DoCmd.SetWarnings False
DoCmd.OpenQuery "copypoFSC"
DoCmd.SetWarnings True
Me![frmPurchaseOrderDetailFSC].Requery

Exit_Command57_Click:
Exit Sub

Err_Command57_Click:
MsgBox Err.Description
Resume Exit_Command57_Click

End Sub

After the line " !SupplierID = Me!SupplierID" I get an error and message box
that it was unable to update. Any thoughts. The goal is to copy all the
records information but with a different record ID or primary key.

Thanks in advance.
Steve
 
D

David W. Fenton

I have upconverted my Access 2003 database backend to SQLExpress
2005. Most of the functions work with a few minor glitches. The
biggest is the fact that I can not duplicate records anymore. Here
is the VBA Code I have been using with Access backend...
Click to expand...

Doing updates row-by-row to a form's RecordsetClone is completely
batshit crazy. There is no justification for the existence of your
code in the first place -- it's simply the WRONG APPROACH to the
problem. It's so wrong in so many ways that it's hard to even begin
to explain why it's just WRONG.
 
D

Dale Fye

Steve,

When you migrated to SQL Server, did you allow the wizard to insert a
TimeStamp field in the tables? It has been a while since I've had access to
SQL Server, but I seem to remember that in order for the form to be
updateable the way you are attempting, you need to have a timestamp field in
the table (don't quote me on this, I looked on Google, but did not find an
the article I thought I read a couple of months ago). The timestamp (or
RowVersion) datatype is just a unique identifier that SQL server uses. Don't
put any data in it (SQL Server will do that) and don't do anything else with
that field.

I agree with Alex, that it would probably be more efficient to write an
append query or a stored procedure. Pass it the ID of the record you are on,
and let it return the primary key value of the new record to your code. Then
requery the form and use FindFirst to move to the new record.

But I would try the timestamp thing first.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Alex Dybenko said:
Hi,
are you sure that you can update in a form?
anyway - better approach in to run insert query, preferable pass-through one

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


Steve said:
I have upconverted my Access 2003 database backend to SQLExpress 2005.
Most
of the functions work with a few minor glitches. The biggest is the fact
that
I can not duplicate records anymore. Here is the VBA Code I have been
using
with Access backend...

Private Sub Command57_Click()
On Error GoTo Err_Command57_Click
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim f As Form

Set dbs = CurrentDb
Set rst = Me.RecordsetClone

On Error GoTo Err_Command57_Click
Me.Tag = Me![PurorderID]

With rst
.AddNew
!SupplierID = Me!SupplierID
!Address = Me!Address
!City = Me!City
!StateOrProvince = Me!StateOrProvince
!PostalCode = Me!PostalCode
!PhoneNumber = Me!PhoneNumber
!PoDate = Now()
!EmployeeID = intuser
!PoStatus = "creating"
!ShipViaID = Me!ShipViaID
!Payment = Me!Payment
!ShipToName = Me!ShipToName
!ShiptoAddress = Me!ShiptoAddress
!ShipToCity = Me!ShipToCity
!ShipToState = Me!ShipToState
!ShipToZip = Me!ShipToZip
!jobname = Me!jobname
!PoNotes = Me!PoNotes
!Spec = Me!Spec
!InHouseNotes = Me!InHouseNotes
!fsc = 1

.Update
.Move 0, .LastModified
End With
Me.Bookmark = rst.Bookmark

DoCmd.SetWarnings False
DoCmd.OpenQuery "copypoFSC"
DoCmd.SetWarnings True
Me![frmPurchaseOrderDetailFSC].Requery

Exit_Command57_Click:
Exit Sub

Err_Command57_Click:
MsgBox Err.Description
Resume Exit_Command57_Click

End Sub

After the line " !SupplierID = Me!SupplierID" I get an error and message
box
that it was unable to update. Any thoughts. The goal is to copy all the
records information but with a different record ID or primary key.

Thanks in advance.
Steve
Click to expand...
Click to expand...
 
S

Steve

David thank you for confirming this.
I did not write the code, I am just trying to get it fixed. What approach
can I use? I have tried the DoCmd.RunCommamd approach and it has failed due
to it trying to copy the Primary key. There is also an issue with SQL and
RecordCountClone. Any direction that in this matter would be nice.
 
D

David W. Fenton

David thank you for confirming this.
I did not write the code, I am just trying to get it fixed. What
approach can I use? I have tried the DoCmd.RunCommamd approach and
it has failed due to it trying to copy the Primary key. There is
also an issue with SQL and RecordCountClone. Any direction that in
this matter would be nice.
Click to expand...

I'm not sure what the code is trying to do, so I can't really
suggest what should be done. If you're copying a previous record to
a new record, I think I'd like use DoCmd.RunCommand
acCmdRecordsGoToNew to get a new blank record, and then navigate to
the record being copied in the RecordsetClone and copy fields to the
new record, skipping the PK, of course.

The point is, you edit the form's edit buffer, not the
RecordsetClone, and use the RecordsetClone only as the source of
read-only data.
 
S

Steve

Thanks David,
A little breif on this code. It is duplicating the item of an order. It is a
sub table to the order table. The sub table uses the Order table's PK and the
sub tables Item number as its PK.

This code works great in just Access but when I moved the backend to SQL it
sucks. There are a lot of refrenceses to the RecordSetClone through out the
code. It seams that SQL does not like to use them so I am working on an
correct way to have the code work.
 
D

David W. Fenton

A little breif on this code. It is duplicating the item of an
order. It is a sub table to the order table. The sub table uses
the Order table's PK and the sub tables Item number as its PK.
Click to expand...

If you're in a form, using the RecordsetClone to look up the
previous item's data should work well.
This code works great in just Access but when I moved the backend
to SQL it sucks. There are a lot of refrenceses to the
RecordSetClone through out the code. It seams that SQL does not
like to use them so I am working on an correct way to have the
code work.
Click to expand...

I think it was bad code in Access -- editing the RecordsetClone is
just not something that is advisable. Using it to look up
information (or for navigation) is fine, as you're not changing it.

As I said, I'd create the new record in the form, then use the
RecordsetClone to look up the data from the previous record and then
copy it from the RecordsetClone to the new record, field by field.
That should work well regardless of the back end.

In other words, treat the RecordsetClone as a read-only object and
you should be fine.
 
Top