Need to know all fields that have default values

[nrugado]

I have an access database that I have converted the tables to SQL Server. I am now in the process of changing some field types and have noticed that the default value field that was populated in MS access is not Populated in the SQL server Tables/Fields. Does anyone know where to get this information, if it can even be gotten out of the MDB file? It will save me the time of having to look at each and every field in my Access DB for a Default value. Any help would greatly be appreciated.

Thanks Nick

 

[cainemart]

I think you are going to have to check every table and every field in the tables to get the info you need but I'm not 100% sure.

cainebean

 

[nrugado]

That's what I was afraid of.... Thanks

 

[cbogz]

I don't have my Access/VBA books handy, but I think there is a way to do this in VBA. You'd have to cycle through the collection of tables, then through the fields of each table, and look to see if the default value property was set or not. This might be a question you'd want to post to the Access Modules forum.

 

[cmmrfrds]

Here is a function you can modify to get what you need - it is a property in the tables/columns collection.


Function catalogTC()
'-- set reference to ADOX library
'- Microsoft ADO Ext. 2.6 for DDL and Security
'-- Microsoft ActiveX data objects 2.6 library also needed for ADO

Dim cg As New ADOX.Catalog
Dim tb As New ADOX.Table
Dim cn As ADODB.Connection
Dim cl As Column
Dim pp As Property

Set cg.ActiveConnection = CurrentProject.Connection

For Each tb In cg.Tables
Debug.Print "table name = "; "-------"; tb.Name; "--------"; tb.Type


If (tb.Type = "TABLE" And tb.Name = "Categorys&quot Then
For Each cl In tb.Columns
Debug.Print "name = "; cl.Name
For Each pp In cl.Properties
Debug.Print "property name = "; pp.Name
Debug.Print "property value = "; pp.Value
Next
Next
End If
Next

End Function