GUID

  • Thread starter Thread starter Branden Johnson
  • Start date Start date
B

Branden Johnson

Using Microsoft Access 2002 (10.2627.2625)
Windows XP Pro SP1

I am writing a VB6 app to insert records into my Access database, based on
one of my forms. Upon clicking on the "Send" button in my app I have the
code generating a record in tblMRC and multiple records in tblPRODUCT. I
have a third table tblMRCPRODUCTLINK that I want to write a "link" record
for the MRC and the PRODUCT(s) that I am writing.

My problem is this: I do not have a good way of determining the PRODUCTID
(Primary Key) for the records I am writing. Here is an example of the INSERT
statement I am passing for a single PRODUCT INSERT:
sqlProduct = "" & _
"INSERT INTO " & _
"PRODUCT(NAME, DESCRIPTION, SERIALNUMBER, PARTNUMBER) " & _
"VALUES " & _
"(" & _
txtName(0).Text & ", " & _
txtDescription(0).Text & ", " & _
txtSerialNum(0).Text & ", " & _
txtProdNum(0).Text & _
")"

This statement inserts a record into the PRODUCT table and auto generates
the PRODUCTID (AutoNumber - Replication ID). Does anyone know if I can get
the next GUID, to be used by Access? This way I could specify the PRODUCTID
in my INSERT statement and problem solved.
 
Before the INSERT execution: Not AFAIK.

Get the number allocated just after the Insert: Yes.

For single Record insertion and ID value is needed, I tend to create an
(empty) DAO.Recordset, use the AddNew Method to add the Record and then
retrieve the ID after Update Method.

Check Access VB Help on the AddNew / Update methods of DAO Recordset.
 
Branden:

Similar to Van's suggestion, is to use the SELECT @@Identity query to find
the ProductID value for the last inserted row. The following KB article
gives more information on its usage.

http://support.microsoft.com/default.aspx?scid=kb;en-us;232144

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Using Microsoft Access 2002 (10.2627.2625)
Windows XP Pro SP1

I am writing a VB6 app to insert records into my Access database, based on
one of my forms. Upon clicking on the "Send" button in my app I have the
code generating a record in tblMRC and multiple records in tblPRODUCT. I
have a third table tblMRCPRODUCTLINK that I want to write a "link" record
for the MRC and the PRODUCT(s) that I am writing.

My problem is this: I do not have a good way of determining the PRODUCTID
(Primary Key) for the records I am writing. Here is an example of the INSERT
statement I am passing for a single PRODUCT INSERT:
sqlProduct = "" & _
"INSERT INTO " & _
"PRODUCT(NAME, DESCRIPTION, SERIALNUMBER, PARTNUMBER) " & _
"VALUES " & _
"(" & _
txtName(0).Text & ", " & _
txtDescription(0).Text & ", " & _
txtSerialNum(0).Text & ", " & _
txtProdNum(0).Text & _
")"

This statement inserts a record into the PRODUCT table and auto generates
the PRODUCTID (AutoNumber - Replication ID). Does anyone know if I can get
the next GUID, to be used by Access? This way I could specify the PRODUCTID
in my INSERT statement and problem solved.
 
Back
Top