Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Query Parameters | Excel Discussion (Misc queries) | |||
Passing parameters from excel to access | Excel Programming | |||
Is there a way to pass parameters to a web query in excel | Excel Programming | |||
Web query with parameters does not work in Excel 2000 - Please help | Excel Programming | |||
RP - Office 2000 automation parameters Access and excel | Excel Programming |