Solved

Getting AutoIncrement Field in Acces and SQLServer

Posted on 2002-06-04
8
1,156 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
8 Comments
 
LVL 1

Expert Comment

by:killer5
Comment Utility
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
Comment Utility
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 50 total points
Comment Utility
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
 

Author Comment

by:akshayajmera
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:akshayajmera
Comment Utility
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
Comment Utility
Sorry, I don't think you have a choice......I could be wrong.
0
 
LVL 10

Expert Comment

by:smegghead
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

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…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now