ADO Field Determine if Autonumber with VBA

K

Ken Higgins

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
 
A

Allen Browne

Can you use ADOX to test Properties("Increment") of the Column in the Table
of the Catalog?
 
B

Barry Gilbert

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
 
K

Ken Higgins

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.
 
K

Ken Higgins

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.
 
R

RoyVidar

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
 
K

Ken Higgins

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
 
Top