Which Access 2007 References are needed when using DAO?

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.

>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

Thanks, JimHorn, for your responses. Do you think that the references could be the cause of the 'Operation must use an Updateable query'  error?

Most definately.  If you don't have a reference set to DAO object library, Access VBA can't use it.

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

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

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.

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?

Could this reference be causing a problem?

Microsoft Visual Basic for Applications Extensibility 5.3

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

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?

It could be the insert or the delete.  Are TempData and COS tables or queries?

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

mastoo,
TempData  is a local table. CSO is a linked table.

Jim,
CSO is a linked table and it can be manually updated.

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.