[RESOLVED] How to pass parameters to a query

[Webskater]

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?