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

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. 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.

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:

   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()