insert query with sequential primary key

by

Here are some notes that may help you towards your goal, however life would be a lot easier and a lot safer with autonumbers. This is VBA as you mention MS Access.

Function NextTranNumber(ByRef FirstTran As Long, _
         ByRef LastTran As Long, Optional BlockSize = 1)
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim lngResult As Long
Dim strCon As String

    lngResult = 0  'assume fail

    strCon = TestCon ''Connection to back-end
    cn.Open strCon

    rs.CursorType = adOpenKeyset
    rs.LockType = adLockPessimistic
    rs.CursorLocation = adUseServer

    ''Where BEInfo is a single line table that holds a transaction seed
    strSQL = "SELECT ASeqNumber FROM BEInfo"

    rs.Open strSQL, cn, , , adCmdText

    'Note this is ADO, so no rs.Edit
    FirstTran = rs!ASeqNumber + 1
    rs!ASeqNumber = rs!ASeqNumber + BlockSize
    rs.Update

    LastTran = rs!ASeqNumber
    rs.Close
    Set rs = Nothing
End Function

Sub TransactionProcessing()
Dim FirstTran As Long
Dim LastTran As Long
Dim db As Database
Dim sSQL As String
Dim Block As Long
Dim rs As DAO.Recordset

    Set db = CurrentDb

    'Existing temporary table
    sSQL = "DELETE FROM FETempTrans"
    db.Execute sSQL, dbFailOnError
    'The records to be added to the main table
    sSQL = "INSERT INTO FETempTrans ( ID, AText ) SELECT 0 AS ID, AText FROM Table1"
    db.Execute sSQL, dbFailOnError

    Block = db.RecordsAffected

    'Reserve a transaction block based on the temp table count
    NextTranNumber FirstTran, LastTran, Block

    Set rs = db.OpenRecordset("FETempTrans")

    Do While Not rs.EOF
        rs.Edit
        rs!ID = FirstTran
        rs.Update
        FirstTran = FirstTran + 1
        rs.MoveNext
    Loop

    If FirstTran - 1 = LastTran Then
        'compare the temp set to the main table
        'if it passes, update the main table
    Else
        'fail
    End If
End Sub

More Related Contents:

  1. Combine values from related rows into a single concatenated string value
  2. How do you use version control with Access development?
  3. Microsoft Access condense multiple lines in a table
  4. Bang Notation and Dot Notation in VBA and MS-Access
  5. “General error Unable to open registry key Temporary (volatile) …” from Access ODBC
  6. Is there a simple way of populating dropdown in this Access Database schema?
  7. How do I make a stored procedure in MS Access?
  8. How can I get table names from an MS Access Database?
  9. Is there any way to create multiple insert statements in a ms-access query?
  10. Running Microsoft Access as a Scheduled Task
  11. How to diagnose MS access crashes
  12. “Operation must use an updateable query” error in MS Access
  13. VBScript & Access MDB – 800A0E7A – “Provider cannot be found. It may not be properly installed”
  14. Run-time error ‘3061’. Too few parameters. Expected 1. (Access 2007)
  15. MS Access trigger?
  16. MS Access LIMIT X, Y
  17. Prevent enabling of shift bypass – Access 2010
  18. Access query producing results like ROW_NUMBER() in T-SQL
  19. Is MS Access (JET) suitable for multiuser access?
  20. Save password for ODBC connection to MS SQL server from MS Access 2007
  21. How to write Case Sensitive Query for MS Access?
  22. Setting up an MS-Access DB for multi-user access
  23. Access VBA: Find max number in column and add 1
  24. MS Access prepared statements
  25. ConcatRelated function in a query
  26. Access 2007 – INSERT and instant SELECT doesn’t retrieve the inserted data
  27. Create sequential ID value based on the year that a record is added
  28. How to use cross join in access?
  29. Get Name of Current VBA Function
  30. How to extract the schema of an Access (.mdb) database?

Leave a Comment