Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Posts
    2

    Angry Unanswered: in VB, How to detect if a database field is of "Autonumber" type???

    I've been trying to write a DBMS application, very simple really but once the connection is on and i'm browsing the recordsets, i CAN'T update any fields that are of type autonumber!! (SQL language constraint)

    I NEED to be able to detect those field through my code so i can skip the field in the SQL 'update' process.

    I'd appreciate any help...

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Hi fffreak82,

    Hopefully you can adapt this to your needs. Be sure the "Microsoft DAO Object Library" reference is selected. Pass the subroutine the name of the .mdb where the tables are (full drive, path, and filename) or don't pass anything, and the tables in the current .mdb will be searched.

    JT
    Code:
    Option Compare Database
    
    Public Sub ShowTheAutonumberField(Optional varData As Variant = Null)
       Dim dbSelectedMDB As Database
       Dim lngFieldIndex As Long
       Dim lngTableIndex As Long
       Dim strAutoNumberField As String
       Dim strTableName As String
    
       If IsNull(varData) Then
          Set dbSelectedMDB = CurrentDb
       Else
          Set dbSelectedMDB = OpenDatabase(varData)
       End If
       ' look through each table
       For lngTableIndex = 0 To dbSelectedMDB.TableDefs.Count - 1
          ' look through each field
          For lngFieldIndex = 0 To dbSelectedMDB.TableDefs(lngTableIndex).Fields.Count - 1
             ' check field type
             If dbSelectedMDB.TableDefs(lngTableIndex).Fields(lngFieldIndex).Type = 4 Then
                ' field is datatype "long"
                Select Case dbSelectedMDB.TableDefs(lngTableIndex).Fields(lngFieldIndex).Attributes
                Case 16, 17, 18
                   ' if the attributes evaluate to 16, 17, or 18
                   ' it's the autonumber field - do what you like
                   strAutoNumberField = dbSelectedMDB.TableDefs(lngTableIndex).Fields(lngFieldIndex).Name
                   strTableName = dbSelectedMDB.TableDefs(lngTableIndex).Name
                   Exit For
                End Select
             End If
          Next
       Next
       dbSelectedMDB.Close
       Set dbSelectedMDB = Nothing
    End Sub

  3. #3
    Join Date
    Mar 2003
    Posts
    2

    Talking



    Hi JT,

    Thx for your help really, i've been looking for this method for i don't know how long...

    I've been working on VB for about a month now, so i'm a beginner... Thx again!

  4. #4
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179

    Smile

    Glad I could help, fffreak82.

    FYI: In my many, many years of working with VBA (I refuse to be exact, cuz it might provide a clue to how ancient I am), I've never designed a table with more than one autonumber field. I'm not even sure it's possible to do it! But in the off-chance that you do - get rid of the "Exit For" statement and the procedure will search through all the fields, instead of stopping after it finds the first autonumber field.

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    you can't have more than 1 AN per table and i can't think of any use for 2 AN's
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •