[Webinar] Learn how to a build a cloud-first strategyRegister Now


vb.net dao connection to access 2007 database with attachment field

Posted on 2011-10-06
Medium Priority
Last Modified: 2012-06-21
I am working on an asp.net 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
  • 2
LVL 83

Expert Comment

ID: 36925616
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

allej0ly earned 0 total points
ID: 36980768
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

ID: 37010538
Found solution

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Screencast - Getting to Know the Pipeline
Suggested Courses
Course of the Month20 days, 14 hours left to enroll

810 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