> No query will give you that information, but you can run some VBA to get it.
>
> Dim dbCurr As DAO.Database
> Dim tdfCurr As DAO.TableDef
> Dim fldCurr As DAO.Field
>
> Set dbCurr = CurrentDb()
> For Each tdfCurr In dbCurr.TableDefs
> For Each fldCurr In tdfCurr.Fields
> If (fldCurr.Attributes And dbAutoIncrField) <> 0 Then
> Debug.Print "Field " & fldCurr.Name & " in table " & _
> tdfCurr.Name & " is an AutoNumber field."
> End If
> Next fldCurr
> Next tdfCurr
> Set dbCurr = Nothing
>
> That'll write the results to the Immediate window (Ctrl-G)
>
> --
> Doug Steele, Microsoft Access MVP
>
http://I.Am/DougSteele> (no private e-mails, please)
>
>
> "James R." <James_DBA[ at ]newsgroup.nospam> wrote in message
> news:9C67326D-21D9-45F9-8537-EE1609D59EB8[ at ]microsoft.com...
> > Hi,
> >
> > First off I want to start by saying this is an Access 97 question; I know
> > it's not MS supported and appreciate any help that can be given for this.
> > I'm
> > working on converting this archaic database to SQL Server 2005; it's quite
> > the project.
> >
> > Here is my current problem. I used SSMA for Access some time ago to setup
> > the initial data structure. Now one thing that didn't get migrated
> > properly
> > was the AutoNumber data type; it did get migrated as an Integer, but it
> > didn't get marked as IDENTITY or a seed value placed in for it. So, I'll
> > have
> > to make this change manually.
> >
> > The problem is that my database conversion has approximately 180+ tables,
> > and some of those tables have 30+ fields.
> >
> > I want to know if there is a Query I can run on the Access 97 database to
> > list all of the fields (and the table the field is in) that are of the
> > AutoNumber data type? I know how to do this in SQL but can't seem to find
> > any
> > correlating system tables that contain this information.
> >
> > I don't want to have any changes made to the data type or anything...I
> > just
> > want a simple listing of the fields/tables that have the data type of
> > AutoNumber so I can simply go into the SQL version and make the proper
> > field
> > Identity Specific.
> >
> > If it's of any help the below is a query I've used in SQL to find a BIT
> > data
> > type for all the tables in the database; this is the same results (or
> > idea)
> > that I want to accomplish on the Access 97 database. Hopefully this can be
> > done!
> >
> > DECLARE [ at ]OldDT nvarchar(3)
> > SET [ at ]OldDT = 'bit'
> > SELECT
> > o.Name AS Table_Name,
> > c.Name AS Column_Name
> > FROM
> > sys.Objects o JOIN sys.Columns c ON c.Object_id = o.Object_ID
> > WHERE
> > o.Type IN ('U')
> > AND type_name(c.system_type_id) = [ at ]OldDT
> > GROUP BY o.name, c.name;
> >
> > I appreciate any help that can be provided!
> >
> > Thanks,
> > James
> > --
> > Knowledge is the first step towards success. Little knowledge creates big
> > ideas.
>
>
>