Solved Pass date as a timestamp to SQL Server (1 Viewer)

NigelBishop

New member
Local time
Today, 09:21
Joined
Oct 4, 2019
Messages
14
Hello a quick back story

I have an access frontend hocked up to an SQL Server backend and all is working fine.
I have a form in Access is bound to a table in SQL and manually populating the form causes no issues.
I have been asked to add the ability to duplicate records to assist in data entry and to reduce data entry errors.

There is a Timestamp field [ValCol] in the table which is causing issues when I try to duplicate the record, I understand what the Timestamp field is within SQL and it automatically populates correctly when data is manually entered, however it doesn't appear to populate when I try to duplicate a record.

I am using the following code to perform the duplication (not all fields within the record are duplicated)
If I change the table to a local table and make the field [ValCol] not required the code runs fine.(the Highlighted code is not included)

To make the code run correctly with the SQL Backend I'm guessing that I need to provide the [ValCol] value however I have no Idea how to achieve this so any help would be gratefully received, maybe I need to take a completely different approach?

With Me.RecordsetClone

.ADDNEW
![ResultDate].Value = [T_RDate]
![RefType].Value = [RefType]
![QCRef].Value = [QCRef]
![Area].Value = [Area]
![TypeOfSample].Value = [TypeOfSample]
![Room].Value = [Room]
![Site].Value = [Site]
![Enteredby].Value = UCase(NetworkUserName())

' ![ValCol].Value = ?????????? ----------This is the part of the code I need help with

.Update

' 'Display the new duplicate.
Me.Bookmark = .LastModified

End With

Thank you in advance
 

June7

AWF VIP
Local time
Today, 08:21
Joined
Mar 9, 2014
Messages
5,423
I tested this code. I viewed the table in SSMS and Timestamp field has value but it is not displayed as a date. Field is set as required.
AFAIK, this field cannot be edited.
 

Minty

AWF VIP
Local time
Today, 16:21
Joined
Jul 26, 2013
Messages
10,354
The Timestamp is like an autonumber generated by the server, as far as I know you can't set it's value, it has to be set by the server.
 

GPGeorge

Grover Park George
Local time
Today, 09:21
Joined
Nov 25, 2004
Messages
1,776
The TimeStamp, or more properly, RowVersion, datatype in SQL Server is NOT a date at all, despite the misleading name. In fact, MS has been trying for years to get people to use the more descriptive name, RowVersion, with only limited success. Unfortunately, they don't help the situation by leaving the TimeStamp name in the list of datatypes you see when creating a new field.

It is incremented by SQL Server each time any field in a record is modified. This allows Access to know accurately without having to check each and every field in a record, if there has been a change to that record. If the timestamp has changed "something" else in that record was also changed.

When concurrent editing is going on among multiple users, this is an important aspect of maintaining speed and accuracy of data updates.

Leave it alone! It's not there for your use anyway. Omit it from your attempt to duplicate records.
 

Users who are viewing this thread

Top Bottom