Which Access 2007 References are needed when using DAO?

Within an Access 2007 database, I am receiving 'Operation must use an Updateable query' message in code where there is not an update query. The code does contain db.Execute "INSERT INTO ...." and I set db using the following statements:
Dim db As DAO.Database
Dim rst As DAO.Recordset

set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

I am wondering if there is a problem with the references that are set. The database generates emails and imports data from Excel and Word.

Which references are needed and in what order should they occur?

I currently have the following references selected (and am thinking that the reference to Access database engine Object Library should not be selected):

Visual Basic For Applications
Microsoft Access 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Access database engine Object Library
Microsoft Visual Basic for Applications Extensibility 5.3
Microsoft Office 12.0 Object Library
Microsoft Excel 12.0 Object Library
Microsoft Word 12.0 Object Library

thanks.
newbie46Asked:
Who is Participating?
 
mastooConnect With a Mentor Commented:
IIRC, Access 2007 you can do the ^G to get into code and then Tools - Options - Debug - break on all errors.  This will cause it to stop on the line that causes the error.  It might be nice to confirm the line causing the error although as Jim above says, none seem like they could cause this.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Microsoft DAO (some version number) Object Library

On my Vista/Office+Access 2010 box I'm using DAO 3.6
c:\Program Files (x86)\Common Files\microsoft shared\dao\dao360.dll

Note that the version of DAO is not directly dependant on the version of Access you're using.
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
>Which references are needed and in what order should they occur?
Once you add the DAO reference you should be able to run your code.

>Also, the order afaik is not important based on the references you chose.
Order may come into play if you have both ADO and DAO; as if your code doesn't specify which one it'll choose the one highest in the order.

Dim rs as ADODB.Database     'This will use ADO
Dim rs as DAO.Database          'This will use DAO
Dim rs as Database                   'This will use whatever is highest in Tools:References
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>am thinking that the reference to Access database engine Object Library should not be selected
The following references are required for Access 12 to work, so do not un-select them:

Visual Basic For Applications
Microsoft Access 12.0 Object Library
OLE Automation     'Not abundantly sure about this one
0
 
newbie46Author Commented:
Thanks, JimHorn, for your responses. Do you think that the references could be the cause of the 'Operation must use an Updateable query'  error?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Most definately.  If you don't have a reference set to DAO object library, Access VBA can't use it.
0
 
peter57rConnect With a Mentor Commented:
Not in A2007.

The Access database engine includes DAO. There is no need for a separate reference.

Please post the full sql of your query.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor President / OwnerCommented:
This:

Microsoft Office 12.0 Access database engine Object Library

 is the correct reference for DAO in Access 2007 and up.  Your references are correct and the order doesn't matter unless you don't delcare things explicitly as Jim H pointed out.

<< Do you think that the references could be the cause of the 'Operation must use an Updateable query'  error? >>

  What are you doing with this recordset.  One thing is on the open, you've specified dbOpenDynaset, which means you want an updateable recordset.

Jim.
0
 
newbie46Author Commented:
JDettman,
Here is the code:

Dim fDialog As Office.FileDialog
Dim strPath As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim RecordCounter As Long

Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
Set db = CurrentDb()

'--------------------------------------------------------------------------------------------------------------------------------
'Select File
'--------------------------------------------------------------------------------------------------------------------------------
strPath = ""
If fDialog.Show = True Then
  strPath = fDialog.SelectedItems(1)
End If
 
If Len(strPath) = 0 Then
  GoTo Exit_ImportData_Click
End If

Me.PleaseWaitMsg.Visible = True
'--------------------------------------------------------------------------------------------------------------------------------
'Determine whether Workbook is Open
'--------------------------------------------------------------------------------------------------------------------------------
Dim Wkb As Workbook
   
On Error Resume Next ' In Case it isn't Open

Set Wkb = Workbooks(strPath)
   
If Not Wkb Is Nothing Then
  Wkb.Close SaveChanges:=False
End If
           
On Error GoTo Err_ImportData_Click

Me.PleaseWaitMsg.Visible = True

db.Execute "DELETE * FROM TempData"
db.Execute "DELETE * FROM CSO"
 
gImportType = "ddd"
DoCmd.OpenForm "frmImportProgress", , , stLinkCriteria
DoCmd.MoveSize 1000, 1000, 13000, 9500

Forms!frmImportProgress!ProgressMsg1.Visible = True
Forms!frmImportProgress!ProgressBar1.BackStyle = 1

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TempData", strPath, False

db.Execute "DELETE * FROM TempData WHERE F1='Purchase_Order_No' OR F1='Purchase Order No'"

Forms!frmImportProgress!ProgressMsg2.Visible = True
Forms!frmImportProgress!ProgressBar2.BackStyle = 1
     
RecordCounter = 1

strSQL = "SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14 " & _
         "FROM TempData "
         
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
If Not (rst.BOF And rst.EOF) Then
  Do While Not rst.EOF
    If RecordCounter = 50000 Then
      Forms!frmImportProgress!ProgressMsg2a.Visible = True
      Forms!frmImportProgress!ProgressBar2a.BackStyle = 1
    ElseIf RecordCounter = 100000 Then
      Forms!frmImportProgress!ProgressMsg2b.Visible = True
      Forms!frmImportProgress!ProgressBar2b.BackStyle = 1
    ElseIf RecordCounter = 150000 Then
      Forms!frmImportProgress!ProgressMsg2c.Visible = True
      Forms!frmImportProgress!ProgressBar2c.BackStyle = 1
    ElseIf RecordCounter = 200000 Then
      Forms!frmImportProgress!ProgressMsg2d.Visible = True
      Forms!frmImportProgress!ProgressBar2d.BackStyle = 1
    ElseIf RecordCounter = 250000 Then
      Forms!frmImportProgress!ProgressMsg2e.Visible = True
      Forms!frmImportProgress!ProgressBar2e.BackStyle = 1
    ElseIf RecordCounter = 300000 Then
      Forms!frmImportProgress!ProgressMsg2f.Visible = True
      Forms!frmImportProgress!ProgressBar2f.BackStyle = 1
    ElseIf RecordCounter = 350000 Then
      Forms!frmImportProgress!ProgressMsg2g.Visible = True
      Forms!frmImportProgress!ProgressBar2g.BackStyle = 1
    ElseIf RecordCounter = 400000 Then
      Forms!frmImportProgress!ProgressMsg2h.Visible = True
      Forms!frmImportProgress!ProgressBar2h.BackStyle = 1
    ElseIf RecordCounter = 450000 Then
      Forms!frmImportProgress!ProgressMsg2i.Visible = True
      Forms!frmImportProgress!ProgressBar2i.BackStyle = 1
    ElseIf RecordCounter = 500000 Then
      Forms!frmImportProgress!ProgressMsg2j.Visible = True
      Forms!frmImportProgress!ProgressBar2j.BackStyle = 1
    ElseIf RecordCounter = 550000 Then
      Forms!frmImportProgress!ProgressMsg2k.Visible = True
      Forms!frmImportProgress!ProgressBar2k.BackStyle = 1
    ElseIf RecordCounter = 600000 Then
      Forms!frmImportProgress!ProgressMsg2l.Visible = True
      Forms!frmImportProgress!ProgressBar2l.BackStyle = 1
    ElseIf RecordCounter = 650000 Then
      Forms!frmImportProgress!ProgressMsg2m.Visible = True
      Forms!frmImportProgress!ProgressBar2m.BackStyle = 1
    ElseIf RecordCounter = 700000 Then
      Forms!frmImportProgress!ProgressMsg2n.Visible = True
      Forms!frmImportProgress!ProgressBar2n.BackStyle = 1
    ElseIf RecordCounter = 750000 Then
      Forms!frmImportProgress!ProgressMsg2o.Visible = True
      Forms!frmImportProgress!ProgressBar2o.BackStyle = 1
    ElseIf RecordCounter = 800000 Then
      Forms!frmImportProgress!ProgressMsg2p.Visible = True
      Forms!frmImportProgress!ProgressBar2p.BackStyle = 1
    ElseIf RecordCounter = 850000 Then
      Forms!frmImportProgress!ProgressMsg2q.Visible = True
      Forms!frmImportProgress!ProgressBar2q.BackStyle = 1
    ElseIf RecordCounter = 900000 Then
      Forms!frmImportProgress!ProgressMsg2r.Visible = True
      Forms!frmImportProgress!ProgressBar2r.BackStyle = 1
    ElseIf RecordCounter = 950000 Then
      Forms!frmImportProgress!ProgressMsg2s.Visible = True
      Forms!frmImportProgress!ProgressBar2s.BackStyle = 1
    End If
     
    'Check whether Transaction Date contains an actual date
    If Nz(rst!F14) = "" Or IsDate(rst!F14) Then
      'Do nothing
    Else
      MsgBox ("Import Aborted. PO: " & Nz(rst!F1) & " has a non-date value in its TDate field.")
      GoTo Exit_ImportData_Click
    End If
   
    'Determine whether Project Period contains 2 dates
    ProjectPeriodStartDate = Null
    ProjectPeriodEndDate = Null
     
    If Len(rst!F6) = 11 Then
      ProjectPeriodStartDate = Mid(rst!F6, 1, 10)
      ProjectPeriodEndDate = Null
    ElseIf Len(rst!F6) = 21 Then
      ProjectPeriodStartDate = Mid(rst!F6, 1, 10)
      ProjectPeriodEndDate = Mid(rst!F6, 12, 10)
    End If
   
    db.Execute "INSERT INTO CSO (PurchaseOrderNo, AAAAA, BBBB, CCCC, DDDD, ProjectPeriod, ProjectPeriodStartDate, " & _
                         "ProjectPeriodEndDate, EEEE, FFFF, GGGG, HHHH, IIII, JJJJ, KKKK,
llll ) " & _
               "VALUES ('" & Nz(rst!F1, "") & "', '" & Nz(rst!F2, "") & "', '" & Nz(rst!F3, "") & "', '" & Replace(Nz(rst!F4, ""), "'", "''") & _
                   "', '" & Nz(rst!F5, "") & "', '" & Nz(rst!F6, "") & "', '" & ProjectPeriodStartDate & "', '" & ProjectPeriodEndDate & "', '" & _
                   Nz(rst!F7, "") & "', '" & Nz(rst!F8, "") & "', '" & Nz(rst!F9, "") & "', '" & Nz(rst!F10, "") & "', '" & Nz(rst!F11, 0) & "', '" & _
                   Nz(rst!F12, 0) & "', '" & Nz(rst!F13, 0) & "', '" & CDate(Nz(rst!F14, "01/01/1900")) & "')"
   
    RecordCounter = RecordCounter + 1
    rst.MoveNext
  Loop
End If
rst.Close

'Delete all ImportError tables
Dim tbldef As TableDef
For Each tbldef In db.TableDefs
  If InStr(1, tbldef.Name, "ImportError") > 0 Then
    DoCmd.DeleteObject acTable, tbldef.Name
  End If
Next tbldef
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
And you get the error on what line?

couple other comments.  On this:

    db.Execute "INSERT INTO CSO (PurchaseOrderNo

add error handling if you don't have it and add:

  , dbFailOnError

to that statement.  That way, if you have an error on the insert, you'll know it.

 On this:

If RecordCounter = 50000 Then
      Forms!frmImportProgress!ProgressMsg2a.Visible = True
      Forms!frmImportProgress!ProgressBar2a.BackStyle = 1
    ElseIf RecordCounter = 100000 Then
      Forms!frmImportProgress!ProgressMsg2b.Visible = True
      Forms!frmImportProgress!ProgressBar2b.BackStyle = 1

 no clear on eaxactly what it is your trying to do, but seems like there'd be a better way then all those if's.   I've attached a sample DB with various progress bars.

WIth that, you can often update a progress bar by using MOD, which let's you do something every nth interation of a loop.

MOD returns the remainder, so if you do   x MOD 10 and x is 10,20,30,40,50 you get zero back because x is evenly divisible by 10.

So a check of:

  If x MOD 10 = 0 then
      ' Update the progress bar
 End If

On this:

If Not (rst.BOF And rst.EOF) Then
  Do While Not rst.EOF


 You don't need both checks.    rst.EOF will be true on a blank file right off and your loop won't execute.

HTH,
Jim.
ProgressBar.zip
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Also on the error handling, something that is very helpful is to number your code with:

http://mztools.com/v3/download.aspx

(free) and then use VBA.ERL as part of your error handler.

This pin-points the exact line of code that caused an error.

Jim.
0
 
newbie46Author Commented:
JDettman,
I think that the error is occuring in the Insert Into stmt. I will have to rerun the import when I am back in the office tomorrow.

My error handling is as follows:

Exit_ImportData_Click:
  Set fDialog = Nothing
  Set db = Nothing
  Me.PleaseWaitMsg.Visible = False
  DoCmd.Close acForm, "frmImportProgress"
  Exit Sub
 
Err_ImportData_Click:
 MsgBox Err.Number & vbCrLf & Err.Description, , "ImportData_Click"
 Resume Exit_ImportData_Click

1) Do I need the, dbFailOnError in the above error handling? Or is what I have sufficient?

"One thing is on the open, you've specified dbOpenDynaset, which means you want an updateable recordset."
2) Should I be using dbOpenDynaset in my code or should it be removed?

3) With reference to using VBA.ERL as part of your error handler, I cannot freely download tools to my machine. I would need to check with IT concerning using this tool.

4) Looking at my code, can you see what could be causing the 'Operation must use an Updateable query'  error?
0
 
newbie46Author Commented:
Could this reference be causing a problem?

Microsoft Visual Basic for Applications Extensibility 5.3
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<1) Do I need the, dbFailOnError in the above error handling? Or is what I have sufficient?>>

Your error handling is fine. The dbFailOnError doesn't go in the error handling, but on the db.Execute statement after the SQL argument.   By specifying that, your error handler will be fired if there is any problem with the insert statement.

You could add a bit to your cleanup at exit however.  When you exit, you should close anything you opened and set it to nothing.  One thing I noticed is the rst is missing, so you should be doing:

Exit_ImportData_Click:
  On Error resume next

 If not rst is nothing then
  rst.close
  set rst = nothing
End If

 Set fDialog = Nothing
 Set db = Nothing

  Me.PleaseWaitMsg.Visible = False
  DoCmd.Close acForm, "frmImportProgress"

  Exit Sub
 
Err_ImportData_Click:
 MsgBox Err.Number & vbCrLf & Err.Description, , "ImportData_Click"
 Resume Exit_ImportData_Click


<<2) Should I be using dbOpenDynaset in my code or should it be removed?>>

 Doesn't matter.  Your not performing any operation against the rst that I can see other then reading it.

<<3) With reference to using VBA.ERL as part of your error handler, I cannot freely download tools to my machine. I would need to check with IT concerning using this tool.>>

 It would be worth checking into with IT.  MZ Tools has been available as freeware for years and many will vouch for it.  The line numbering cabability is just a small part of what it does.

 Between that and Smart Identer, you have a good toolbox for VBA programming.

 The line number function is worth it's weight in gold alone as when coupled with VBA.ERL in the error handlers, you can save hours pin-pointing where errors occur.  Here's one of my handlers:

Error_AppMain:
840       UnexpectedError ModuleName, RoutineName, Version, Err.Number, Err.Description, Err.Source, VBA.Erl
850       Resume Exit_AppMain


 UnexpectedError being a procedure to log and optionaly e-mail me with all the info.  ModuleName, RoutineName, and Version are all constants in the code.  Here's an example of what I receive:


An application event was logged:

Msgbox Title: Order Processor Ver 1.6.8 - Unexpected error

Message:
The application has encountered an unexpected error.
App Name: Order Processor    Version: 1.6.8

Station Name: ICPAPP01     User Name: srvAutoTask

Module: OCS_RecordFunctions     Routine: OpenADORecordsetOnDSN     Version: 1.0.0

Error Number: -2147217871
Description: [Microsoft][ODBC SQL Server Driver]Timeout expired

Source: Microsoft OLE DB Provider for ODBC Drivers      Line number: 50

<<) Looking at my code, can you see what could be causing the 'Operation must use an Updateable query'  error? >>

 I was thinking the insert, but I didn't believe I was looking at all the code.  And BTW, one other tip on that, code it like this:


 Dim strSQL as string

    strSQL = "INSERT INTO CSO (PurchaseOrderNo, AAAAA, BBBB, CCCC, DDDD, ProjectPeriod, ProjectPeriodStartDate, " & _
                         "ProjectPeriodEndDate, EEEE, FFFF, GGGG, HHHH, IIII, JJJJ, KKKK,
llll ) " & _
               "VALUES ('" & Nz(rst!F1, "") & "', '" & Nz(rst!F2, "") & "', '" & Nz(rst!F3, "") & "', '" & Replace(Nz(rst!F4, ""), "'", "''") & _
                   "', '" & Nz(rst!F5, "") & "', '" & Nz(rst!F6, "") & "', '" & ProjectPeriodStartDate & "', '" & ProjectPeriodEndDate & "', '" & _
                   Nz(rst!F7, "") & "', '" & Nz(rst!F8, "") & "', '" & Nz(rst!F9, "") & "', '" & Nz(rst!F10, "") & "', '" & Nz(rst!F11, 0) & "', '" & _
                   Nz(rst!F12, 0) & "', '" & Nz(rst!F13, 0) & "', '" & CDate(Nz(rst!F14, "01/01/1900")) & "')"

  db.Execute strSQL, dbFailOnError

 This let's you put a breakpoint or stop before the execute and inspect the SQL statement as Access/Jet sees it and spot syntax problems easily.

Jim.
0
 
newbie46Author Commented:
Thanks for the suggestions.

" I was thinking the insert, but I didn't believe I was looking at all the code"
Are you not sure what is causing the 'Operation must use an Updateable query' error based on seeing my code?
0
 
mastooCommented:
It could be the insert or the delete.  Are TempData and COS tables or queries?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Could this reference be causing a problem?

Microsoft Visual Basic for Applications Extensibility 5.3 >>

No.  Only possibility would be if you had both ADO and DAO libs loaded and you were not being explicit in Dim'ing (ie.    rst as recordset vs  rst as DAO.Recordset).  As Jim H said, you then leave it up to the order of references as to what your refering to (if ADO was first and DAO second, or reveresed) because both ADO and DAO have a recordset object and they are different.

 But you don't have a ADO reference, so that's not the issue.

 You should check that your good though by doing a compile. If you have a clean compile,  then your good with the references as they stand.

As to the error, if you open CSO manually, can you update it?

Jim.
0
 
newbie46Author Commented:
mastoo,
TempData  is a local table. CSO is a linked table.
0
 
newbie46Author Commented:
Jim,
CSO is a linked table and it can be manually updated.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Well I don't see it then.

But as I said, implement dbfailOnError, stuff the SQL into a string before it gets executed, etc and you'll find it pretty quick.

 or you can place a STOP at the top of the procedure, execute it, then step through with Shift/F8 line by line until you hit the error.  That could be long though if it's the insert statement at the end of that loop<g>.

Jim.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.