Visual Basic Q&A

Symptoms
The Access AutoNumber field is always available when a server-side cursor is used. When a client-side cursor is used, the AutoNumber field is only returned immediately when an Access 2000 database is used with OLE DB Provider for Jet 4.0 driver and with the Jet 4.0 ODBC driver.
Resolution
The following table shows when the AutoNumber field is immediately available without a requery.
Client-side cursor for Access 97 and 2000 with different drivers:

Collapse this tableExpand this table
DriverAccess 97Access 2000Office XPJet OLE DB 3.51NOUnrecognized Database FormatUnrecognized Database FormatJet OLE DB 4.0Returns 0YESYESJet ODBC 3.51NONONOJet ODBC 4.0Returns 0YESYES
The following code sample demonstrates the results shown in the above table: Open a standard EXE project in Visual Basic. Form1 is created by default.Under Project References, select Microsoft ActiveX Data Objects .Place two CommandButtons on the form.Paste the following code in the form code window:

Dim cn As ADODB.ConnectionDim rs As ADODB.RecordsetPrivate Sub Command1_Click()rs.Open “select * from ORDERS”, cn, adOpenKeyset, adLockOptimisticEnd SubPrivate Sub Command2_Click()rs.AddNewrs!CustomerID = “ALFKI”rs!EmployeeID = 1rs.UpdateBatchDebug.Print rs!OrderID & Chr(9) & rs!CustomerID & Chr(9); rs!EmployeeIDEnd SubDim sconnect As StringPrivate Sub Form_Load() Dim sconnect As StringSet cn = New ADODB.Connection Set rs = New ADODB.Recordset’Change the paths to the mdb’s in the following statements for your machine; ‘Uncomment ONE of the statements to set sconnect to a valid connection string. ‘ Using JETOLEDB drivers’Test Office 97 using JETOLEDB’sconnect = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb”’sconnect = “Provider=Microsoft.Jet.OLEDB.3.51;Data Source=D:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb”‘ Test Office 2000 using JETOLEDB’sconnect = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Program Files\Office2000\Office\Samples\northwind.mdb”’sconnect = “Provider=Microsoft.Jet.OLEDB.3.51;Data Source=D:\Program Files\Office2000\Office\Samples\northwind.mdb”‘Test Office XP using JETOLEDB’sconnect = “Provider=Microsoft.Jet.OLEDB.3.51;Data Source=D:\Program Files\Microsoft Office\Office10\Samples\northwind.mdb”’sconnect = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Program Files\Microsoft Office\Office10\Samples\northwind.mdb”‘ Using ODBC drivers’Test Office 97 using ODBC drivers’sconnect = “Driver={Microsoft Access Driver (*.mdb)};” & _”Dbq=nwind.mdb;” & _”DefaultDir=D:\Program Files\Microsoft Visual Studio\VB98\;” & _”Uid=Admin;Pwd=;” ”Test Office 2000 / 9 using ODBC drivers’sconnect = “Driver={Microsoft Access Driver (*.mdb)};” & _”Dbq=northwind.mdb;” & _”DefaultDir=D:\Program Files\Microsoft Office\Office\Samples;” & _”Uid=Admin;Pwd=;”‘Test Office XP / 10 using ODBC drivers’sconnect = “Driver={Microsoft Access Driver (*.mdb)};” & _”Dbq=northwind.mdb;” & _”DefaultDir=D:\Program Files\Microsoft Office\Office10\Samples;” & _”Uid=Admin;Pwd=;”cn.CursorLocation = adUseClient’cn.CursorLocation = adUseServercn.Open sconnectEnd Sub

Symptoms
The Jet OLE DB version 4.0 provider supports the SELECT @@Identity query that allows you to retrieve the value of the auto-increment field generated on your connection. Auto-increment values used on other connections to your database do not affect the results of this specialized query. This feature works with Jet 4.0 databases but not with older formats.
Resolution
The following code demonstrates using the SELECT @@Identity to retrieve the value of the newly inserted auto-increment field. The code snippet also includes code to create the table for the query.

Dim cnDatabase As ADODB.ConnectionDim rsNewAutoIncrement As ADODB.RecordsetDim strConn As StringDim strSQL As StringDim strPathToMDB As StringstrPathToMDB = “C:\NewJet4.MDB”strConn = “Provider=Microsoft.Jet.OLEDB.4.0;” & _”Data Source=” & strPathToMDB & “;”Set cnDatabase = New ADODB.ConnectioncnDatabase.Open strConnstrSQL = “CREATE TABLE AutoIncrementTest ” & _”(ID int identity, Description varchar(40), ” & _”CONSTRAINT AutoIncrementTest_PrimaryKey PRIMARY KEY (ID))”cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecordsstrSQL = “INSERT INTO AutoIncrementTest ” & _”(Description) VALUES (‘AutoIncrement Test’)”cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecordsstrSQL = “SELECT @@Identity”Set rsNewAutoIncrement = New ADODB.RecordsetrsNewAutoIncrement.Open strSQL, cnDatabase, adOpenForwardOnly, _adLockReadOnly, adCmdTextMsgBox “New Auto-increment value is: ” & rsNewAutoIncrement(0).ValuersNewAutoIncrement.CloseSet rsNewAutoIncrement = NothingstrSQL = “DROP TABLE AutoIncrementTest”cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecordscnDatabase.CloseSet cnDatabase = Nothing Thanks to this newly added functionality, you can see the newly added auto-increment values in your client-side ActiveX Data Objects (ADO) recordsets in ADO 2.1 and later. When you submit the new row to the Jet provider by calling Update or UpdateBatch (depending on your choice of LockType), the ADO cursor engine generates an INSERT INTO query to create the new row in the table. If the recordset contains an auto-increment field, ADO will also generate a SELECT @@Identity query to retrieve the value generated for that auto-increment field. The following code demonstrates this feature:

Dim cnDatabase As ADODB.ConnectionDim rsNewAutoIncrement As ADODB.RecordsetDim strConn As StringDim strSQL As StringDim strPathToMDB As StringstrPathToMDB = “C:\NewJet4.MDB”strConn = “Provider=Microsoft.Jet.OLEDB.4.0;” & _”Data Source=” & strPathToMDB & “;”Set cnDatabase = New ADODB.ConnectioncnDatabase.Open strConnstrSQL = “CREATE TABLE AutoIncrementTest ” & _”(ID int identity, Description varchar(40), ” & _”CONSTRAINT AutoIncrementTest_PrimaryKey PRIMARY KEY (ID))”cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecordsstrSQL = “SELECT ID, Description FROM AutoIncrementTest”Set rsNewAutoIncrement = New ADODB.RecordsetrsNewAutoIncrement.CursorLocation = adUseClientrsNewAutoIncrement.Open strSQL, cnDatabase, adOpenStatic, _adLockOptimistic, adCmdTextrsNewAutoIncrement.AddNewrsNewAutoIncrement(“Description”).Value = “AutoIncrement Test”rsNewAutoIncrement.UpdateMsgBox “New Auto-increment value is: ” & rsNewAutoIncrement(0).ValuersNewAutoIncrement.CloseSet rsNewAutoIncrement = NothingstrSQL = “DROP TABLE AutoIncrementTest”cnDatabase.Execute strSQL, , adCmdText + adExecuteNoRecordscnDatabase.CloseSet cnDatabase = Nothing You can create a new Jet 4.0 database using Microsoft Access 2000 or using the ADOX library that is included with MDAC 2.1. To use this library in your Visual Basic project, create a reference to Microsoft ADO Ext. 2.1 for DDL and Security. You can then use code like the following to create a new Jet 4.0 database:

Dim strPathToMDB As StringDim catNewDatabase As ADOX.CatalogstrPathToMDB = “C:\NewJet4.MDB”If Dir(strPathToMDB) <> “” ThenKill strPathToMDBEnd IfstrConn = “Provider=Microsoft.Jet.OLEDB.4.0;” & _”Data Source=” & strPathToMDB & “;”Set catNewDatabase = New ADOX.CatalogcatNewDatabase.Create strConnSet catNewDatabase = Nothing To determine the format of your Microsoft Access database, check the dynamic “Jet OLEDB:Engine Type” property in the Connection object’s Properties collection. The property will return a value of 5 for Jet 4.x databases. The following code snippet demonstrates using the property:

Dim cnDatabase As ADODB.ConnectionDim strConn As StringDim strPathToMDB As StringstrPathToMDB = “C:\NewJet4.MDB”strConn = “Provider=Microsoft.Jet.OLEDB.4.0;” & _”Data Source=” & strPathToMDB & “;”Set cnDatabase = New ADODB.ConnectioncnDatabase.Open strConnIf cnDatabase.Properties(“Jet OLEDB:Engine Type”).Value = 5 ThenMsgBox “Jet 4.0 database”ElseMsgBox “Not a Jet 4.0 database”End IfcnDatabase.CloseSet cnDatabase = Nothing

Symptoms
This article describes how to use ActiveX Data Objects Extensibility (ADOX) to create an OLE Object field in a Microsoft Access Database (.mdb file). You must use the adLongVarBinary constant to create the field. You do not have to specify a field size in the field definition.
Resolution
Step-by-Step ExampleCreate a new Standard EXE project in Visual Basic. Form1 is created by default.From the Project menu, click References. From the list of available components, click Microsoft ADO Ext. 2.1 for DDL and Security.Add a CommandButton control to Form1.Paste the following code onto the Declarations section of Form1:

Private Sub Command2_Click()Set cat = New ADOX.CatalogSet tbl = New ADOX.Tablecat.ActiveConnection = _”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\nwind2.mdb;”tbl.Name = “OleObjTable”tbl.Columns.Append “Column1″, adIntegertbl.Columns.Append “Column2″, adIntegertbl.Columns.Append “Column3″, adVarWChar, 50′ Please note adLongVarBinary = 205tbl.Columns.Append “MyOleObject”, adLongVarBinarycat.Tables.Append tblEnd Sub Modify the cat.ActiveConnection assignment to point to a valid Microsoft Access Database file.Run the project, and click Command1. Notice that a table named OleObjTable is created in the database. When you view the table in Design Mode, the Column3 field definition is displayed as OLE Object.

Symptoms
ADOX is an extension to ActiveX Data Objects that allows the manipulation of the database schema. This article illustrates how to use ADOX to create a table and add a Primary Key.
Resolution
NOTE: Not all OLE DB providers support the interfaces required to support ADOX methods. With those providers, you have to use Data Definition Queries or another object model to manipulate the database schema.
The first procedure in the example below creates a new table in an existing Microsoft Access database, creates a new field in that table, then creates a primary key index. When adding a single-field primary key, you do not need to use the ADOX Key object.
The second procedure utilizes the ADOX Key object to add a multiple field key to a table.
Steps to Create the Sample Application In Microsoft Visual Basic 5.0 or 6.0, create a new Standard EXE project. Form1 is created by default. On the Project menu, select References to add the following type libraries:
Microsoft ActiveX Data Objects 2.1 Library
Microsoft ADO Ext. 2.1 for DDL and Security Add two Command buttons (Command1 and Command2) and the following code to the Form1:

Option ExplicitPrivate Sub Command1_Click()” This code adds a single-field Primary key’Dim Cn As ADODB.Connection, Cat As ADOX.Catalog, objTable As ADOX.TableSet Cn = New ADODB.ConnectionSet Cat = New ADOX.CatalogSet objTable = New ADOX.Table’Open the connectionCn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb”‘Open the CatalogSet Cat.ActiveConnection = Cn’Create the tableobjTable.Name = “Test_Table”‘Create and Append a new field to the “Test_Table” Columns CollectionobjTable.Columns.Append “PrimaryKey_Field”, adInteger’Create and Append a new key. Note that we are merely passing’the “PimaryKey_Field” column as the source of the primary key. This’new Key will be Appended to the Keys Collection of “Test_Table”objTable.Keys.Append “PrimaryKey”, adKeyPrimary, “PrimaryKey_Field”‘Append the newly created table to the Tables CollectionCat.Tables.Append objTable’ clean up objectsSet objKey = NothingSet objTable = NothingSet Cat = NothingCn.CloseSet Cn = NothingEnd SubPrivate Sub Command2_Click()” This code adds a multi-field Primary Key’Dim Cn As ADODB.Connection, Cat As ADOX.CatalogDim objTable As ADOX.Table, objKey As ADOX.KeySet Cn = New ADODB.ConnectionSet Cat = New ADOX.CatalogSet objTable = New ADOX.TableSet objKey = New ADOX.KeyCn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb”Set Cat.ActiveConnection = CnobjTable.Name = “Test_Table2″objTable.Columns.Append “PrimaryKey_Field1″, adIntegerobjTable.Columns.Append “PrimaryKey_Field2″, adIntegerobjKey.Name = “PrimaryKey”objKey.Type = adKeyPrimaryobjKey.Columns.Append “PrimaryKey_Field1″objKey.Columns.Append “PrimaryKey_Field2″objTable.Keys.Append objKeyCat.Tables.Append objTable’ clean up objectsSet objKey = NothingSet objTable = NothingSet Cat = NothingCn.CloseSet Cn = NothingEnd Sub NOTE: You might have to adjust the connect string to point to a valid Jet database. Run the application and click the Command buttons. You can check the table definitions for Test_Table and TestTable2 in Microsoft Access 97, Microsoft Access 2000, or the Visual Basic Visual Data Manager add-in.

Symptoms
If you use the Visual Basic Data Form Wizard to open an Access 2000 database you may get the following error:

“Unrecognized Database Format ‘….\your_database_name.mdb’”
Resolution
The Visual Basic Data Form Wizard uses the Jet OLEDB 3.5 Provider which does not recognize the Access 2000 database format.