I have already posted this to the MS SQL section but thought I would give it a go here...
I am using Access 2002 as a front end to SQL server and I am trying to add a new record to several related tables.
The form is bound to my Publications table so when the user starts typing it gets assigned a new ID number by the autonumber mechanism. I read this value by using me!PublicationID and then I try and create related records in 2 other tables using VBA/ADO. PublicationID is the foreign key in these 2 tables.
The trouble is that the initial autonumber, that gets assigned, changes once the new record is saved to my Publications table...
User starts entering data. The last publication has an ID of 14 so this one gets an ID of 15. Once the record is saved the actual value stored in the ID field is higher! I assume this higher ID number takes into account all the records that I have added and then deleted after number 14.
Is there any way to close up these phantom autonumber gaps? I want the database to use the next available value but it insists on remembering all the numbers assigned to records tat never even got saved.
Alternatively would it be best to save the new record and then retrieve the number that really gets assigned and use this for the related foreign keys?
1. Saved the data in Advertisers. It has now been given a final ID number that won't change
2. I used a stored procedure (a query would have done the trick if I knew how to pass parameters to it or used an sql statement constructed on the client) that found the publication with the name that was just entered and then max(publicationID). Here it is:
ALTER Procedure procFindLastPublicationID
WHERE (PublicationName LIKE @MyParam + '%')
For some reason I only got matches when the WHERE clause was a 'LIKE' rather than an equals. Does anyone know why?
The VBA function to call this:
Function fnFindLastPublicationID(strPublicationName As String) As Integer
Dim rst1 As ADODB.Recordset
Dim cmd1 As ADODB.Command
Dim prm1 As ADODB.Parameter
Dim ary1 As Variant
If strPublicationName = "" Then
ReDim ary1(1, 1)
ary1(0, 0) = -1
' Initialize the stored procedure
Set cmd1 = New ADODB.Command
cmd1.ActiveConnection = CurrentProject.Connection
cmd1.CommandType = adCmdStoredProc
' Which stored procedure?
cmd1.CommandText = "procFindLastPublicationID"
' Set the parameters
Set prm1=cmd1.CreateParameter("@MyParam", adVarChar, _
' Pass the criterion
prm1.Value = strPublicationName
Set rst1 = cmd1.Execute
' Return the result
ary1 = rst1.GetRows
' Close the recordset
Set rst1 = Nothing
fnFindLastPublicationID = ary1(0, 0)
(The proc that calls this converts NULLs to empty strings before passing PublicationName)
So this function takes a Publication name and returns the most recent record that matches it...