[RESOLVED] How to pass parameters to a query

Hi

I read a very useful tutorial on here which has shown me how to connect to an access database using ADO.

This is a snippet of the code ...

Set cn = New ADODB.Connection
cn.ConnectionString= "Provider=Microsoft.Jet.OLEDB.4.0;"& "DataSource= C:\MyDocuments\Phaedrus\ShipData.mdb"
cn.Open
Set rs = New ADODB.Recordset
rs.Open "MakeWeightQry", cn, adOpenKeyset, adLockPessimistic, adCmdTable 'opening the recordset explained in the notes

... I have two questions.

Question 1
As I am going to be opening and closing recordsets, doing updates etc. all over the place - can I store the connection string globally so that, in the final version, I only have to change the path to the database in one place?

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= C:\MyDocuments\Phaedrus\ShipData.mdb"

I haven't got my head round the different types of modules. I put this ...

Dim MyConnection As String
MyConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= C:\Phantom\Phantom\PhoneData.mdb"

in a module called Module1.bas and tried to change the code that opens the recordset to:
cn.ConnectionString= MyConnection
but I get an error message that says 'Invalid Outside Procedure'. Can anyone explain how I can store the connection string globally? Thanks.

Question 2
How can I pass parameters to the Query?
rs.Open "MakeWeightQry", cn, adOpenKeyset, adLockPessimistic, adCmdTable

MakeWeightQry is a complex query with lots of joins etc ... Can I pass parameters to it without having to write the whole SELECT statement out concatenating the parameters into the string?

That code looks familiar, I presume this is Beacon's ADO tutorial (linked from our FAQ's, and my signature).


Question 1
The error message 'Invalid Outside Procedure' means that you have put code into a place that is not valid - the Declarations section at the top of the form (or module), which is only valid for declaration lines (such as Dim/Public/Private/Const). You need to place all code (such as MyConnection = ) inside a Sub or Function.

In basic terms, a Module is exactly the same as a Form, except it has no visual part (and also no events for the visual parts!), the way you put code in is exactly the same.


When you say you will be using the connection "all over the place" do you mean in just one form, or in multiple forms?

If it is just one form, then the method used in the tutorial is exactly what you need - the connection is set up once (when the form loads), and closed once (when the form closes).

If you mean multiple forms, then you need to make a few alterations. You should declaring the connection object (cn) in a module, and you should be using Public instead of Dim or Private. Doing this allows multiple forms to use the same connection, and so be open (working with data) at the same time.

You could declare MyConnection as a public Constant, but doing that means that the path of the database file can never be different - it must be in the folder "C:\Phantom\Phantom\", which may not be where your program has actually been installed (assuming that you are letting others have a copy of the program!). The usual way to set this up is via code (so you can use App.Path, or something else if apt).

What you should ideally do is set up the connection and connection string at the start of your program (so "Set cn = New ..." and "cn.ConnectionString= ..." and "cn.Open ..."), and close it at the end of your program ("Cn.close" and "Set cn = nothing"). Exactly where each of these snippets would go depends on the structure of your program. If you give us an explanation, we can suggest where these parts should go.


Question 2
Does MakeWeightQry actually have parameters? If not, you can't pass parameters to it.

If you have an Access query which accepts parameters, you can use something like this (obviously needs some adjustments for the relevant parameters):

VB Code:

Dim rs As ADODB.Recordset
Dim adoCommand As ADODB.Command
    Set adoCommand = New ADODB.Command
    With adoCommand
           'Replace adoConnection with the name of your connection object
        .ActiveConnection = adoConnection
        .CommandType = adCmdStoredProc 'since it is a Query/SP
           'Replace Query1 with the name of your Query/SP
        .CommandText = "Query1" 
        .Prepared = True
        .Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50, Data1)
        .Parameters.Append .CreateParameter(, adBoolean, adParamInput, , Data2)
        .Parameters.Append .CreateParameter(, adInteger, adParamInput, , Data3)
        .Parameters.Append .CreateParameter(, adDate, adParamInput, , Data4)
        Set rs = .Execute
    End With

Thanks for your reply. I didn't realise you could use stored procedures with an Access database.

Cheers