Solved dao connection to access 2007 database with attachment field

Posted on 2011-10-06
Last Modified: 2012-06-21
I am working on an project in VS2010 using vb.NET (not VBA) designing an interface to allow a user to upload a file to an access 2007 table attachment field. My understanding is that in order to interface with a table with an attachment field you must use DAO to connect to the database so that you can expose the attachment field properties.  When I try to open the DAO recordset I am getting "COM exception was unhandled by user code" with a message stating "Too few parameters. Expected 3".  The error code is -2146825227 and the stacktrace message is "at dao.Database.OpenRecordset(String Name, Object Type, Object Options, Object LockEdit)"  

I have also added the Microsoft.Office.Interop.Access. and Imports Microsoft.Office.Interop.Access references to the project and have these imports specified on the page:
Imports Microsoft.Office.Interop.Access.Dao
Imports Microsoft.Office.Interop.Access
Imports System.Net.Mail

Below is the code that I am trying to use:

 ' Set up DAO database connection
        Dim strSQL As String = "SELECT Quarterly_Due.DueDate, Quarterly_Due.Register, Quarterly_Due.QMRP_Name, Quarterly_Due.Review_Upload, Quarterly_Due.Uploader, Quarterly_Due.CheckInTime, Quarterly_Due.CheckInFlg FROM(Quarterly_Due) WHERE (((Quarterly_Due.DueDate)='" & DueDate_var & "') AND ((Quarterly_Due.Register)='" & RegNum_var & "'));"
        Dim dbe = New DBEngine()
        Dim db As dao.Database
        Dim rst As dao.Recordset
        Dim rstChld As dao.Recordset
        Dim fldAttach As Field2
        Dim fldCheckFlg As Field
        Dim fldTimeStamp As Field
        db = dbe.OpenDatabase("C:\Inetpub\wwwroot\QMRP_Quarterly_Check\App_Data\QMRP_Quarterly.accdb", False, False, "")
        rst = db.OpenRecordset(strSQL, dao.RecordsetTypeEnum.dbOpenDynaset, dao.RecordsetOptionEnum.dbConsistent, dao.LockTypeEnum.dbOptimistic)  <<--------  This is the line where the exception is thrown
        rstChld = rst.Fields("Review_Upload").Value
        fldAttach = rstChld.Fields("FileData")
        rst.Fields("CheckInTime").Value = Now()
        rst.Fields("CheckInFlg").Value = True
        rst.Fields("UpLoader").Value = UpLoader_var

The sql statement executes without error in the Access query design window and enumerates to:

"SELECT Quarterly_Due.DueDate, Quarterly_Due.Register, Quarterly_Due.QMRP_Name, Quarterly_Due.Review_Upload, Quarterly_Due.Uploader, Quarterly_Due.CheckInTime, Quarterly_Due.CheckInFlg FROM(Quarterly_Due) WHERE (((Quarterly_Due.DueDate)='10/13/2011') AND ((Quarterly_Due.Register)='10659'));"

I have played around with modifying that and removing the reference to the attachment field (Review_Upload) in the sql statement, but I continue to get the exception message, but the number of parameters missing message will change from 3 to 1.  At this point I am not sure whether I am dealing with a problem with the sql statement or a syntax error in the DAO connection code.  I have scoured all of the resources that I can find but have not been able to resolve the problem. Any assistance would be greatly appreciated.  
Question by:allej0ly
    LVL 83

    Expert Comment

    Do you have to use Access? And do you have to store file in Access? One option is to store only path of file in Access.

    Accepted Solution

    Error was in the namespace in the opendatabase statement:
    was pointing to a directory outside of the solution.  Correcting the path resolved the issue.

    Author Closing Comment

    Found solution

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
    The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now