Solved

Getting AutoIncrement Field in Acces and SQLServer

Posted on 2002-06-04
8
1,158 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
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 50 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

930 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

12 Experts available now in Live!

Get 1:1 Help Now