Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use ADO to retrieve data from Access Parameter Query
Hi All
can someone please help with some sample ADO code - I need to run an Access Parameter Query *from EXCEL* in my remote Access Database, which expects two parameters and return the data to sheet1. Thanks for any help. Regards Andrew |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use ADO to retrieve data from Access Parameter Query
Here is a function that returns a recordset based on a SQL statement.
Private Const m_cDBLocation As String = "D:\ForecastNew.mdb" Public Function RunQuery(ByVal strSelect As String, ByVal strFrom As String, _ ByVal strWhere As String, ByVal strOrderBy, ByVal blnConnected As Boolean) As ADODB.Recordset Dim strConnection As String strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & m_cDBLocation & ";" Set RunQuery = New ADODB.Recordset With RunQuery .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockBatchOptimistic End With RunQuery.Open strSelect & " " & strFrom & " " & strWhere & " " & strOrderBy, strConnection, , , adCmdText If blnConnected = False Then Set RunQuery.ActiveConnection = Nothing End Function The first for arguments for the function are the parts of the SQL Statement and the last argument is whether you want to leave the coneection to the database open. Leave it open if you wnat to update the record in the database. You can get the SQL statment from the access database by changing the vie of the query to SQL. You will need to do some playing to replace the parameters with the variables. HTH "CodeMonkey" wrote: Hi All can someone please help with some sample ADO code - I need to run an Access Parameter Query *from EXCEL* in my remote Access Database, which expects two parameters and return the data to sheet1. Thanks for any help. Regards Andrew |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use ADO to retrieve data from Access Parameter Query
Jim
thanks for the response, but I believe that you misunderstood the question. I want to run an existing Access *Parameter* Query and pass 2 params to the query. I have managed to do this using the Parameter object in ADO now. Here is my code for the benefit of all (most code in the ngs seems to be DAO): Sub CollectDataByRegion() Dim com As ADODB.Command Dim col As Integer Dim Param1 As ADODB.Parameter Dim Param2 As ADODB.Parameter Cells.Clear Set com = New ADODB.Command Dim rst As ADODB.recordset com.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=PathToDatabase\MyDatabase.mdb;Persist Security Info=False;Jet OLEDB:Database Password=SomePassword" com.CommandText = "MyQuery" Set Param1 = com.CreateParameter(, adDate, adParamInput) Param1.Value = "10/02/04" com.Parameters.Append Param1 Set Param2 = com.CreateParameter(, adDate, adParamInput) Param2.Value = "10/10/04" com.Parameters.Append Param2 Set rst = com.Execute 'Write the field names For col = 0 To rst.Fields.Count - 1 Range("A1").Offset(0, col).Value = rst.Fields(col).Name Next ' Write the recordset Range("A1").Offset(1, 0).CopyFromRecordset rst rst.Close Set com = Nothing End Sub Regards Andrew "Jim Thomlinson" wrote in message ... Here is a function that returns a recordset based on a SQL statement. Private Const m_cDBLocation As String = "D:\ForecastNew.mdb" Public Function RunQuery(ByVal strSelect As String, ByVal strFrom As String, _ ByVal strWhere As String, ByVal strOrderBy, ByVal blnConnected As Boolean) As ADODB.Recordset Dim strConnection As String strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & m_cDBLocation & ";" Set RunQuery = New ADODB.Recordset With RunQuery .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockBatchOptimistic End With RunQuery.Open strSelect & " " & strFrom & " " & strWhere & " " & strOrderBy, strConnection, , , adCmdText If blnConnected = False Then Set RunQuery.ActiveConnection = Nothing End Function The first for arguments for the function are the parts of the SQL Statement and the last argument is whether you want to leave the coneection to the database open. Leave it open if you wnat to update the record in the database. You can get the SQL statment from the access database by changing the vie of the query to SQL. You will need to do some playing to replace the parameters with the variables. HTH "CodeMonkey" wrote: Hi All can someone please help with some sample ADO code - I need to run an Access Parameter Query *from EXCEL* in my remote Access Database, which expects two parameters and return the data to sheet1. Thanks for any help. Regards Andrew |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use ADO to retrieve data from Access Parameter Query
HI CodeMonkey
"CodeMonkey" wrote: Jim thanks for the response, but I believe that you misunderstood the question. I want to run an existing Access *Parameter* Query and pass 2 params to the query. I have managed to do this using the Parameter object in ADO now. Well if it works and you know how to do it what's the question??? I can post you some code but I m not sure what it is your having problems with? Do you want to pass the parameter per an Excel cell or an input box or hard coded as in your example or is that not even the problem? Good Luck TK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pass parameter to access query | Links and Linking in Excel | |||
Get External Data from Access Parameter Query | Excel Discussion (Misc queries) | |||
import data from an Access parameter query | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) | |||
Pass Parameter to Access Query | Excel Programming |