VB .NET :: Using OleDB Connection to Pass Parameters Into a mdb Query

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place!

Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and looks like this:

qryCust:
SELECT DISTINCT Customers.CustomerID FROM SalesItem
WHERE (((SalesItems.Name)="Redeemed" Or (SalesItems.Name)="Issued") AND ((Transact.DateStamp)>DateAdd('h',28,[?])));

This query takes a Date parameter that needs to be supplied when the query is run. So if you're writing a program in VB .NET that calls this query you need to somehow provide the parameter also. So to do this you need an OleDBCommand object and then you can add parameters onto your query.

The solution to writing a query that uses this pre-existing query will need to look like the below code:

Private Function queryDB(ByVal sql As String, ByVal tbl_qry As String) As DataSet
                              Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & databasePath
                              Dim oleConn As New OleDb.OleDbConnection(connStr)
                              ds = New DataSet
                              Dim dsAdapter As New OleDb.OleDbDataAdapter(sql, oleConn)
                              'dsAdapter.Fill(ds, tbl_qry)
                      
                              Dim cmd As System.Data.OleDb.OleDbCommand = New System.Data.OleDb.OleDbCommand(sql, oleConn)
                              dsAdapter.SelectCommand = cmd
                      
                              ' Add parameters and set values.
                              Dim inactiveDate As String = Format(dtp.Value, "M/d/yyyy")
                              cmd.Parameters.Add("@Date", System.Data.OleDb.OleDbType.VarChar, 15).Value = inactiveDate
                              cmd.Parameters(0).Direction = ParameterDirection.Input
                              dsAdapter.Fill(ds, tbl_qry)
                              Return ds
                          End Function
                      

Select all Open in new window

This supplies the query in Access with the required Date parameter while allowing also another query to be written on top!