Microsoft KB Archive/163943

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

Article ID: 163943

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q163943



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



SYMPTOMS
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:   Create a module and type the following line in the Declarations section if the line is not already there: Option Explicit   Type the following procedure: Function Test(qtyVal As Integer) As Integer Test = qtyVal + 10 End Function  Close and save the module as modTest.

Method 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])  On the Query menu, click Make-Table, and then in the Table Name box, type tblTemp. Click OK.</li> Save the query as qryTemp.</li> Run the qryTemp query and close it.</li>  Create the following update query based on dbo_Sales and tbltemp tables: <pre class="fixed_text">  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] </li> Save the query as qryUpdate</li> Run the query to update the qty field in the Sales SQL attached table.</li></ol>

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
  Create a module and type the following line in the Declarations section if the line is not already there: Option Explicit </li>  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 </li> Close and save this module as modUpdateSQL</li> To test this function, type the following line in the Debug window, and then press ENTER:

?UpdateID

</li></ol>

<div class="status_section">

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

<div class="moreinformation_section">

Steps to Reproduce Problem
 Create a new database, and then on the File Menu, point to Get External Data, and click Link Tables.</li> From the Files Of Type list, click ODBC Databases, select the SQL Server Data Source, and then click OK.</li> Click Options and type Pubs in the Database box, and click OK.</li> Select the Sales table from the list, and then click OK.</li> <li> Create a module and type the following line in the Declarations section if the line is not already there: Option Explicit </li> <li> Type the following procedure: Function Test(MyVar as Long) as Long Test = MyVar /1 End Function </li> <li>Close and save the module as Module1.</li> <li> Create the following query based on the dbo_Sales table: <pre class="fixed_text">  Query: qryTest Type: Update Field: [qty] Update To: Test(qty) </li> <li>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.</li></ol>

Additional query words: MSACCESS caused an invalid page fault in module MSJet35 DLL msjet

Keywords: kbbug kberrmsg kbpending KB163943

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.