Pass parametres from Excel to Access query

How would I pass information from an Excel VBA module to an Access
query using ADO? At the moment I can run the report if I hard code the
parameters into the query but that is a bit pointless.

I run this report in Access at the moment and have a user form setup
that allows the user to specify their own parameters. The report is
then output to an Excel spreadhseet. As I intend to further manipulate
the data in Excel it would be better if Excel could be in charge from
the start.

I have included the VBA code below.



Public Sub SavedQuery()

Dim objField As ADODB.field
Dim rsData As ADODB.Recordset
Dim lOffset As Long
Dim sxConnect As String

'Create the connection string
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=J:\linktolive\linktolive.mdb;"

'Create the recordset object and run the query
Set rsData = New ADODB.Recordset

rsData.Open "[ADOFalkirkPriorityArrivals]", szConnect,
adOpenForwardOnly, _
adLockReadOnly, adCmdTable

'Make sure we get records back
If Not rsData.EOF Then

'Dump the contents of the recordset onto the worksheet
Sheet1.Range("A2").CopyFromRecordset rsData
'Fit the column widths to the data
Sheet1.UsedRange.EntireColumn.AutoFit
Sheet1.UsedRange.EntireRow.RowHeight = 20


Else
MsgBox "Error: No records returned.", vbCritical
End If

'Close the recordset
rsData.Close
Set rsData = Nothing


End Sub

Andrew:

Don't change the query in Access, recreate the SQL in Excel VBA. It might
look like this

> rsData.Open "[ADOFalkirkPriorityArrivals]", szConnect,
> adOpenForwardOnly, _
> adLockReadOnly, adCmdTable


Dim sSql As String
Dim sWhere As String

Const sFROM As String = "SELECT * FROM MyTable "

sWhere = "Where MyField=" & Range("A1").Value

sSql = sFROM & sWhere

rsData.Open sSql, szConnect, etc...


--
**** Kusleika
MVP - Excel
Daily Dose of Excel
http://www.*****-blog.com


Andrew wrote:
> How would I pass information from an Excel VBA module to an Access
> query using ADO? At the moment I can run the report if I hard code the
> parameters into the query but that is a bit pointless.
>
> I run this report in Access at the moment and have a user form setup
> that allows the user to specify their own parameters. The report is
> then output to an Excel spreadhseet. As I intend to further manipulate
> the data in Excel it would be better if Excel could be in charge from
> the start.
>
> I have included the VBA code below.
>
>
>
> Public Sub SavedQuery()
>
> Dim objField As ADODB.field
> Dim rsData As ADODB.Recordset
> Dim lOffset As Long
> Dim sxConnect As String
>
> 'Create the connection string
> szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=J:\linktolive\linktolive.mdb;"
>
> 'Create the recordset object and run the query
> Set rsData = New ADODB.Recordset
>
> rsData.Open "[ADOFalkirkPriorityArrivals]", szConnect,
> adOpenForwardOnly, _
> adLockReadOnly, adCmdTable
>
> 'Make sure we get records back
> If Not rsData.EOF Then
>
> 'Dump the contents of the recordset onto the worksheet
> Sheet1.Range("A2").CopyFromRecordset rsData
> 'Fit the column widths to the data
> Sheet1.UsedRange.EntireColumn.AutoFit
> Sheet1.UsedRange.EntireRow.RowHeight = 20
>
>
> Else
> MsgBox "Error: No records returned.", vbCritical
> End If
>
> 'Close the recordset
> rsData.Close
> Set rsData = Nothing
>
>
> End Sub

Thanks ****, I eventually managed to do it the way you said. The query
was a bit complicated so I had to go over it a few times to ensure the
syntax was correct but it works now.