AutoNumber behavior after MS Access migration to sql server (1 Viewer)

DevTycoon

Registered User.
Local time
Today, 16:53
Joined
Jun 14, 2014
Messages
94
Hello,

Issue:
When a query is attached to a MS Access table the AutoNumber will show the new ID increment before the record is committed. When the underlying table is in MS SQL the increment does not appear until the record is committed.

This link has the best information I could find so far on auto incrementing columns.
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-identity-or-autonumber-values

The solution provided by the MS link indicates I would need to have an adodb connection that pulls the next id using IDENT_CURRENT function.

The reason I am asking is because when new records are created I track the primary key / form name that created the record using the before update event. When the table is on SQL server the primary key is null in the audit table for new records.

Thanks,
 

RuralGuy

AWF VIP
Local time
Today, 17:53
Joined
Jul 2, 2005
Messages
13,826
If you use a different approach, maybe the AfterUpdate event, I believe it should work for either situation.
 

DevTycoon

Registered User.
Local time
Today, 16:53
Joined
Jun 14, 2014
Messages
94
Thanks for the reply.

I have been looking into the other approaches like you mentioned.



When the tables are in access I can easily capture the PK using the before
update event and then use that id to map audit records to the
destination table. That subtle "*feature*" in access tried to foil my
migration to sql. Nice try JET :cool:

Would anyone have like to talk shop on the strategy?


I looked into stored procedures with output parameters but I would need to execute the sp for each row of data and some times there can be alot of rows.

Use a MARS ado connection to fill up the recordset faster maybe...

Thanks for the conversation

===============


Code:
'_____________ado connection
set conn = new adodb connection 

'_____________establish ado recordset 
set adoRS_Stage  = new adodb.recordset
adoRS_Destination  = new adodb.recordset
adoRS_Audit  = new adodb.recordset

'____________connect to ado recordset
adoRS_Stage = select * from stageTable where 1=0,conn
adoRS_Destination = select * from destinationTable where 1 = 0 ,conn
adoRS_Audit = select * from auditTable where 1=0,conn


'_____________close db connections and start the 
'_____________batch processing on the recordsets



'_____________build field list used to map values between the three rs
'_____________(1) audit trail and (2 & 3) mirror between 
'_____________stage table and target table

for counter = 0 to adoRS_Stage.fields.count-1
[INDENT]MyFieldArray(counter) = adoRS_Stage.fields(counter)[/INDENT]
next counter



'______________Create the batch timestamp right before
'______________Working on records to test performance 
'______________help gauge processing time per 1000 records
batchTimeStamp = now()


'______Begin the official batch process with field 
'______manipulations for each record in the stage table

for i = 1 to adoRS_Stage.recordcount
[INDENT]with adoRS_Stage
[INDENT].add
.field("BatchTimeStamp") = batchTimeStamp 
.field("BatchSequence") = i[/INDENT]
end with[/INDENT]

'_______Next phase in the batch

'_______Mirror the stage rs to destination rs in memory my matching
'_______element names.  I have conveniently used the same column
'_______names in each recordset's underlying table so I can match
'_______on column name, irregardless of the recordset field index number



[INDENT]with adoRS_TargetTable
[INDENT]
.add
for each arrayElement in MyFieldArray
[INDENT]adoRS_Stage("element name") = ado_Destination("element name")[/INDENT]
next ArrayElement

.fields("BatchTimeStamp")
.fields("BatchSequence")
.fields("CreatedOn")
.fields("ModifiedOn")
.fields("AccessedOn")
.fields("CreatedBy")
.fields("ModifiedBy")
.fields("AccessedBy")
[/INDENT]
end with
[/INDENT]




'capture these details in the audit rs only the action type 

batch id
,batch time stamp
,user
,date time created




'____________connect to ado recordset
adoRS_Stage = select * from stageTable where 1=0,conn
adoRS_Destination = select * from destinationTable where 1 = 0 ,conn
adoRS_Audit = select * from auditTable where 1=0,conn




'_____________use the ado bulk update command
adoRS_Stage .BulkUpdate
adoRS_Destination.BulkUpdate 
adoRS_Audit.BulkUpdate


'
'_____________Housekeeping

'_____________delete the stage table records where 
'_____________batch id / timestamp match in the destination table

'_____________update the audit table with the matching
'_____________destination primary key for every matching
'_____________batch id / batch timestamp
 

Minty

AWF VIP
Local time
Today, 23:53
Joined
Jul 26, 2013
Messages
10,355
SQL Server (2008 Onwards I think) has inbuilt auditing functions, that might be much easier to implement.

If not a trigger on Update or Insert will work, but may well cause you a fair bit of programming.
 

DevTycoon

Registered User.
Local time
Today, 16:53
Joined
Jun 14, 2014
Messages
94
Hi Minty, are you referring to the CDC technology? That is on the enterprise version but not basic version from what I can read online.
 

Minty

AWF VIP
Local time
Today, 23:53
Joined
Jul 26, 2013
Messages
10,355
Ah - Yes CDC I have to confess I wasn't aware it wasn't available on the "lightweight" versions. According to teh interwebs you are unfortunately correct;
Change Data Capture is available only in SQL Server 2008 Enterprise, Developer, and Evaluation editions.

Welcome to the stored procedures and triggers slight learning curve.
 

Users who are viewing this thread

Top Bottom