SQL Server Access linked DefaultValue not showing up.

[casey]

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?

 

[WayneRyan]

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

 

[WayneRyan]

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

 

[casey]

Thanks!!!

Wayne,

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

 

[casey]

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???