Excel VBA DAO - How to open existing Access Query and pass Parameter

[matlong30]

Hi there peoples,

First ever post on Tek-tips, so after struggling with this for a couple of days and searching the web, I thought I would try this.

How can you use VBA in Excel to open an existing MS Access Query "qryEquityP" (set up to accept user supplied perameters "PDate" [ISO Date field YYYYMMDD ie "20031212"] and "Ticker" [ie "TSCO"])??

This should return just one value in my pre-established query. Normally I use SQL to create a new query, but I just wanted to see if there was an effecient way to call this Query (set up to accept the user-defined parameter through the use of "[]" in the field).

Sub GetFilesDAOViaQuery()   
  Dim db As DAO.Database   
  Dim qd As DAO.QueryDef   'pre-defined Query
  Dim rs As DAO.Recordset
  Dim p As DAO.Parameter  'Set up the parameter
  
  p = 20031212
  Set db = OpenDatabase("Y:\Portfolio.mdb", False, False)
  Set qd = db.QueryDefs("qryEquityP")

end sub

Any coding example would be much appreciated!!

Thanks Matt

 

[SkipVought]

Hi,

The only way that I know of passing parameters to a query via VBA, is to construct the SQL string on the fly. True parameter queries are designed NOT to use VB code.

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[matlong30]

Hi Skip,

This is not exactly true, as I have succeded in doing it in ADO, however it is so messy I am looking for improvements to my very clumsy code, and also way to do it in DAO...

Public Sub GetFilesADOViaQuery()
'   variables
    Dim db As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rs As ADODB.Recordset
    Dim prm As ADODB.Parameter
    Dim rngTarget As Range

    'Open the database
    Set db = New ADODB.Connection
    db.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='Y:\Personal\Portfolio\Portfolio Performance.mdb'; User Id=admin; Password=;"
    Set rs = New ADODB.Recordset
    
    'get the stored proc (query) to append to error log
    Set cmd = New ADODB.Command
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "qryEPricesByDate"    '"qappData"
    cmd.ActiveConnection = db
    
    Set prm = cmd.CreateParameter("Pdate", adDouble, adParamInput, , "20031218")
    Set rngTarget = ThisWorkbook.Worksheets("Pricedate").Range("H4")
    
    cmd.Parameters.Append prm

    'Get the data from the query into the recordset
    rs.Open cmd.Execute
    rngTarget.CopyFromRecordset rs
    
    'cleanup
    db.Close
    Set db = Nothing
    Set rs = Nothing
    Set prm = Nothing
    Set cmd = Nothing

End Sub

 

[RoyVidar]

True parameter queries are designed NOT to use VB code." - on the contrary, lot of people recommend such approaches, which can often be faster than firing off dynamic SQL, due to some precompilation in stored queries. It has other advantages too ;-)

With your existing code, lets say your parameters are named [prmPdate] and [prmTicker] (pehaps you've also declared them in the query - query menu or Parameters [prmDate] DateTime, ...) you can do it as simple as this:

[tt]Set qd = db.QueryDefs("qryEquityP")
qd.parameters("[prmPdate]").value = dtYourDate
qd.parameters("[prmTicker]").value = strYourTicker[/tt]

Doing it in code vs having the query pop up those dreaded parameter boxes, allows for somewhat better datavalidation too.

Roy-Vidar

 

[matlong30]

Hi Roy,

Very embarressed here buddy - but I am having so many problems with this code! I have not used parameters in DAO before, and the MS Excel help on it seems pretty scratchy.

The code I have down blow does not work in the slightest, and keeps on crashing - If you can tell me the steps I am not doing properly... I would be eternally grateful!

Thanks Matt

Sub GetFilesDAOViaQuery()   'Function to extract a set of data using a pre-defined query
  Dim db As DAO.Database   'Declare Database variable of type DAO format - Early binding
  Dim qd As DAO.QueryDef   'Name of the pre-defined Query
  Dim rs As DAO.Recordset
  Dim PDate As DAO.Parameter, Ticker As DAO.Parameter  'Set up the 2 parameters needed
  
  
  Set db = OpenDatabase("Y:\Personal\Portfolio\Portfolio Performance.mdb", False, False)
  'Set PDate.Value = "20031212"
  'Set up the existing Access query to be used
  Set qd = db.QueryDefs("qryEPrices_TickerDate")
  Set qd.Parameters("[PDate]").Value = "20031212"
  qd.Parameters("[Ticker]").Value = "ANZ"
 
  
  Set rs = qd.OpenRecordset

 

[RoyVidar]

Shouldn't be a need for the set statement, just

[tt]qd.Parameters("[PDate]").Value = "20031212"
qd.Parameters("[Ticker]").Value = "ANZ"[/tt]

But this would probably rely on the stored query looking something like this

[tt]parameters [PDate] Text(8), [Ticker] Text(3);
Select blah ....
WHERE myDateDield = [PDate] and myTickerField = [Ticker][/tt]

(with correct field size) probably the field names and paramter names need to be different - so if your field name is PDate, use for instance [prmPDate] for the parameter.

You should probably have the file DAO360.chm on your harddrive - if you look up Parameter there, you should find something ;-)

Roy-Vidar