ADO Field Determine if Autonumber with VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Anyone out there know how to determine if a number field is an
Autonumber using VBA with ADO (I cant use DAO for this project).
Thanks
 
Can you use ADOX to test Properties("Increment") of the Column in the Table
of the Catalog?
 
The Field object has an attributes property that you can evaluate:

If CBool(MyRs.Fields("fieldName").Attributes AND adFldRowId) Then
Msgbox "This is an autonumber"
End If

Barry
 
Thanks ALex but no luck in finding code on that site.
Here is the code I have been using and going crazy with:

Public Sub testADOX()
Dim adoxTABLE As ADOX.TABLE
Dim adoxCOLUMN As ADOX.Column
Dim adoxCOLUMNS As ADOX.Columns
Dim CAT As ADOX.Catalog
Dim Cnxn As ADODB.Connection

Set Cnxn = New ADODB.Connection
Set Cnxn = CurrentProject.AccessConnection

Set CAT = New ADOX.Catalog
Set CAT.ActiveConnection = Cnxn

'this next statement to open the CAT is a performance killer
Set adoxTABLE = CAT.TABLES("VolumeDiscountTable")

Set adoxCOLUMNS = adoxTABLE.Columns

For Each adoxCOLUMN In adoxCOLUMNS
Debug.Print adoxCOLUMN.Name, adoxCOLUMN.Properties.Count

if adoxCOLUMN.name = "PlanId" then STOP

Next adoxCOLUMN
Set CAT = Nothing
Cnxn.Close
Set Cnxn = Nothing
End Sub

The problem I am having is when the STOP executes I can use
the immediate window to examine the properties etc.

I dont see any properties (ie properties.count = 0) and
NOTHING of either "INCREMENT" or "AutoIncrement" anywhere i can find.

Also, if you might be able to show us if there is a better way to
open the catalog.

The way I am doing it is unacceptably slow.

But my main focus is on the AutoIncrement property. I'll use
the OpenSchema method on the Connection if I have to. But I cannot seem
to locate any of the OLE DB Recordsets that contain this property either.

Thats the issue. If you have a code sample that works Please post or
reference it that I thinktrying to abstract this might be more difficult.
 
Thanks Barry -- here is the code I tried withat this suggestion No Go - sorry

Public Sub TestAutoNum2()

Dim myRS As ADODB.Recordset

Set myRS = New ADODB.Recordset
With myRS
Set .ActiveConnection = CurrentProject.AccessConnection
.Open "VolumeDiscountTable", Options:=adCmdTable
If CBool(.Fields("PlanId").Attributes And adFldRowID) Then
MsgBox "This is an autonumber"
End If
End With
End Sub

PlanID is an autonumber field and I checked all that prior to posting this.
 
Ken Higgins wrote in message
Thanks Barry -- here is the code I tried withat this suggestion No
Go - sorry

Public Sub TestAutoNum2()

Dim myRS As ADODB.Recordset

Set myRS = New ADODB.Recordset
With myRS
Set .ActiveConnection = CurrentProject.AccessConnection
.Open "VolumeDiscountTable", Options:=adCmdTable
If CBool(.Fields("PlanId").Attributes And adFldRowID) Then
MsgBox "This is an autonumber"
End If
End With
End Sub

PlanID is an autonumber field and I checked all that prior to posting
this.
Click to expand...

Check out this:
http://support.microsoft.com/default.aspx?scid=kb;en-us;304100
 
The reference worked!

I post my test program humbly below. The trick is to know that
"ISAUTOINCREMENT" was a property of a field in the adodb.recordset.

As you said the Microsoft link answered the question:
http://support.microsoft.com/default.aspx?scid=kb;en-us;304100

thanks Roy-Vidar!

Respectfully,
Ken Higgins

Working Test Code follows:
'----------------------------------------------------------------------------
Public Sub TestAutoNum2()
'code that worked on my machine thank you
Dim strConnect As String
Dim adoCON As ADODB.Connection
Dim rs As ADODB.Recordset
Dim X As Long

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=W:\Billing051\BillingDatabase051.mdb;" & _
"Persist Security Info=False"

Set adoCON = New ADODB.Connection
With adoCON
.ConnectionString = strConnect
.Open
End With

Set rs = New ADODB.Recordset
rs.ActiveConnection = adoCON
rs.CursorLocation = adUseServer
rs.CursorType = adOpenStatic
rs.Open "Select * from [VolumeDiscountTable];"
For X = 0 To rs.Fields.Count - 1
If rs.Fields(X).Properties("ISAUTOINCREMENT") = True Then
MsgBox "Field " & rs.Fields(X).Name & " is Autoincrement"
End If
Next X

rs.Close
Set rs = Nothing
Set adoCON = Nothing
End Sub
 
Back
Top