Complex queries
The cross tab queries and other complex queries were not
converted to their equivalent in SQL. We had to manually convert those complex
queries to make them compatible with SQL Server. Those were converted to views,
stored procedures or functions.
Write-Conflict Error
“This record has been changed by another user since you
started editing it. If you save the record, you will overwrite the changes the
other user made. Copying the changes to the clipboard will let you look at the
values the other user entered, and then paste your changes back in if you
decide to make changes.”
Parent!LastUpdateBy = CurrentUser( ) and Parent!LastUpdateDate
= Date. These two lines of code were not supported in Access ADP and we found
this to be the reason for the Write Conflict error on the sub forms in our project.
We removed these two lines of code to avoid a write conflict error. However,
we had to add LastUpdatedBy = CurrentUser () and LastUpdateDate = Date only in
the Save Button Function to update the forms at the end. In this case, the
main form did not get updated immediately after any modifications were made to
the sub forms value. It only got updated after pressing the “Save Button.” The
cause of write conflict error may be different for other ADP applications. It
may be due to not assigning a default value to a bit data type in SQL or due to
some primary key problems.
The Write Conflict error may appear when two forms that
update the same data source are opened at the same time. The first form puts
an edit lock on the record(s) and then the second form changes the record(s) and
saves the changes. When the first form tries to close and write back to the
table, the changes that were made by the second form are detected. This causes
the error to be returned.
Docmd.TransferText and Docmd.TransferSpreadSheet
These commands are used to export records to a text file or
an excel sheet with proper format and are not supported by Access ADP.
For the Docmd.TransferText Command we had to create a file
system object and use that to write the whole record into a text file. The
record was fetched by executing a view or stored procedure. All the columns of
each record were written into the text file using a loop. The CreateTextFile
and Writeline function of the file system object were used to write the records
into a specific text file in a specified location.
Listing 5
In Access:
DoCmd.TransferText acExportDelim, , "QueryName", sPath & "\FileName.txt", True, , 65001
Modified Code:
Dim CommandText As String
Dim rs As New ADODB.Recordset
Dim strMsg As String
Dim strText As String
Dim strFileText As String
Dim strHeaderText As String
Dim fs
Dim a
CommandText = "Exec QueryName '" & parameter& "'"
rs.Open CommandText, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdText
If rs.RecordCount = 0 Then
DoCmd.Hourglass False
MsgBox "No Records to Export!"
Exit Sub
End If
strHeaderText = strHeaderText & """FieldName1""" & ","
strHeaderText = strHeaderText & """FieldName2"""
strFileText = strFileText & strHeaderText & vbCrLf
rs1.MoveFirst
Do While Not rs.EOF
strText = strText & """" & rs1 ("FieldName1") & """" & ","
strText = strText & """" & rs1("FieldName2") & """"
strFileText = strFileText & strText & vbCrLf
strText = ""
rs.MoveNext
Loop
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile(sPath & "\FileName.txt", True)
a.WriteLine (strFileText)
a.Close
rs.Close
Set rs = Nothing
Docmd.TransferSpreadSheet:
For this command also we changed the syntax accordingly.
In Access:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QueryName", sFilename, True
Modified Code:
DoCmd.OutputTo acOutputServerView, _
“ViewName ", acFormatXLS, sFilename, False
OR
DoCmd.OutputTo acOutputStoredProcedure, _
"StoredProcedureName ParameterName", acFormatXLS, sFilename, FalseDocmd.OpenForm Taking Parameter
Docmd.OpenForm taking input parameters was not supported in
access ADP. We had to convert the record source of the form to a view. Only
then did this command work.
DoCmd.OpenForm FormName, , , Condition
Print Functionality
In the print functionality we faced the problem of Header
Record Delimiter Error. For that, a header file with all the columns with
appropriate delimiters had to be written. We added the header file with “,” as
field delimiter and “enter” as record delimiter and that solved our problem.
Updatable View
If the record source of a form is not an updatable view then
one cannot edit any field of the form. And the status bar always shows the
error message “The Recordset is Not Updatable.” Tables without unique index
will be upsized, but they will be read-only in SQL Server. We added a unique
index to each of the tables used and made the required view updatable. The
view should not contain the following things to be updatable.
- A Set Operator (Union, Intersect, Minus, Union All)
- Distinct Operator
- Aggregate Functions (Sum, Avg, Count, etc.)
- A Group By Clause
- The select list with components other than column
specification or more than one specification of the same column.
Error While Inserting Records to a Form
Sometimes when adding a record to an ADP form an error
message appears:
“The data was added to the database but the data won't be
displayed in the form because it doesn't satisfy the criteria in the underlying
record source.”
For this we have to set the ResyncCommand Property of the
Form in the design view to a SQL statement.
For Example: Resync Command: Select * From tblName where
FieldName = ?
If the Rowsource contains a stored procedure with multiple
tables joined together then the Resync Command Property of the form should be
set to an SQL statement that selects the same fields as the stored procedure
and parameterize the primary key of the table that is designated as the Unique
Table.
