Run an Access Query from Excel VBA with Parameters
 

Dear all,

I have got an access file (L:\YPITHALAT\IKMALImportDATABASE.mdb)

And a query named: qryRapor01-Son

The column names a

1. Analist
2. CD_Supplier
3. CS

Well, to be an easy example, i just want to run the query and get these 3
columns in the excel sheet (for example the first value to cell a1).

My parameter would be like "where Analist is "MK" (OR MK is going to be a
variable)

The problem is i do not know how to make this.

Could you please help?

regards,

Run an Access Query from Excel VBA with Parameters
 

Here is a working example you can easily modify to suit. In the VB
Editor menu Tools | References, check Microsoft ActiveX Data Objects
x.x Object Library.

Hth,
Merjet

Sub ADOParamQuery()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim intColIndex As Integer

Set TargetRange = Sheets("Sheet1").Cells(1, 1)
' open the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & "c:\temp
\db1.mdb" & ";"
Set cmd = New ADODB.Command

With cmd
.ActiveConnection = cn
.CommandText = "Query1"
.CommandType = adCmdStoredProc
End With
' Build The Parameter
Set prm = New ADODB.Parameter

With prm
.Name = "Name"
.Value = "Jim"
.Type = adVarChar
.Size = 50
.Direction = adParamInput
End With
' Append The Parameter
cmd.Parameters.Append prm
' Execute It
Set rs = cmd.Execute
With rs
For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
TargetRange.Offset(0, intColIndex).Value =
rs.Fields(intColIndex).Name
Next intColIndex
TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset
data
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub