Discussion Locked

VB6 and MS Access

0 helpful
7 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
How do u get the value of an autoid key when u insert a new record and u want to pass the id as a foreign key to another table using Ms Access and VB6

7 replies

by andrew

Here's a way using ADO recordsets:

Dim rs As New ADODB.Recordset
rs.Open "SELECT * FROM CATEGORIES","Provider=MSDASQL;DSN=NWind"_
, adOpenKeyset, adLockOptimistic

rs.AddNew
rs!CategoryName = "RSDemo"
rs!Description = "RS demonstration"
rs.Update

rs.MoveLast
MsgBox "The AutoNumber field equals " & rs!CategoryId, vbInformation

by andrew

I use @@IDENTITY in SQL Server, but couldn't find the equivalent in Access.

Anyone?

by sankulpune

Hello User Deleted

Here is code to copy AutoID value to a foreign key.

Sub SubCopyAutoID2ForeignID()
'
' Creates a new record and copies its
' AutoID as foreign key to another table
'
' Code by SanganakSakha 20/9/01
' No comprehensive errorchecking done
'
Dim rstTbl1 As DAO.Recordset
Dim rstTbl2 As DAO.Recordset

Set rstTbl1 = CurrentDb.OpenRecordset("Table1")
Set rstTbl2 = CurrentDb.OpenRecordset("Table2")

rstTbl1.AddNew
rstTbl2.AddNew

rstTbl2.Fields("A") = rstTbl1.Fields("A")

rstTbl1.Update

' Take care to assign value to primary key
' of table 2 if it has one, before update

rstTbl2.Update

Set rstTbl1 = Nothing
Set rstTbl2 = Nothing

End Sub

I have successfully tried this code and would be glad to help you implement this solution.

With regards,

SanganakSakha,
Simple And Effective Solutions To Small Problems, No QuickFixes.

This discussion is closed to new replies.