Detect autonumbered field in MS Access

[Jim Buyens]

How can I determine programatically whether a columm in an MS-Access
database is autonumbered?

I've been successful getting column names, datatypes, and so forth
from
<connection>.GetOleDbSchemaTable(OleDbSchemaGuid.Columns...), but if
the autonumber information is there, it isn't obvious.

If possible, I'd like to do this completely in ADO.NET.

Thanks in advance.

Jim Buyens
Microsoft FrontPage MVP

 

[Jeffrey Tan[MSFT]]

Hi Jim ,

We have reviewed your post. We will do some research on your issue. We will
reply to you ASAP.

Thanks for your understanding.

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

 

[Paul Clement]

On 23 Jan 2004 13:48:40 -0800, (e-mail address removed) (Jim Buyens) wrote:

¤ How can I determine programatically whether a columm in an MS-Access
¤ database is autonumbered?
¤
¤ I've been successful getting column names, datatypes, and so forth
¤ from
¤ <connection>.GetOleDbSchemaTable(OleDbSchemaGuid.Columns...), but if
¤ the autonumber information is there, it isn't obvious.
¤
¤ If possible, I'd like to do this completely in ADO.NET.
¤

There is nothing in the schema that differentiates it from another numeric value unless you make
some assumptions.

You will probably need to resort to either ADOX or DAO.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Kevin Yu [MSFT]]

Hi Jim,

Thank you for posting in the community! My name is Kevin, and I will be
assisting you on this issue.

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to get the information about
if the column is auto increment with ADO.NET. If there is any
misunderstanding, please feel free to let me know.

As far as I know, ADO.NET cannot get auto increment information from an
OleDbConnection, because the auto increment is not supported by all
databases. Just as Paul mentioned, we can use ADOX or DAO to achieve this.

Microsoft ActiveX Data Objects Extensions for Data Definition Language and
Security (ADOX) is an extension to the ADO objects and programming model.
ADOX includes objects for schema creation and modification, as well as
security. We can use it to manipulate on the schema of a database. Here I
have written a short example of how to determine whether a column in auto
increment using VB.NET. Since the ADOX.Catalog requires an active
connection, we have to reference both ADO and ADOX libraries. Here are the
steps:

1. Create a new VB.NET project.
2. Select Project / Add Reference... from the menu.
3. Double click to select adodb from the .NET tab. (adodb is the Primary
Interop Assemblies of ADO library.) And select Microsoft ADO Ext. 2.7 for
DLL and Security in the COM tab. (Assume that you have MDAC 2.7 installed
on your computer.)
4. The following is the code. It will go through each column in the column
collection if the column is auto increment, a message box will pop up.

Dim cnn As ADODB.Connection = New ADODB.ConnectionClass
Dim cat As ADOX.Catalog = New ADOX.CatalogClass
Dim tbl As ADOX.Table
Dim col As ADOX.Column

cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\db3.mdb;Persist Security Info=False"
cnn.Open()
cat.ActiveConnection = cnn
tbl = cat.Tables("Table1")
For Each col In tbl.Columns
If col.Properties("AutoIncrement").Value = True Then
MessageBox.Show("auto inc")
End If
Next
End Sub

Hope this helps.

Does this answer your question? If anything is unclear, please feel free to
reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Kevin Yu [MSFT]]

Hi Jim,

For more information about Primary Interop Assemblies, please reference:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/ht
ml/whypriinterop.asp

If you need to know more about ADOX object model, here a link for reference:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/
adoddlo1_1.asp

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Jim Buyens]

Yes, that's right. I'd like to determine from within an ASP.NET page
whether or not a column in an Access database table is auto increment.
The reason is that occasionally, I need to change the schema of an
Access database on a remote Web server without downloading the
database, changing it in Access, and then uploading it. I can write a
page that accepts and processes DDL SQL statements easily enough, but
Access doesn't seem to have a DDL command that displays the schema of
a table. Therefore, I decided to write an ASP.NET page just for
displaying table schemas.

Data types have been a bit confusing, since ADO.NET data type
apparently aren't 1:1 with Access data types. However, I can cope with
that. Auto-number detection has been the major sticking point.

It seems that the method you suggest below will work, and I'll
certainly give it a try. Thanks as well for the reference link to
ADOX. You've been a great help.

Jim Buyens
Microsoft FrontPage MVP

 

[Kevin Yu [MSFT]]

Hi Jim,

You are welcome. If you have any further questions, please feel free to
post them in the community. I am standing by to be of assistance.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."