ADOX/ADO quirk when creating a primary key

From: Rob Nicholson (informed_at_community.nospam)
Date: 11/26/04 

Date: Fri, 26 Nov 2004 14:18:49 -0000

This is one reason why I hate ADO/ADOX :-) The enclosed code is written in
Excel 2000 VBA and carries out the following:

o Delete existing Jet MDB file
o Create new Jet MDB file using ADOX
o Add a table to the new Jet database called People with two columns: ID &
People
o Add a primary key to the ID field
o Write 10 records into the database
o Read them back
o Print out the record count using SELECT COUNT(*)

The print of the record count returns 0 but there are obviously 10 records
in the recordset (as they print out). Comment out the line that adds the
primary key and it works as expected. So the primary key is causing a
problem.

Even more bizarre - open the database that has been created this way in
Access 2000 and the table is there fine, but no records are visible.
However, if you add a record and run a query similar to the one shown below
then it returns 11 records - the 10 "hidden" ones plus the one added in
Access.

All very bizarre!

Cheers, Rob.

----cut here----
Option Explicit

Sub Test()

' Kill existing database.

On Error Resume Next
Kill "c:\new.mdb"
On Error GoTo 0

' Create new database.

Dim Catalog As New ADOX.Catalog
Catalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\new.mdb"

' Create "People" table with two columns: ID & Name

Dim Table As New ADOX.Table
Table.Name = "People"
Table.Columns.Append "ID", adInteger
Table.Columns.Append "Name", adVarWChar, 50
Table.Keys.Append "PrimaryKey", adKeyPrimary, "ID" ' COMMENT THIS LINE OUT
AND RECORD COUNT WORKS!
Catalog.Tables.Append Table

' Open ADO connection to database.

Dim cn As New ADODB.Connection
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.ConnectionString = "Data Source=c:\new.mdb"
cn.Open

' Populate some records.

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "People", cn, , adLockOptimistic
Dim i As Long
For i = 1 To 10
    rst.AddNew
    rst!ID = i
    rst!Name = "Fred " & Format(i, "00")
    rst.Update
Next
rst.Close

' Display records.

Set rst = New ADODB.Recordset
rst.Open "Select * From People", cn
While Not rst.EOF
    Debug.Print rst!Name
    rst.MoveNext
Wend

' Display count.

Set rst = New ADODB.Recordset
rst.Open "Select Count(*) As NumRecords From People", cn
Debug.Print rst!NumRecords & " records"

End Sub
----cut here----

Relevant Pages

  • Re: Custom ID Field
    ... current database, open the Recordset, find the number, etc. ... Dim rst As DAO.Recordset ... Set rst = db.OpenRecordset ... Each year the format has to be ...
    (microsoft.public.access.formscoding)
  • Re: ADOX/ADO quirk when creating a primary key
    ... > o Create new Jet MDB file using ADOX ... > o Write 10 records into the database ... > Dim Catalog As New ADOX.Catalog ... > Set rst = New ADODB.Recordset ...
    (microsoft.public.data.ado)
  • Re: SQL Server
    ... After splitting the database and using the upsize wizard I received the ... Set rst = dbs.OpenRecordset ... ' Fill in the options for this switchboard page. ... Dim rst As Recordset ...
    (microsoft.public.access.gettingstarted)
  • Re: How to get recordcount or number of records when using linked tables
    ... I have an access 2003 database as a front end to another access 2003 ... I am trying to set the recordsource of a form to a query established by ... Dim rst As Recordset ... Set rst = db.OpenRecordset ...
    (comp.databases.ms-access)
  • Re: Is This Possible ... ? Yes - Upload images to an Access database
    ... It looks like your databases folder is outside the root of your web ... I created a database exactly as you said, the only change I made was to ... and underneath an 'upload' button and a 'view images' button. ... Dim con As New Data.OleDb.OleDbConnection ...
    (microsoft.public.dotnet.framework.aspnet)