I have a query in Access DB which returns record(s) for a given value by
popping up a windows(parameterised query).
I want to run this query from Excel to extract one or more values by:
--having input to the query from the active cell(where cursor is placed and
from where I run this query),
--record(s) extracted to be placed in next column one below the other.

(I tried using VLOOKUP function. But this returns only one value while I
want all matchcing records to be extracted)

Not sure if this is a duplicate question though searched the list to see if
there is one that comes close. But found none.
Appreciate your response.

Thanks and good day,

Hi,

You need to add a reference to the Access Object Library (Tools..
references) then use the DoCmd.OpenQuery "qryMissingFiles" method, passing in
the query name and the parameter.

Or, my prefereed option is to use ADO with the Jet Provider to execute the
query directly.

Another alternative is to use th Excel Query tables option (in Excel, Data
menu, Get External Data...)

HTH

Philip

"Nagesh" wrote:

I have a query in Access DB which returns record(s) for a given value by
popping up a windows(parameterised query).
I want to run this query from Excel to extract one or more values by:
--having input to the query from the active cell(where cursor is placed and
from where I run this query),
--record(s) extracted to be placed in next column one below the other.

(I tried using VLOOKUP function. But this returns only one value while I
want all matchcing records to be extracted)

Not sure if this is a duplicate question though searched the list to see if
there is one that comes close. But found none.
Appreciate your response.

Thanks and good day,

Hi,

Third option does not allow me to run the query directly.
For 2nd option I wrote this code which is failing. Please let me know if
something needs correction here.

----------------------------------------------------------------------------------
Sub tt()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Cstring As String
Dim sSql As String
Dim sWhere As String
Dim sFrom As String

'Create the connection string
Cstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\mydb.mdb;"
Set cn = New ADODB.Connection
cn.Cstring

'Create the recordset object and run the query
Set rs = New ADODB.Recordset
rs.ActiveConnection = cn

sFrom = "SELECT Q_ALL_formulas_Accounts_single_window.[Account Id],"
sFrom = sFrom + "Q_ALL_formulas_Accounts_single_window.[Formula] "
sFrom = sFrom + "FROM Q_ALL_formulas_Accounts_single_window "
sWhere = "WHERE Q_ALL_formulas_Accounts_single_window.[Account Id]=" &
Range("C6").Value
sSql = sFrom & sWhere

rs.Open sSql, Cstring, adOpenForwardOnly, _
adLockReadOnly, adCmdTable

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

'Dump the contents of the recordset onto the worksheet
Sheet1.Range("A2").CopyFromRecordset rs
'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
rs.Close
Set rs = Nothing
End Sub

-------------------------------------------------------------------------

Thanks,
Nagesh


"Philip" wrote:

Hi,

You need to add a reference to the Access Object Library (Tools..
references) then use the DoCmd.OpenQuery "qryMissingFiles" method, passing in
the query name and the parameter.

Or, my prefereed option is to use ADO with the Jet Provider to execute the
query directly.

Another alternative is to use th Excel Query tables option (in Excel, Data
menu, Get External Data...)

HTH

Philip

"Nagesh" wrote:

I have a query in Access DB which returns record(s) for a given value by
popping up a windows(parameterised query).
I want to run this query from Excel to extract one or more values by:
--having input to the query from the active cell(where cursor is placed and
from where I run this query),
--record(s) extracted to be placed in next column one below the other.

(I tried using VLOOKUP function. But this returns only one value while I
want all matchcing records to be extracted)

Not sure if this is a duplicate question though searched the list to see if
there is one that comes close. But found none.
Appreciate your response.

Thanks and good day,

for your ADO Connection

cn.Cstring

should be either:

cn.open Cstring

or

with cn
.connectionstring=Cstring
.open
end with

HTH

Philip
"Nagesh" wrote:

Hi,

Third option does not allow me to run the query directly.
For 2nd option I wrote this code which is failing. Please let me know if
something needs correction here.

----------------------------------------------------------------------------------
Sub tt()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Cstring As String
Dim sSql As String
Dim sWhere As String
Dim sFrom As String

'Create the connection string
Cstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\mydb.mdb;"
Set cn = New ADODB.Connection
cn.Cstring

'Create the recordset object and run the query
Set rs = New ADODB.Recordset
rs.ActiveConnection = cn

sFrom = "SELECT Q_ALL_formulas_Accounts_single_window.[Account Id],"
sFrom = sFrom + "Q_ALL_formulas_Accounts_single_window.[Formula] "
sFrom = sFrom + "FROM Q_ALL_formulas_Accounts_single_window "
sWhere = "WHERE Q_ALL_formulas_Accounts_single_window.[Account Id]=" &
Range("C6").Value
sSql = sFrom & sWhere

rs.Open sSql, Cstring, adOpenForwardOnly, _
adLockReadOnly, adCmdTable

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

'Dump the contents of the recordset onto the worksheet
Sheet1.Range("A2").CopyFromRecordset rs
'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
rs.Close
Set rs = Nothing
End Sub

-------------------------------------------------------------------------

Thanks,
Nagesh


"Philip" wrote:

Hi,

You need to add a reference to the Access Object Library (Tools..
references) then use the DoCmd.OpenQuery "qryMissingFiles" method, passing in
the query name and the parameter.

Or, my prefereed option is to use ADO with the Jet Provider to execute the
query directly.

Another alternative is to use th Excel Query tables option (in Excel, Data
menu, Get External Data...)

HTH

Philip

"Nagesh" wrote:

I have a query in Access DB which returns record(s) for a given value by
popping up a windows(parameterised query).
I want to run this query from Excel to extract one or more values by:
--having input to the query from the active cell(where cursor is placed and
from where I run this query),
--record(s) extracted to be placed in next column one below the other.

(I tried using VLOOKUP function. But this returns only one value while I
want all matchcing records to be extracted)

Not sure if this is a duplicate question though searched the list to see if
there is one that comes close. But found none.
Appreciate your response.

Thanks and good day,