You may need to change code that uses objects and methods that are not supported in remote tables. In many cases, such as the CompactDatabase method or the Container object, there is simply no equivalent on a SQL Server.
The following data access objects are not supported:
The following methods are not supported:
Microsoft Access supports transactions nested up to five levels. SQL Server supports only one transaction at a time. If your Microsoft Access code includes nested transactions, only the outermost transaction is sent to the server; the other transactions are ignored, and no error is produced.
BeginTrans 'Outermost transaction sent to the server
'edits, updates
BeginTrans 'Nested transaction is not sent to the server
'edits, updates
CommittTrans
CommittTrans
You may need to modify your code in light of this difference. If your nested transactions are rolled back when any of them fail, the single-transaction limitation won't pose major difficulties. If some nested transactions are committed even if others fail, duplicating this functionality with one transaction may prove to be difficult.
Microsoft Access default and counter field values appear when you begin editing a new record. Default values generated by SQL Server defaults and counter values generated by table triggers appear only after a record has been inserted. You will need to change any code, such as code for lookups, that depends on having the values before the record is committed.
In Microsoft Access, field validation occurs when the user tabs out of a field. When you edit SQL Server data in attached tables, triggers and rules are not fired until you leave the record. Record validation rules that rely on field validation occurring when a field is exited may need to be modified.
The upsizing report shows whether each Microsoft Access table validation rule, field validation rule, and default was successfully converted. If the Upsizing Wizard was not successful in translating an Microsoft Access Basic expression, you will need to rewrite it using Transact-SQL.
For example, if a field validation rule could not be converted, you should rewrite the validation rule in Transact-SQL and then add it to the update and insert trigger for the table. You could also create a rule.
Another option is to perform validation at the form level in Microsoft Access. However, if server data is then modified without using a particular form, the validation will not be applied and invalid data may be entered.
You cannot open a Dynaset object against an attached server table in exclusive mode. Consequently, the value of the Record Locks property of all forms must be set to No Locks or Edited Record. (Edited Record is treated the same as No Locks.) The value All Records is illegal and generates an error.
With server tables, Microsoft Access uses optimistic locking internally. The row is locked only while the update process occurs, when the edited value is committed, which is usually a very brief interval.
Many developers take a "back-end, front-end" approach to developing Microsoft Access applications. They keep tables in one database (the back end) and all other objects such as forms and reports in another database (the front end).
To upsize a back-end, front-end application
1. Upsize the back-end database using the Upsizing Wizard.
2. After upsizing, open the front-end database in Microsoft Access.
3. From the File menu, choose Add-ins, and then choose Import Database.
A File Open dialog box is displayed.
4. Choose the back-end database, then click OK.
This will import all tables and queries from the back-end database, including the attached tables and aliasing queries that the Upsizing Wizard created.
You may delete local tables and queries that you don't need.
Whenever your application causes an error to occur on the server, Microsoft Access displays two messages: first the Microsoft Access error message and then the message from the server. If you trap an ODBC error (numbered 3146 through 3299) in a Microsoft Access Basic procedure, the Error$ function returns both the Microsoft Access message and all server error messages concatenated together. By parsing the value returned by Error$, you can find out which server error occurred and what the text of the server error message is.
The following error-handling code demonstrates how you can obtain the server error number and message when an ODBC error occurs:
Dim RemoteError As Integer, ErrorText As String
Dim NumStart As Integer, TextStart As Integer
Select Case Err
Case 3146 To 3299 ' It's an ODBC error, so parse it.
' Find error number.
NumStart = InStr(Error$, "(#") + 2
ErrorNum = Val(Mid$(Error$, NumStart))
' Find error message.
TextStart = InStr(Error$, "] ") + 2
ErrorText = Mid$(Error$, TextStart, NumStart - TextStart - 3)
... ' Handle remote error.