Help finding field definitions...

[Klaus L Jensen]

Hey

I have a Access2000 (many) database, and I need to make a special tool to
port this database to MsSql.. I cant extract the field names / types from
access???

I can get at list of tables, now I need the fields in each table..

 

[Allen Browne]

There is a function in this link you can borrow to get the information on
the field names, types, size, and descriptions:
http://allenbrowne.com/func-06.html

 

[Klaus L Jensen]

Thanks it is great..

But can I not do it in SQL???

Do not wish DAO i possible..

 

[Allen Browne]

No.

Once you have the names and data types of the fields, its easy enough to
combine them however you want.

 

[Klaus L Jensen]

Ok, thanks... But I'm MSSQL developer, and I'm use to get fielddefs from
some sql string... I have found some hidden tables, but can't find the
table containing the fielddefs...

But thanks for the tip....

 

[Allen Browne]

You can query the table names from MSysObjects, but not the field names.

The other alternative is ADOX:

Function ShowAllTables()
Dim cat As New ADOX.Catalog 'Root object of ADOX.
Dim tbl As ADOX.Table 'Each Table in Tables.
Dim col As ADOX.Column 'Each Column in the Table.

'Point the catalog to the current project's connection.
Set cat.ActiveConnection = CurrentProject.Connection

'Loop through the tables.
For Each tbl In cat.Tables
Debug.Print tbl.Name
'Loop through the columns of the table.
For Each col In tbl.Columns
Debug.Print , col.Name, col.Type
Next
Debug.Print "--------------------------------"
Next

'Clean up
Set col = Nothing
Set tbl = Nothing
Set cat = Nothing
End Function