Solved

Getting AutoIncrement Field in Acces and SQLServer

Posted on 2002-06-04
8
1,167 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 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
Independent Software Vendors: 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

Independent Software Vendors: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MsgBox 2 61
Using "ScreenUpdating" 6 83
Prevent checkbox click event occur while editing it in vb6 8 63
Passing a Text Box name to a Sub 6 102
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…

735 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