When dealing with a SQL back end, there are some things you have to watch out for.
SQL Server fields are called columns. I will refer to them as such for the sake of clarity.
There are some differences of which you must be aware.
Yes/No fields are bit columns in a SQL table.
A bit column can be null, zero or 1. In Access, a Yes/No field is either 0 or -1. Bit columns display in Access as 0 or -1. But in this case, what is displayed is not always what is real.
The 0 could be 0 or null. Access will choke on nulls in that type of field. You MUST make sure all nulls are updated to 0. In fact, it is a good practice to make a default of 0 for bit columns.
The -1 is actually 1. All queries that have a bit column in the criteria that is equal to -1 must be changed to either = 1 or <>0.
Any table or view** that is not static MUST have a unique index.
Any competent SQL DBA will tell you every table should have a clustered Primary Key, or, if it is not clustered there should be another clustered index on the table. Tables and views without unique indices are not updatable. The error message you get
(if any) is too vague to let you know this.
Any design change made to a table or view will not show up in the Access front end.
You must relink the object. If you try to insert a value in the new column through VBA without relinking the value will go into the column that used to have the new column's ordinal position.
Let's say you have a SQL table with columns in this order:
CustomerID int IDENTITY(1,1) - Same as an AutoNumber in Access starting at 1 and incrementing by 1
CompanyName varchar(50) - Same as a text (50) in Access
Address varchar(50)
Then you create a new column Rating int and put it in the table right after CompanyName. Should you try to update Rating the data will actually go into Address. In this case, there will be no error because whatever alpha-numeric value you enter
will be accepted in the Address column provided it is not too big.
See the potential for disaster?
Sometimes if you change an object used by a view, not only will Access not see the change without a relink, but SQL might not even see the change until you run a DDL command of sp_refreshview @viewname = MyView to update the view's metadata.
Relinking a view causes it to no longer be updatable.
When you link to a view Access will ask you to select field(s) that makes a record unique. While you do not have to do this, if you don't, the view will not be updatable. Relinking manually might lose the unique index that Access creates. If you relink
through VBA the index will not be kept unless you write code to do it.
Using the Format property in a form or report will not always work.
I have had the formatting ignored whether I use the control's Format property or the Format() function directly in the query. I worked around this by using the Format() function directly in the ControlSource as in:
=Format([Amount], '#,###.00')
This bug may have been fixed, but if it hasn't at least you will be aware of it if it bites you.
DAO code modifications
There are a couple changes you will need to make when working with DAO. You need to include the argument "dbSeeChanges"
1. Recordsets:
Set rs = db.OpenRecordset("SELECT Top 1 * FROM ErrorLog", _
dbOpenDynaset, dbSeeChanges)
2. Executing a SQL statement:
CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges
Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals
Why am I getting a Write Conflict pop-up with a grayed out "save record" button in Access?
