Hello,

Any thoughts on how to get Access to recognize the DefaultValues set in SQL Server?

I have default values set up in SQL, but when I open the Access table in Design, they're not showing up. The Required property of the field comes through but not the DefaultValues? This is causing havoc for a CopyTable function that does a make table and reads the DefaultValues and Required when it builds the table. The Required is true and the DefaultValue is not set.

Anybody ever see this?

Casey,

The definition of the table resides on the Server. Access "tries" to
let you see the Design View of the table, but it has limitations.

I take it that if you try to insert new records from the Access side,
the Default values are handled properly.

If you add another layer and are trying to "clone" the table's structure
on the Access side, that will be tougher because Access is not controlling
the table's definition.

One option would be to copy the table on the Server side using a SQL
statement like "Select * Into tblNew From tblOld where 1 = 0". In theory
that would copy the structure with no data rows. Note: Just tried that
and even SQL Server won't propogate the Default value!!!

You can use the sp_columns Stored Procedure. It will let you access the
Default value in its column 'Column_Def'. Then your Access front-end can
provide it.

Sure seems a lot more complicated that it should be.

hth,
Wayne

Casey,

Simple example DefaultValue will be either "" or the Default:

DefaultValue = fnGetDefaultValue("YourTable", "YourField")



Public Function fnGetDefaultValue(TableName As String, FieldName As String) As String
Dim rst As New ADODB.Recordset
DbConnection.Execute "Use YourDatabase" ' <-- Your DB name goes here
rst.Open "Exec sp_columns '" & TableName & "'", DbConnection
While Not rst.EOF And Not rst.BOF
If rst!Column_Name = FieldName Then
fnGetDefaultValue = Mid(rst!Column_Def, 3, Len(rst!Column_Def) - 4)
Exit Function
End If
rst.MoveNext
Wend
fnGetDefaultValue = ""
End Function


hth,
Wayne

Wayne,

Thanks for testing that. That's a great idea. I will test that out and let you know. I appreciate your help.

WayneRyan,

Thanks. That works great!

I would also like to send it a Column_Name too since I know which column_name I'm looking for.
Syntax from MSDN:
strSQL ="Exec sp_columns @table_name = N'TMPL_CadData', @column_name = N'lngLength'"
rst.Open strSQL, cnn

It opens ok, but shows that the rst.EOF = true

...Can you help with the syntax to use for sending a column_name too if possible???