Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Getting AutoIncrement Field in Acces and SQLServer

Posted on 2002-06-04
8
Medium Priority
?
1,189 Views
Last Modified: 2007-12-19
Hi All Experts,
 I am trying to get metadata for tables of Access and SQLServer using Connection.Openschema method.

All the information needed is fectched properly except i am able to identify wethere the field is Autoincrment or not. In Autoincremnt Column the datatype fetched is Number.

Is there any way to Identify wethere the column is Autoincremnt(In Access) and Identity (In SQLServer).

Thanx is Advance

Akshay
0
Comment
Question by:akshayajmera
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 1

Expert Comment

by:killer5
ID: 7055524
In SQLServer you can do this. But in Access... ???

TO GET THE AUTO-INCREMENT FIELDS IN SQLServer...

Select Name From SysColumns
where autoval is not null
0
 
LVL 17

Expert Comment

by:inthedark
ID: 7055541
Here is the code that finds the AutoIncrement/Counter/Indentity field:

For Access:
   
        Set aCat = New ADOX.Catalog
        aCat.ActiveConnection = CN
        Set aTab = aCat.Tables(TableName)
        For Each acol In aTab.Columns
            If acol.Properties("AutoIncrement") = True Then
                IdentityName = acol.Name
                IdentitySeed = acol.Properties("Seed")
                IdentityIncrement = acol.Properties("Increment")
                Exit For
            End If
        Next
        Set acol = Nothing
        Set aTab = Nothing
        Set aCat = Nothing


For SQL Server:

    SQL = "SELECT  t.TABLE_NAME, c.name AS COLUMN_NAME, IDENT_SEED(t.TABLE_NAME) As Seed, IDENT_INCR(t.TABLE_NAME) As Increment"
    SQL = SQL + " From INFORMATION_SCHEMA.TABLES AS t INNER JOIN sysobjects AS s ON s.name = t.TABLE_NAME"
    SQL = SQL + " INNER JOIN syscolumns AS c ON c.id = s.id"
    SQL = SQL + " Where ((t.TABLE_NAME='" + TableName + "')"
    SQL = SQL + " And (OBJECTPROPERTY(OBJECT_ID(t.TABLE_NAME), 'TableHasIdentity') = 1)"
    SQL = SQL + " AND (t.TABLE_TYPE = 'BASE TABLE')) AND c.autoval IS NOT NULL"
   

    ok = ADO.OpenRSROOK(CN, RS, SQL) ' Open readonly recordset
    If Ok And Not RS.EOF Then
        IdentitySeed = ADO.GitNum(RS("Seed"))
        IdentityIncrement = ADO.GitNum(RS("Increment"))
        IdentityName = ADO.Git(RS("COLUMN_NAME"))
    End If

Hope this helps.
0
 
LVL 17

Accepted Solution

by:
inthedark earned 200 total points
ID: 7055547
Here are some other ADOX table management features:

' make project references to:
' Microsoft ActiveX Data Objects 2.x
' Microsoft ADO Ext DLL & Security

Dim CN As ADODB.Connection
Dim AccessMDBFile As String

AccessMDBFile = "D:\test.mdb"
Set CN = New ADODB.Connection

CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + AccessMDBFile + ";"
CN.Open

' here are the main adox table/field objects
Dim aCat As ADOX.Catalog
Dim aTable As ADOX.Table
Dim aKey As ADOX.Key
Dim aIndex As ADOX.Index
Dim aCol As ADOX.Column ' a field object

' first get access to the Catalog
Set aCat = New ADOX.Catalog
Set aCat.ActiveConnection = CN

' now you can work with a table

Set aTable = New ADOX.Table

aTable.Name = "New Table3"
aCat.Tables.Append aTable     ' save the new table

Set aTable = aCat.Tables("New Table3")


Set aCol = New ADOX.Column

aCol.Name = "Field1"
aCol.DefinedSize = 20
aCol.Type = adVarWChar ' VB autosuggests here which helps

aTable.Columns.Append aCol  ' save the new field
Set aCol = Nothing

' create another column
Set aCol = New ADOX.Column
aCol.Name = "Field2"
aCol.DefinedSize = 10
aCol.Type = adVarWChar ' VB autosuggests here which helps

aTable.Columns.Append aCol  ' save the new field
Set aCol = Nothing

' Create a key
Set aKey = New ADOX.Key
aKey.Name = "Primary"
aKey.Type = adKeyPrimary ' you can only have one primary key
aKey.Columns.Append "Field1"
aKey.Columns.Append "Field2"

aTable.Keys.Append aKey ' save the key

Set aIndex = New ADOX.Index
aIndex.Name = "ByField2"

aIndex.Clustered = False
aIndex.Columns.Append "Field2"
aIndex.Columns.Append "Field1"

aTable.Indexes.Append aIndex ' save the index

Set aTable = Nothing

' Delete table
aCat.Tables.Delete "New Table3"

Set aCat = Nothing
CN.Close
Set CN = Nothing
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:akshayajmera
ID: 7055581
Hi IntheDark,

 Thanx for the code
 In Sql Server it is working perfectly alright.
 
In Access I am using Connection.Openschema , I don't want to user ADOX.Catalog , is there another way ?

Akshay
0
 

Author Comment

by:akshayajmera
ID: 7055619
Hi IntheDark,

 Thanx for the code
 In Sql Server it is working perfectly alright.
 
In Access I am using Connection.Openschema , I don't want to user ADOX.Catalog , is there another way ?

Akshay
0
 
LVL 17

Expert Comment

by:inthedark
ID: 7055623
Sorry, I don't think you have a choice......I could be wrong.
0
 
LVL 10

Expert Comment

by:smegghead
ID: 7055943
This works... Using the "COLUMN_FLAGS" field 'anded' with 32. Also the data type must be adInteger (eq. to Long Integer in Access)

    Dim x As New Connection
    Dim rs As New Recordset
    Dim IsAuto As Boolean
    x.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=fred.mdb;Persist Security Info=False"
    Set rs = x.OpenSchema(adSchemaColumns, Array(Empty, Empty, "table1"))
    While Not rs.EOF
        IsAuto = ((rs.Fields("COLUMN_FLAGS") And 32) = 0) And (rs.Fields("DATA_TYPE") = adInteger)
        Debug.Print rs.Fields("COLUMN_NAME") & vbTab & IIf(IsAuto, "Is Autonumber", "Not Autonumber")
        rs.MoveNext
    Wend
0
 
LVL 17

Expert Comment

by:inthedark
ID: 7058309
smegghead I suspect that if you use SQL server V7 and change your identity field to an Int the column flags don't change. This is also true of access.

Try adding a field called "NotAnAutonumber" to your table1. Make the field a Number "Long Integer"  edit the field property "Required" to yes.

The when you run your example you get:

field1  Is Autonumber
NotAnAutonumber  Is Autonumber

<FLAME>

If only it was that easy.  The real question here is why did Microsoft make it so hard to find out information that is essential info for all real BD programmers? The answer to that question is "They don't have any, so they wouldn't know!"

What Microsoft should have done is get the programmer who wrote the ADO drivers for SQL server and the programmer who wrote the ADO drivers for Access. Shut them in a room with enough beer. Locked the door and shouted through the keyhole "Don't forget that millions of programmers will need to know what the Identity field is called and the seed, and the increment!"

This is a case where the actions of one man could have saved the pain of millions. Imagine the cost of all of those waisted hours!

</FLAME>
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question