empty string in access null error in sql server

[Tarnish]

Not really sure where I should ask this, and I'm sure the answer is already on the forums somewhere, but...

I am converting ms access 2003 split f.e./b.e. to ms access 2010 f.e./ms sql 2012 b.e. I'm deploying to win7/64 bit desktops running 32-bit office 2010.

I've run into a number of issues getting the error message about trying to assign a null to a non-variant datatype. The access front end vba would prevent that issue from arising before the record was saved, but it appears that sql server is checking the value before any of my access f.e. vba code runs, so all my validation code appears to be ineffective, now.

Is my assessment correct? Is sql server trying to validate the entries whenever the user leaves a data control bound to a table/query column (but before the user attempts to save the record via the form)? If so, is there a way around this without having to change all the data types to variant? Did I miss some event in access that can actually be used to catch the problem before sql server jumps in?

Just for full disclosure, the issue arises when a user begins to type a value into a form control but then deletes what they have typed.

Thanks for any suggestions.

T

 

[lameid]

Sounds like this is an Access question as it seems to be writing to the database and belongs here: forum181

Having said that, while Access will technically generally function with Linked Tables to SQL server, there are better practices. Generally these use disconnected recordsets or instead use other methods to update unbound forms from Access to the target database.

SQL Passthru Queries and ADO command and recordset objectes are things to get familiar with to get you started as is T-SQL which is this forum.

 

[Tarnish]

Thanks for the reply, lameid--

Thought about the access forums, but wanted to check here first. I think access' best answer for me is probably just to unbind the form and build the insert sql from the form controls, but I was hoping not to have to do that. If that's the best answer I can find, I'll have no choice, but that's a lot of work in this case and I've been given virtually no time to get this thing up and running.

Thanks again,
T