How to use Access autonumber column with OLE DB templates in Visual C++

SUMMARY

When using the OLE DB Templates with an Access database, it is often necessary to add records to a table that contains an autonumber column.

Enabling the autonumber column to increment properly with the Microsoft OLE DB Provider for Jet 3.51 requires some care. To accomplish the task, two accessors are required:

• one, for inserting new records, that contains the autonumber column so it can be retrieved and used in the application.
• one that does not contain the autonumber column.

How to use Multiple Accessors with the OLE DB Templates is documented in the MSDN in the FAQ for the Consumer Templates inside the Visual C++ documentation.

The following sample code displays how to define multiple accessors:

   BEGIN_ACCESSOR_MAP(CProductsAccessor, 2)   // Pass number of accessors

     BEGIN_ACCESSOR(0, true)         // true = an auto accessor
       COLUMN_ENTRY(2, m_ProductName)
       COLUMN_ENTRY(3, m_UnitPrice)
       COLUMN_ENTRY(4, m_UnitsInStock)
       COLUMN_ENTRY(5, m_UnitsOnOrder)
       COLUMN_ENTRY(6, m_ReorderLevel)
     END_ACCESSOR()
     BEGIN_ACCESSOR(1, false)       // false = not an autoaccessor
       COLUMN_ENTRY(1, m_ProductID)
     END_ACCESSOR()

   END_ACCESSOR_MAP()
				

Code to add a new record to the table would resemble the following:

   CProducts rs;

   rs.Open();
   rs.ClearRecord();
   strcpy(rs.ProductName,"New product name");
   rs.UnitPrice = 10;
   rs.UnitsInStock = 0;
   rs.UnitsOnOrder = 100;
   rs.ReorderLevel = 25;
   rs.Insert();
				

This code inserts the new record using the auto accessor; the ProductId column, which is defined as an autonumber column, will be automatically updated by Jet. If we had included the autonumber column in the auto accessor, the OLE DB Provider for Jet 3.51 would have used whatever value we had assigned to m_ProductID when it updated the column and not used an autoincrement value.

   rs.MoveNext(); //Move to next record retrieving values for auto accessor
   rs.GetData(1); //Retrieve values for accessor 1
				

   rs.UnitsInStock--;  // Decrement units in stock
   rs.SetData(0);      // Update columns in accessor 0