Search for columns in tables that are a specific data type?

[James R.]

Hi,

I've posted this in microsoft.public.access.conversion already but am unsure
of if it might be better answered in this newsgroup. I apologize for the
Cross-posting of this.

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 @OldDT nvarchar(3)
SET @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) = @OldDT
GROUP BY o.name, c.name;

I appreciate any help that can be provided!

Thanks,
James

 

[Jeff Boyce]

James

It may be more info than you want, but you can use the Database Documenter
to show you the table structure of all your tables.

Note ... Access will only allow one "Autonumber" field type per table,
maximum.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[James R.]

Jeff,

Thanks, that's actually a great idea. I could then just have it published to
Word or Excel and then do a simple Find.

Thanks!
James
--
Knowledge is the first step towards success. Little knowledge creates big
ideas.

James

It may be more info than you want, but you can use the Database Documenter
to show you the table structure of all your tables.

Note ... Access will only allow one "Autonumber" field type per table,
maximum.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Allen Browne]

The AutoNumber field is the one that has is marked as auto-incrementing.
Here's an example of how to use DAO code to identify the autonumber field in
a table:
http://allenbrowne.com/func-DAO.html#GetAutoNumDAO
Loop through the TableDefs collection.

If you prefer to use ADOX code, this example identifies the autonumber (and
reads the Seed):
http://allenbrowne.com/func-ADOX.html#GetSeedADOX