SQL Server Performance Forum – Threads Archive
Access 2000 to SQL Server 2000
I recently migrated some user tables from Access to SQL Server while leavingthe forms, reports, and queries in Access with Linked tables to the SQL
instance. Some of the users queries/forms are not working correctly? It
seems that Access Queries return a single empty row when no matches are found
while SQL Server returns an empty result set. In Access, the users use a
query to search for a customer number, if no matching number is found the
customer form is displayed with one record containing no values and the users
can then input new customer information. When the query/form is pointed to
SQL Server, when no matching customer is found a blank page is displayed. Is
there a way to force SQL Server to return an empty row if none are found?
Access will return an empty row, provided that the user has insert permissions on the underlying table. Sometimes when you get a true empty recordset behind an Access form, the whole form is grey, with no controls visible on the Detail section. This way you might be locking the user out – that’s a really ugly situation that you should avoid. Are you using the AfterUpdate event of a combobox to set the Filter property of the form, or to requery the form? Okay, then in the same event you add a check If Me.NewRecord = Not 0 Then which is true if you’re on the blank row, so you know there is no match, and you can act accordingly. Use the Me.NewRecord property in Form_BeforeUpdate in case you need to respond to a new entry on the table, before committing the record. Don’t rely on your "save button" to be clicked for the record to be committed: there are many other occurrences when an Access form will try to commit a record, and in each and every one of them the Form_BeforeUpdate and Form_AfterUpdate events will occur.
]]>