Solved

Get record written data

Posted on 2016-07-18
7
41 Views
Last Modified: 2016-07-19
I currently use the following VBA code to get the MAX record just after commit:
<BEGIN VBA>    Dim strSQLLot As String
    Dim strSQLFailure As String
    Dim intFailed As Integer
    Dim intCounter As Integer
    Dim dbConn As ADODB.Connection
    Dim recSet As ADODB.Recordset
    Dim intNextFailureId As Integer
    Dim intArray() As Integer
    Dim dbs As DAO.Database
    Dim rsSQL As DAO.Recordset
    Dim intMaxLotId As Integer
    Dim intNewLotID As Integer
'Open the database
        Set dbConn = CurrentProject.Connection
       
        'Write the form's data to the SQL table tblInitialData
        DoCmd.RunCommand acCmdSaveRecord
                               
        'determine MAX Lot ID number
        DoCmd.RunMacro "mcrRunqryMaketblMaxLotID"
        intMaxLotId = DLookup("MaxOfTestLotID", "tblMaxLotID")
       
        'Prompt user with MAX Lot ID Number.
        MsgBox "Lot number " & intMaxLotId & " has been created.", , "ATE DATABASE"
                       
        DoCmd.Close
                       
        'open switchboard and close initial form
        DoCmd.OpenForm "Switchboard"
<END VBA>

I previously tried to get a SELECT statement to run but had no luck.  I would like to use either SCOPE_IDENTITY() or @@IDENTITY but cannot determine the correct VBA to make that run.  Indeed, will it even run in scope based on using the DoCmd.RunCommand acCmdSaveRecord statement instead of an INSERT INTO statement?  I have tried DoCmd.OpenQuery, CurrentDb.Execute, and others that apparently do not allow SELECT statements.

TIA,
Tim
0
Comment
Question by:gibneyt
7 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41717734
check "Retrieving Microsoft Access Autonumber Values" from https://msdn.microsoft.com/en-us/library/ks9f57t0.aspx?f=255&MSPPError=-2147217396
0
 
LVL 26

Expert Comment

by:Nick67
ID: 41717792
What you are attempting is a little trickier than you might think.
Dlookup() is a performance pig.
None of the domain lookup functions should EVER be used.
This will find the 'largest' value depending on datatype and sort order
Dim rs As dao.Recordset
Set rs = db.OpenRecordset("SELECT TOP 1 TheFieldOfInterest FROM TheTableOfInterest  ORDER BY  TheFieldOfInterest DESC;", dbOpenDynaset, dbSeeChanges)

TheMaxValue = rs!TheFieldOfInterest

No matter how you do it though, you have to be aware you are in a multi-user setup.
No matter how fast you think you may be, what gets retrieved may be the value from the record just committed -- or from someone else's new addition.

@@IDENTITY is at the SQL Server end of things.
You can get that back to Access -- but not if you are letting the users create the record via a bound form as you appear to be doing (DoCmd.RunCommand acCmdSaveRecord)

If you create the record with DAO, pulling back the identity is fairly simple.

   With rs
        .AddNew
        !MyField = MyValue
        .Update
        .Bookmark = .LastModified
        lngJobID = ![JobID]
    End With

Where my identity field is JobID and a Long (SQL Server bit) value.

But note that this value has NO guarantee of actually being the last record in play.
If you use the value to build a value that's unique (like work order or invoice numbers)  you need to build a check in to ensure that an attempted value is ACTUALLY valid when you go to use it.
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 41719130
I would suggest that use MS Access only for single user run and SQL Server for multiple user run, and then using MAX(JobID) as already discuss here, would work in Access, and in SQL Server, you can use SCOPE_IDENTITY() to retrieve the last value.

Hope that helps.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 41719461
Since you are running the code in a bound form, you don't need to use either the DLooku() or @@Identity query.  Simply reference the identity column in the form's RecordSource.

intMaxLotId = Me.LotID

If you run an append query, then you can't access the identity column and that is when you would need to use the @@Identity query to retrieve it.  So after running the append query or using DAO or ADO to inset a record use:
            Set rsScope = db.OpenRecordset("SELECT @@IDENTITY as NewID")
                       NewProvProcID = rsScope!NewID

Open in new window


As long as you don't have any triggers that will cause inserts into other tables, this will give you the last identity column inserted in this thread.

@Valliappan AN - Access (more specifically Jet and ACE) is not a single user application.  It is multi-user out of the box.

@Nick - when the tables are bound to SQL server, you cannot reference the Identity column this way.  It works with Jet/ACE due to the close connection Access has with them but Access' connection with SQL Server et al is asynchronous and so the insert has to be sent to the server (ie saved) before you can retrieve the new identity value.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 41719653
@PatHartman
I am confused
"you cannot reference the Identity column this way"
Perhaps I did not phrase things as clearly as I would have liked.
Dim rs As dao.Recordset
 Set rs = db.OpenRecordset("SELECT TOP 1 TheFieldOfInterest FROM TheTableOfInterest  ORDER BY  TheFieldOfInterest DESC;", dbOpenDynaset, dbSeeChanges)
 TheMaxValue = rs!TheFieldOfInterest

Open in new window


This will get the largest committed value of [TheTableOfInterest].[TheFieldOfInterest]
When you have an Access backend, Access will put a hold on an autonumber the minute you begin to make a new record dirty, and you can retrieve that.  SQL Server does not behave that way.  The identity value isn't set until the record is committed.
This code creates a new record, commits it and retrieves the identity value of that.
    With rs
         .AddNew
         !MyField = MyValue
         .Update
         .Bookmark = .LastModified
         lngJobID = ![JobID]
     End With

Open in new window


and works swimmingly with a SQL Server backend.
But absolutely, if a SQL Server backend is in play, you cannot retrieve the identity value of a 'dirty and new' record of a form -- which is something that is possible with an Access backend.  Only committing to save the record causes that identity value to be set in a SQL Server backend.

Is that what you meant?
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41719657
Sorry, I read the code wrong.  It looked like the reference was inside the .addnew rather than outside it.
0
 

Author Closing Comment

by:gibneyt
ID: 41719769
@PatHartman,

Nice!  Accessing the form's RecordSource in this instance does appear to be the shortest and sweetest means to an end.

Many thanks.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Resize text 4 15
TT Status Chang 3 30
SQL: export into csv/sqlcmd method and field config 3 15
SQL Date Retrival 7 26
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now