Solved

Getting AutoIncrement Field in Acces and SQLServer

Posted on 2002-06-04
8
1,163 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

816 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