CODEWITHSUNDEEP
excelms-access
Henry Taylor
Henry Taylor

Reputation: 91

Connecting Excel to Access

I'm trying to connect excel on a database which has the following query.

SELECT * FROM Products WHERE Order = [Enter Order]

but excel can't seem to find this query. It only shows the actual table and other queries which does not use parameters.

Is there a way to connect excel on a query which uses parameters? I'm using MS-Excel 2007.

Upvotes: 2

Views: 1631

Answers (1)

Fionnuala
Fionnuala

Reputation: 91376

Some notes.

"Parameter queries must be created in Microsoft Query."

Customize a parameter query
Use Microsoft Query to retrieve external data

ADODB & VBA

''Ref: Microsoft ActiveX Data Objects x.x Library
Dim cmd As New ADODB.Command
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim prm As ADODB.Parameter
Dim strConn As String

strConn = "Provider = Microsoft.ACE.OLEDB.12.0;" _
        & "Data Source=z:\docs\test.accdb"

conn.Open strConn

cmd.ActiveConnection = conn
cmd.CommandText = "Query4"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParameter("EnterText", adVarWChar, adParamInput, 50)
cmd.Parameters.Append prm
cmd.Parameters("EnterText").Value = ActiveWorkbook.Sheets("Sheet5").[A2]

'Execute the Stored Procedure
Set rs = cmd.Execute
ActiveWorkbook.Sheets("Sheet8").Cells(2, 1).CopyFromRecordset rs
'Close the connection
conn.Close

Upvotes: 2

Related Questions