Issue with migration to SQL Server and .Addnew (1 Viewer)

B

biofaku

Member
Local time
Yesterday, 22:31
Joined
May 15, 2020
Messages
72
Hello there!
I am moving the backend of my access db to SQL Server and I found an issue that Im not able to solve. When I run this code:


Code: 
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim Form As Integer
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tbl_users")
    Dim fld As Field
    datTimeCheck = Now()
        
        rs.addnew
        
        rs![U_Nom] = Me.txt_name
        rs![U_Ape] = Me.txt_lastname
        rs![U_Ini] = Me.txt_ini
        rs![U_Sec] = Me.cbo_sect
        rs![U_Act] = True
        rs![U_Login] = Me.txt_user
        rs![U_Gen] = Me.cbo_gen
        rs![U_Perm] = Me.cbo_perm
        
                        Set rs2 = db.OpenRecordset("jn_hist_users")
                        rs2.addnew
                            rs2![FK_User] = rs![PK_User]
                            rs2![T_Dat] = Date
                            rs2![V_Dat] = "User has been added"
                            rs2![Ed_By] = GetUsername()
                            rs2![F_Now] = Now
                            rs2.Update
                        rs2.Close
        rs.Update
    
    rs.Close
    db.Close
    Set rs = Nothing
    Set rs2 = Nothing
    Set db = Nothing

When I try to get the PK from the first recordset on rs2![FK_User] always comes back empty. Before I moved to SQL Server, when the table was stored on the Access backend, this code used to work just fine.
There is anybody that knows why this is happening?

Thanks you all!
 
You probably need to rs.Update before assigning the values from rs to rs2
 
biofaku said:
Hello there!
I am moving the backend of my access db to SQL Server and I found an issue that Im not able to solve. When I run this code:


Code: 
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim Form As Integer
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tbl_users")
    Dim fld As Field
    datTimeCheck = Now()
       
        rs.addnew
       
        rs![U_Nom] = Me.txt_name
        rs![U_Ape] = Me.txt_lastname
        rs![U_Ini] = Me.txt_ini
        rs![U_Sec] = Me.cbo_sect
        rs![U_Act] = True
        rs![U_Login] = Me.txt_user
        rs![U_Gen] = Me.cbo_gen
        rs![U_Perm] = Me.cbo_perm
       
                        Set rs2 = db.OpenRecordset("jn_hist_users")
                        rs2.addnew
                            rs2![FK_User] = rs![PK_User]
                            rs2![T_Dat] = Date
                            rs2![V_Dat] = "User has been added"
                            rs2![Ed_By] = GetUsername()
                            rs2![F_Now] = Now
                            rs2.Update
                        rs2.Close
        rs.Update
   
    rs.Close
    db.Close
    Set rs = Nothing
    Set rs2 = Nothing
    Set db = Nothing

When I try to get the PK from the first recordset on rs2![FK_User] always comes back empty. Before I moved to SQL Server, when the table was stored on the Access backend, this code used to work just fine.
There is anybody that knows why this is happening?

Thanks you all!
Click to expand...
SQL Server generates the new Identity value AFTER a record is created. Access generates the new Autonumber value BEFORE creating a record.

You need to retrieve the new Identity value for the new record's PK after saving it.
 
cheekybuddha said:
You probably need to rs.Update before assigning the values from rs to rs2
Click to expand...
Hi cheekybuddha!
By doing that I now get the value of PK from the first record of the table but not the value of the PK of the record that I was just adding.
Thanks for your idea :D
 
GPGeorge said:
SQL Server generates the new Identity value AFTER a record is created. Access generates the new Autonumber value BEFORE creating a record.

You need to retrieve the new Identity value for the new record's PK after saving it.
Click to expand...
Hi GPGeorge!
Yes, I supposed that something like that was happening, thanks for confirming it.

What we finally did was updating first and then moving to .lastmodified, I think is working but Im worried that sometimes might come with wrong values for PK
 
You need to get the PK of the Inserted record, you can do that reliably by using the following:
Read the following thread for some hints

Insert Records with VBA and get new autonum value - different variants and possible problems

Inspired by https://www.access-programmers.co.uk/forums/threads/how-to-avoid-the-domain-functions-in-ms-access.330010/ I am trying to compare insert variants in VBA in this thread. First of all: I only discuss the variants that can be used with Jet/ACE (e.g. executing procedures in an active...
www.access-programmers.co.uk

I think unless you are using triggers the @@Identiyy method is very reliable.
 
Gonna take a look at that
Thank you very much Minty!
 
biofaku said:
What we finally did was updating first and then moving to .lastmodified, I think is working but Im worried that sometimes might come with wrong values for PK
Click to expand...
Correct, if multiple users are updating the table at the same time, this is not reliable. Use @Identity. Scope_Identity is better but I have never been able to get it to work:( Perhaps @theDBguy will share some working code.
 
You mean the scope_Identity? It is T-SQL so I would have used a passthrough, I don't think it is valid in an Access query. I haven't tried to use it in quite a while so I can't remember what error I was getting. I'm pretty sure I used the same query I used for the @@Identity, just substituted the Scope_Identity and it "didn't work";)
 
Pat Hartman said:
Perhaps @theDBguy will share some working code.
Click to expand...
Sorry, I don't have access to a SQL Server instance right now. If using it through a pass-through query doesn't work, then my next thought would be to perhaps create a stored procedure to perform the insert and then return the PK value (or, maybe even convert the whole process of creating the parent and child records within the SP).

Just a thought...
 
This is working code from a stored procedure:

Code: 
Declare @AR_Key  int

INSERT INTO ...
VALUES(...)

Select @AR_Key = SCOPE_IDENTITY()
 
Thanks to all, I have been working with those functions but with no success so far.

Can anyone share a more detailed example of how should I implement it?

Thanks again
 
Show us your efforts, i.e. post the non-working code you have so far. That's the quickest route to getting usable feedback.
 
You may also need to add dbSeeChanges as a recordset option when working with SQL Server.

Set rs = db.OpenRecordset("tbl_users",dbSeeChanges)

or

Set rs = db.OpenRecordset("tbl_users",dbOpenDynaset, dbSeeChanges)
 
You can do without your home-grown history table altogether and use SQL Server's built-in feature.
I typically use a sproc to insert data and return the inserted record.
 
This works for me with linked SQL Server tables.

Code: 
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim Form As Integer
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tbl_users", dbOpenDynaset, dbSeeChanges)
    Dim fld As Field
    Dim idn as LongPtr 'integer if within range
    datTimeCheck = Now()
       
        rs.addnew
     
        rs![U_Nom] = Me.txt_name
        rs![U_Ape] = Me.txt_lastname
        rs![U_Ini] = Me.txt_ini
        rs![U_Sec] = Me.cbo_sect
        rs![U_Act] = True
        rs![U_Login] = Me.txt_user
        rs![U_Gen] = Me.cbo_gen
        rs![U_Perm] = Me.cbo_perm
        rs.Update
        idn = db.OpenRecordset("SELECT @@IDENTITY")(0)
   
       
                        Set rs2 = db.OpenRecordset("jn_hist_users", dbOpenDynaset, dbSeeChanges)
                        rs2.addnew
                            rs2![FK_User] = idn
                            rs2![T_Dat] = Date
                            rs2![V_Dat] = "User has been added"
                            rs2![Ed_By] = GetUsername()
                            rs2![F_Now] = Now
                            rs2.Update
                        rs2.Close
       
   
    rs.Close
    db.Close
    Set rs = Nothing
    Set rs2 = Nothing
    Set db = Nothing
 
Yay! thanks! @@identity worked just like that, I thought that it was gonna take something more because I was expecting to need to give it more instructions than that, but it was just that easy.

Thanks to all! :D
 
@@Identity will be fine as long as your query only inserts a single row with an Identity column. If there are triggers that cause additional inserts, then you will need to use the Scope_Identity to reliably get the ID you are looking for.
 
You must log in or register to reply here.

Similar threads

M
Inserting new records with loops and 3 record sets into SQL Table
Replies
0
Views
2,897
mazza
M

Users who are viewing this thread

Total: 2 (members: 0, guests: 2)
Back
Top Bottom