Access bound form/SQL Server - Any gotchas?

[jjwild]

I am helping a client move their data from Jet to SQL Server 2000.

They are using Access 2000 bound forms and want to continue to do so.

I have looked at the posts in this area and it seems fairly safe.

Just to be sure - can someone who has Access 2000 bound forms linked to SQL Server 2000 tables tell me if they have run into any problems (or not) with it?

Thanks,

John Wildanger

 

[jjwild]

I have found one gotcha so far.

If you want to have Null values default to the empty string in SQL Server, Access will refuse to let you blank out a text field. It will put a null there instead of an empty string and see that Null values are not allowed in the SQL Server table.

This is not a problem with a Jet backend.

A workaround is to put the following code in each bound form:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim ctlControl As Control

For Each ctlControl In Me.Controls
If ctlControl.ControlType = acTextBox Then
If IsNull(ctlControl) Then
ctlControl = ""
End If
End If
Next ctlControl

End Sub

This is preliminary, but seems to work.

John Wildanger

 

[jjwild]

The workaround I posted above is incorrect. If the user blanks out a text value in an Access form bound to a not null SQL Server table varchar field, Access immediately triggers an error - no events in VBA code are triggered and the user may not leave the field until the error is fixed.

If a user wants to blank out such a field, he/she can enter "", two double quotes, to tell Access to enter an empty string into that field.