Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2006
    Posts
    103

    Unanswered: question on running query via application

    is it possible that, i create an insert, update or delete query in Access. then i use application (for eg. vb, or excel vba) to trigger the query to run?

    and also how to pass a parameter to a select query

    for instance,

    [para1]

    select * from tableA where staffName = [para1]

    if i put this query in Access, when i click it to run, it will prompt me to put the value for para1

    but if i run it using application, how do i put the value for the parameter


    Here is the Query3 SQL query:

    PARAMETERS dept Text ( 255 );
    SELECT Multi_Skilling_Table.StaffName, Multi_Skilling_Table.Department
    FROM Multi_Skilling_Table
    WHERE (((Multi_Skilling_Table.Department)=[dept]));



    Dim Queryline As String
    Queryline = "Select * From Query3"

    Public Function GetDatabaseFast(ByVal Queryline As String, ByVal sheetName As String, startCell As String) As Boolean

    Dim ConnectionString As String
    ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
    "Data Source=" & DBSource & ";Persist Security Info= False"

    Dim Recordset As ADODB.Recordset
    Set Recordset = New ADODB.Recordset

    Call Recordset.Open(Queryline, ConnectionString, CommandTypeEnum.adCmdText)

    Dim Field As Field
    Dim rg As range

    Worksheets(sheetName).Activate
    'ActiveSheet.range("A2").Activate
    Set rg = ActiveSheet.range(startCell)

    If Not Recordset.EOF Then
    rg.CopyFromRecordset Recordset
    rg.CurrentRegion.Columns.AutoFit
    Else
    ActiveCell.FormulaR1C1 = "There is no record in the table"
    If (Recordset.State And ObjectStateEnum.adStateOpen) Then
    Recordset.Close
    End If
    Set Recordset = Nothing
    GetDatabaseFast = False
    Exit Function
    End If

    Worksheets(sheetName).Activate
    'ActiveSheet.range("B:B").Select
    'Selection.NumberFormat = "dd-mmm-yy"

    If (Recordset.State And ObjectStateEnum.adStateOpen) Then
    Recordset.Close
    End If
    Set Recordset = Nothing
    GetDatabaseFast = True
    End Function
    how can i put in the value for query3 in order to run it using my application.

    Thanks in advance
    Last edited by mkggoh; 06-19-06 at 22:36.

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    is it possible that, i create an insert, update or delete query in Access
    Yes. You can use, for example, the QueryDef object to create, modifiy and delete queries. You can also use DoCmd.RunSQL to skip the formal creation of a query if you just need to do something with a dataset on demand.

    then i use application (for eg. vb, or excel vba) to trigger the query to run?
    Yes, you can use several methods, including OpenQuery. Outside Access, you'll use Automation, create a database object, set it to your DB, and use that object to open the query.

    For the last question, one method to consider is using a function in the query instead of the parameter. In a module, create a public variable (to hold the parameter value) and the function, which will simply return the value in the public variable.

    select * from tableA where staffName = pubPara()

    In a module:
    Code:
    Public pstrPara As String
    
    Public Function pubPara() As String
        pubPara = pstrPara
    End Function
    Anywhere in your code, you can assign the parameter value to the public string.

    hope this helps,
    tc

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •