Access and parameters ...I really need the help!

[cindyray]

First, I do not have the developers edition of Crystal and because of budget restrictions, I won't be getting it. I do however, have Access. How can I pass parameters to an Access Query that depends on the parameters (dates) to be in the header? I've been through every book I have and can't come up with the solution. I'd really appreciate a response.

 

[keamosabei]

Why dont you try creating a temporary table and insert the records there and include the parameter dates when you insert. That way you could use the temporary table instead to create your report.

hope this helps.

 

[cindyray]

That's what I'm doing now, but I have a number of various reports to do this with and was concerned with having to compact the database alot.

 

[siqual]

There are several ways to pass parameters to Access. How you do depends on whether you are using DAO or ADO to access the database. That is the first thing. One possible solution is to create a parameterized query in access and pass the parameters to the query. Here is a code snippet using ADO to pass two parameters:

Dim cmdQuery As ADODB.Command
Dim rstInfo As ADODB.Recordset
Dim prmQuery As ADODB.Parameter
Dim i As Long, lTemp As Long
Dim dv As Date

Set cmdQuery = New ADODB.Command
With cmdQuery
.ActiveConnection = cnnToDB
.CommandText = "StoredParameterizedQuery"
.CommandType = adCmdStoredProc
Set prmQuery = .CreateParameter("ID&quot
With prmQuery
.Direction = adParamInput
.Type = adInteger
.Value =ID
End With
.Parameters.Append prmQuery
Set prmQuery = .CreateParameter("AnotherOne&quot
With prmQuery
.Direction = adParamInput
.Type = adChar
.Size = Len(Text)
.Value = Text
End With
.Parameters.Append prmQuery
End With

Set rstInfo = New ADODB.Recordset
With rstInfo
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open cmdQuery
.
.
.
End With

Note: with ADO you need to specify the cursor type in order to write to the database. The deault is a forward only cursor which does NOT allow you to write to the database.

Hope this helps some. Dan Grogan
dan@siqual.com

"Absit prudentia nil rei publicae profitur."
Without common sense you ain't gonna have nothing.

 

[cindyray]

Thank you, that helps alot. I am using ado.