New Record. Grab new primary key. 1

[GKChesterton]

I like to use .Addnew with a DAO.recordset. As the new record is added, my habit is to grab the newly-generated primary key for use in the next steps, which I can do before .Update.

Now I'm switching from DAO to ADO, and from MS Access back-end to MySQL 5 back-end. And I don't understand how to grab the primary key. Also, with multiple users, I need to make a clean fast grab. I'm using InnoDB tables (I need foreign key constraints).

So far my new VBA code looks like this:
[tt]
Dim MyDatabase As ADODB.Connection
Dim rsQuote As New ADODB.Recordset

Set MyDatabase = New ADODB.Connection
MyDatabase .Open "File Name=C:\MyDatabase.udl"

rsQuote.Open "pBOM", MyDatabase, _
adOpenDynamic, adLockOptimistic
rsQuote.AddNew
rsQuote!field1 = var1
rsQuote!field2 = var2
rsQuote!field3 = var3
- etc., I like not using a flat
SQL query/command for all this.
rsQuote.Update
MyKey = rsQuote!PrimaryKey
rsQuote.Close[/tt]

All works well, but MyKey doesn't get a value.

Should I change my ways and switch to .Execute and SQL statements? Or is there a way to adapt the code above to get the new AUTO_INCREMENT value?

[purple]If we knew what it was we were doing, it would not be called
research [blue]database[white].[/white]development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[PWise]

Dim MyDatabase As ADODB.Connection
    Dim rsQuote As New ADODB.Recordset
    [COLOR=red]Dim RsIdent as New ADODB.Recordset[/color]
    Set MyDatabase = New ADODB.Connection
    MyDatabase .Open "File Name=C:\MyDatabase.udl"

    rsQuote.Open "pBOM", MyDatabase, _
                    adOpenDynamic, adLockOptimistic
    rsQuote.AddNew
        rsQuote!field1 = var1
        rsQuote!field2 = var2
        rsQuote!field3 = var3
            - etc., I like not using a flat 
              SQL query/command for all this.
    rsQuote.Update
    [COLOR=red]RsIdent .Open "Select SCOPE_IDENTITY( ) as PrimaryKey", MyDatabase, _
                    adOpenDynamic, adLockOptimistic[/color]
    MyKey = [i][COLOR=green]RsIdent[/color][/i]!PrimaryKey
    rsQuote.Close

 

[GKChesterton]

Thank you. This looks like the answer, but I'm having trouble making it work.

First, SCOPE_IDENTITY( ) possibly should be changed to @@IDENTITY. Google survey indicates the former is much preferred where available, but it is also limited to SQL Server.

Second, the result of the new code seems to be that I get the value for the Key from before the update. I tried fiddling with the order of the lines a bit, but no luck.

I found new information in the MySQL Manual (including a reference to Access in particular) but wasn't successful applying it.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database[white].[/white]development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[PWise]

Try using .Execute and see if it returns the the right ident

 

[GKChesterton]

Thanks, that helped. The problem was elsewhere -- I have a test back-end and I was accidentally making two different connections.

I tweaked your .Open command to go read-only ... Thanks so much.

rsNew.Open "Select @@IDENTITY as PrimaryKey", MyDatabase, _
                    adOpenStatic

[purple]If we knew what it was we were doing, it would not be called
research [blue]database[white].[/white]development[/blue], would it? [tab]-- Albert Einstein[/purple]​