---

MS Access 2003: Create a pass-through query to retrieve the nextval from an Oracle sequence with VBA code

This MSAccess tutorial explains how to create a pass-through query to retrieve the NextVal from an Oracle sequence using VBA code in Access 2003 (with screenshots and step-by-step instructions).

Question: In Microsoft Access 2003/XP/2000/97, I would like to create a pass-through query so that I can retrieve the NextVal from an Oracle sequence. I would like to then assign the NextVal to a field on an Access form.

How can I do this?

Answer: To assign the NextVal (from an Oracle sequence) to a field on a form, you'll have to create a pass-through query using VBA code.

The first thing you need to do is create an ODBC connection to your Oracle database using the {Microsoft ODBC for Oracle} driver.

To do this, go to the "Data Sources ODBC" icon under the Control Panel and create a new Data Source using the {Microsoft ODBC for Oracle} driver.

Set up your ODBC connection.

In this example, we've setup the Data Source with a name of AAAA, with a user name of BBBB, and an Oracle server called CCCC. You'll need to configure the ODBC connection with your own settings.

Open your Access database, click on the Modules tab and create a new Module. Paste in the following code:

Function AssignNextVal() As Long

   Dim db As Database
   Dim LPassThrough As QueryDef
   Dim Lrs As dao.Recordset
   Dim LSQL As String

   On Error GoTo Err_Execute

   Set db = CurrentDb()

   'Create a temporary passthrough query to retrieve the NextVal from an Oracle sequence
   Set LPassThrough = db.CreateQueryDef("qryTemp")

   'Use {Microsoft ODBC for Oracle} ODBC connection
   LPassThrough.Connect = "ODBC;DSN=AAAA;UID=BBBB;PWD=DDDD;SERVER=CCCC"
   LPassThrough.SQL = "Select member_id_seq.nextval as NV From Dual"
   LPassThrough.ReturnsRecords = True

   Set Lrs = LPassThrough.OpenRecordset(dbOpenSnapshot)

   'Retrieve NextVal from Oracle sequence
   If Lrs.EOF = False Then
      AssignNextVal = Lrs("NV")
   Else
      AssignNextVal = 0
   End If

   'Remove query definition when done
   CurrentDb.QueryDefs.Delete "qryTemp"

   Exit Function

Err_Execute:
   'Remove query definition when done
   CurrentDb.QueryDefs.Delete "qryTemp"

   'Return 0 if an error occurred
   AssignNextVal = 0

End Function

Now you've created a function called AssignNextVal that will return the NextVal from an Oracle sequence.

Please note that you'll need to customize the following line of code:

LPassThrough.Connect = "ODBC;DSN=AAAA;UID=BBBB;PWD=DDDD;SERVER=CCCC"

So that:

AAAA is the name of the ODBC Data Source that you set up.
BBBB is the user name that you will use to log into Oracle.
CCCC is the name of your Oracle server.
DDDD is the password that you will use to log into Oracle.

Now, If you want to assign the NextVal to a field on an Access form, you can just call the AssignNextVal function on the Form's Before Insert event.

For example, if you wanted to assign the NextVal to a field called Member_ID, you could insert the following code on the Form's Before Insert event.

Private Sub Form_BeforeInsert(Cancel As Integer)

   Member_ID = AssignNextVal()

End Sub

If after trying this example, you receive a "not defined" error on the "Dim db as Database" declaration, you will need to follow some additional instructions.