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

ID: Q163943


The information in this article applies to:
  • 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:


  2. 
    Option Explicit 
  3. Type the following procedure:


  4. 
    Function Test(qtyVal As Integer) As Integer
       Test = qtyVal + 10
    End Function 
  5. Close and save the module as modTest.


Method 1

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


  2. 
       Query: qryTemp
       -------------------------
       Type: Select
       Field: Stor_id
       Field: Ord_Num
       Field: Title_id
       Field: Expr1: Test([qty]) 
  3. On the Query menu, click Make-Table, and then in the Table Name box, type tblTemp. Click OK.


  4. Save the query as qryTemp.


  5. Run the qryTemp query and close it.


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


  7. 
       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] 
  8. Save the query as qryUpdate


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


NOTE: Whenever you need to run the qryUpdate update query, you must first run the qryTemp query to generate an updated tblTemp table.

Method 2

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


  2. 
    Option Explicit 
  3. Type the following procedure:


  4. 
    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 
  5. Close and save this module as modUpdateSQL


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


  7. ?UpdateID()


STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 97. This problem no longer occurs in Microsoft Access 2000


MORE INFORMATION

Steps to Reproduce Problem

  1. Create a new database, and then on the File Menu, point to Get External Data, and click Link Tables.


  2. From the Files Of Type list, click ODBC Databases(), select the SQL Server Data Source, and then click OK.


  3. Click Options and type Pubs in the Database box, and click OK.


  4. Select the Sales table from the list, and then click OK.


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


  6. 
    Option Explicit 
  7. Type the following procedure:


  8. 
    Function Test(MyVar as Long) as Long
       Test = MyVar /1
    End Function 
  9. Close and save the module as Module1.


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


  11. 
       Query: qryTest
       --------------------
       Type: Update
       Field: [qty]
       Update To: Test(qty) 
  12. Close and save this query as qryTest.

    Note that when you run the qryTest update query, you receive the error message mentioned in the "Symptoms" section.


Keywords : kberrmsg QryUpdat
Version : 97
Platform : WINDOWS
Issue type : kbbug


Last Reviewed: November 5, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.