Pass parametres from Excel to Access query

[Andrew]

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

 

[Dick Kusleika]

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...


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

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]

Thanks Dick, 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.