?
Solved

Which Access 2007 References are needed when using DAO?

Posted on 2012-09-18
20
Medium Priority
?
749 Views
Last Modified: 2012-10-06
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.
0
Comment
Question by:newbie46
  • 7
  • 6
  • 4
  • +2
20 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38409529
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
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 500 total points
ID: 38409537
>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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38409542
>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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:newbie46
ID: 38409565
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38409573
Most definately.  If you don't have a reference set to DAO object library, Access VBA can't use it.
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 500 total points
ID: 38409588
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
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 38409599
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
 

Author Comment

by:newbie46
ID: 38409673
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
 
LVL 58
ID: 38409820
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
 
LVL 58
ID: 38409831
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
 

Author Comment

by:newbie46
ID: 38410016
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
 

Author Comment

by:newbie46
ID: 38410070
Could this reference be causing a problem?

Microsoft Visual Basic for Applications Extensibility 5.3
0
 
LVL 58
ID: 38410109
<<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
 

Author Comment

by:newbie46
ID: 38410174
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
 
LVL 21

Expert Comment

by:mastoo
ID: 38410210
It could be the insert or the delete.  Are TempData and COS tables or queries?
0
 
LVL 58
ID: 38410216
<<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
 

Author Comment

by:newbie46
ID: 38410580
mastoo,
TempData  is a local table. CSO is a linked table.
0
 

Author Comment

by:newbie46
ID: 38410584
Jim,
CSO is a linked table and it can be manually updated.
0
 
LVL 58
ID: 38410692
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
 
LVL 21

Accepted Solution

by:
mastoo earned 500 total points
ID: 38411297
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question