ACC97: IPF in Msjet35.dll Running Update Against Attached Table

• Microsoft Access 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you use a user-defined function in the Update To row of an update query based on an attached SQL Server table, you may receive the following error message:

   MSACCESS caused an invalid page fault in
   module MSJet35.DLL at 0137:040df209

RESOLUTION

There are two methods you can use to resolve this problem. In the first method, you create a make table query with a user-defined function to create a temporary table. You then create an update query based on the temporary table and the SQL server table. In the second method, you perform the update manually in code.

NOTE: Both methods assume you have an attachment to the Sales table in the PUBS sample database within Microsoft SQL Server and a function called Test(). To create the Test() function, follow these steps:

1. Create a module and type the following line in the Declarations section if the line is not already there:

         Option Explicit
   

2. Type the following procedure:

         Function Test(qtyVal As Integer) As Integer
            Test = qtyVal + 10
         End Function
   
   

3. Close and save the module as modTest.

Method 1

1. Create the following query based on the dbo_Sales table:

         Query: qryTemp
         -------------------------
         Type: Select
         Field: Stor_id
         Field: Ord_Num
         Field: Title_id
         Field: Expr1: Test([qty])
   

2. On the Query menu, click Make Table, and then in the Table Name box, type "tblTemp" (without the quotation marks). Click OK.

3. Save the query as qryTemp.

4. Run the qryTemp query and close it.

5. Create the following update query based on dbo_Sales and tbltemp tables:

         Query: qryUpdate
         -------------------------------------------------
         Type: Update
         Join: tblTemp.[Stor_id] <-> dbo_Sales.[Stor_id]
         Join: tblTemp.[Ord_num] <-> dbo_Sales.[Ord_num]
         Join: tblTemp.[Title_id] <-> dbo_Sales.[Title_id]
         Field: [qty]
            Table: dbo_Sales
            Update To: [tblTemp].[Expr1]
   
   

6. Save the query as qryUpdate

7. Run the query to update the qty field in the Sales SQL attached table.

Method 2

1. Create a module and type the following line in the Declarations section if the line is not already there:

         Option Explicit
   

2. Type the following procedure:

         Function UpdateId()
            Dim MyDb as Database
            Dim MyRS as RecordSet
            Dim MyVar as Long
            Set MyDb = CurrentDB()
            Set MyRS = MyDB.OpenRecordset("dbo_sales",dbOpenDynaset)
            MyRS.MoveFirst
            Do While Not MyRS.EOF
               MyVar = Test([MyRS![qty])
               MyRS.Edit
               MyRS![qty] = MyVar
               MyRS.Update
            Loop
            MyRS.Close
         End Function
   
   

3. Close and save this module as modUpdateSQL

4. To test this function, type the following line in the Debug window, and then press ENTER.

         ?UpdateID()
   

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 97. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Steps to Reproduce Problem