Access Programming and SQL Linked Tables

Write Conflict Errors Updating Records of a Linked SQL Server Table : Y/N Fields

Null Y/N fields in Access do not map to SQL Server bit fields. When Access updates a record containing a null Y/N it compares the original SQL value and decides it has incorrectly. Solution is make all SQL bit fields not allow null and give fields a default value.

Other data conversion errors occur, for example with decimals. See: http://support.microsoft.com/kb/280730

Access 2007 and Updateable SQL Views

Updateable views only work if joins are inner joins.

Data Types : Access and SQL Server

Use datetime not date
bit : set null to no and set default values

Very Good Article: What are the main differences between Access and SQL Server?

Access reports no error when string is too long, just truncates it.
When run an update/insert query from the query designer for SQL Server linked table the data gets truncated.
If run a query direct from code then error occurs if data is too big for field.

There are several types of data that Office Access is unable to check reliably for matching values. These include large object types, such as text, ntext, image, and the varchar(max), nvarchar(max), and varbinary(max) types introduced in SQL Server 2005. In addition, floating-point numeric types, such as real and float, are subject to rounding issues that can make comparisons imprecise, resulting in cancelled updates when the values haven't really changed. Office Access also has trouble updating tables containing bit columns that do not have a default value and that contain null values.

A quick and easy way to remedy these problems is to add a timestamp column to the table on SQL Server. The data in a timestamp column. It is a binary value guaranteed to be unique across the database and to increase automatically every time a new value is assigned to any column in the table.

Access automatically detects when a table contains a timestamp and uses it in the WHERE clause of all UPDATE and DELETE statements affecting that table. This is more efficient and more accurate than verifying that all the other columns still have the same values they had when the dynaset was last refreshed.

SQL Server Views and Access

You must use valid Transact-SQL syntax. For example:
the wildcard characters used with LIKE are % and _, not * and ?.
single quotation marks—not double quotation marks and
pound signs—are used to delimit both literal strings and dates.

Many of the built-in functions for manipulating strings and dates are different. Instead of using IIF to create conditional expressions, you use CASE statements.

Unlike saved SELECT queries in Office Access, SQL views do not support parameters. You can get behavior similar to parameterized SELECT queries with table-valued user-defined functions in SQL Server, but you can't link to functions.

You also cannot sort views by adding an ORDER BY clause. The order of rows in views is undefined and should be specified in queries that select rows from views.

Update statement executed against a view can only modify columns from one table at a time.

http://msdn.microsoft.com/en-us/library/bb188204(v=sql.90).aspx

ADO Connection String for Access SQL Server Linked Table.

The connection string for ODBC DNS less to SQL Server is something like:

"ODBC;DRIVER=SQL Server;SERVER=MOOLA\SQLEXPRESS; APP=GPT;DATABASE=GPT;Trusted_Connection=Yes;QuotedId=Yes"

I was able to use this directly with ADODB.Command. But as of Feb '11 this stopped working, and got a error Method 'Active Connection' of object _command failed. The solution was simply to remove the ODBC; part of the conenction string:

Access ODBC Linked Tables : Requery to prevent "Data Changed" Errors

When you edit a field on a subform that has a linked SQL table as its record source, and you try to edit a field on the main form that is based on the same linked SQL table, you may receive the following message:

The data has been changed....

This error occurs when you start to edit a record that has changed since you began viewing it. The record is updated in the table, but the record source of the main form has not been refreshed to reflect the change.

This behavior is related to the ODBC Refresh Interval. If you have an ODBC interval of 60 seconds and you wait 60 seconds before you edit the record on the main form, you will not receive this error. However, setting the ODBC Refresh Interval to a low enough number to allow you to edit the record without receiving this error would cause an increase in network traffic.

To resolve this error, add Me.Parent.Requery to the AfterUpdate event of the subform.

The error does not occur with Access tables.

SQL Server Views, Updating and Recording Modified Date/User

Muti-table view in SQL server, with many related fields, only fields in one table updatable.

Linked table in Access in Access, Index on primary key.

Updates written back with no errors on a sub form datasheet.

But when trying to use an Access event to record the user and datetime of an update with:

USE dbname
Private Sub Form_BeforeUpdate(Cancel As Integer)

    Me.editUserId = pUserID
    Me.editDT = Now
    
End Sub
        

USE dbname
GO

CREATE TRIGGER updateJob ON job FOR UPDATE
AS
BEGIN
    UPDATE job SET editUserName=SYSTEM_USER, editDT=GETDATE()
        WHERE jobid IN (SELECT jobid FROM inserted)
End

SQL Server Commands not Supported in Access

SELECT COUNT(DISTINCT SupplierID) FROM Jobs

Use Passthrough or stored procedure to get round this.