none
VBA to SQL Server database

    Question

  •  

    Hi

    Appologies up front if the terminology in this question is not correct - I am very much a VB beginner - with grandious ideas.

    I am floundering around trying to figure out how to run a parameter query using VBA.

    I have already defined the query in Excel - Data > Import External Data > New Database Query) from Excel VBA

    The query accesses a SQL Server database

    It is working up to that point and uses a parameter from the workbook, and 2 parameters that it needs to ask the user for - I have this in the macro (VBA)

    I need to run the query from a macro attached to a button in Excel as I have some other work to do before it runs.

    Is there anyway that I can run this query from VBA or do I have to define the SQL etc from scratch in VBA?

    BTW - I am using Excel 2003. I tried to define the SQL using the SQLOpen, SQLExecQuery etc, then found out I needed the ODBC add-in that is not available for Excel 2003. Below is how I had done this, I tried to understand the ADO info and got completely lost - it would seem logical that I can use the predefined query - logical to me anyway :)

    Thanks in advance for any assistance you are able to offer.

    Frankie

       
        'Find last detail row in Date Worksheet
        LastRow = Worksheets(replicondataSheet).UsedRange.Rows.count
              
        'Add 1 to last row
       
        StartPopulationRow = LastRow + 1
        StartData = "A" + StartPopulationRow
       
        'Retrieve Replicon Data
       
        Dim projectcode As String
        Dim DatabaseName As String
       
        'DatabaseName = "replicon_test"
        projectcode = Worksheets(summarySheet).Name("projectcode")
       
        QueryString = _
            "SELECT Rp_V_EDProjStatusUserDt_ts.taskname1, Rp_V_EDProjStatusUserDt_ts.username, Rp_V_EDProjStatusUserDt_ts.entrydate, Rp_V_EDProjStatusUserDt_ts.billable_hours, Rp_V_EDProjStatusUserDt_ts.comments " _
            & "FROM Replicon.dbo.Rp_V_EDProjStatusUserDt_ts Rp_V_EDProjStatusUserDt_ts " _
            & "WHERE (Rp_V_EDProjStatusUserDt_ts.projectcode=pProjectCode) AND (Rp_V_EDProjStatusUserDt_ts.billable_hours>0) AND (Rp_V_EDProjStatusUserDt_ts.entrydate>=StartDateResponse) AND (Rp_V_EDProjStatusUserDt_ts.entrydate<=EndDateResponse)"
        Chan = SQLOpen("DSN=" & DatabaseName)
        SQLExecQuery Chan, QueryString
        Set Output = Worksheets(replicondataSheet).Range(StartData)
        SQLRetrieve Chan, Output, , , True
        SQLClose Chan

     

     

     

     

     

    Friday, December 29, 2006 10:01 PM

Answers

  • Hi Frankie

    As the "Please Read First" message at the top of this forum explains, this forum is for questions concerning Visual Studio Tools for Office, which is part of Visual Studio .NET. VBA is not discussed, nor are questions concerning an application's object model considered to be "on-topic" unless they concern the VSTO technology.

    If you ask your question in the excel.programming newsgroup you should get quick and competent help from the Excel VBA specialists who frequent there.

    I can give you one tip that may help you a bit: you have to provide your own prompts when you use "parameter queries" in code (doesn't matter what programming language). You can use a MsgBox or a UserForm, for example, to get the user input. Then you have to build it into your code. So this

      (Rp_V_EDProjStatusUserDt_ts.entrydate>=StartDateResponse)

    should look more like this:

      sStartDateResponse = MsgBox("Enter the start date")
      (Rp_V_EDProjStatusUserDt_ts.entrydate>=" & StartDateResponse & ")"

    Of course, you should probably build some validation (is it a valid date) and conversion (convert the input to the date format expected by SQL Server) in there. The point is retrieving the user input, then building that into the query string.

    Sunday, December 31, 2006 5:40 PM
    Moderator

All replies

  • Hi Frankie

    As the "Please Read First" message at the top of this forum explains, this forum is for questions concerning Visual Studio Tools for Office, which is part of Visual Studio .NET. VBA is not discussed, nor are questions concerning an application's object model considered to be "on-topic" unless they concern the VSTO technology.

    If you ask your question in the excel.programming newsgroup you should get quick and competent help from the Excel VBA specialists who frequent there.

    I can give you one tip that may help you a bit: you have to provide your own prompts when you use "parameter queries" in code (doesn't matter what programming language). You can use a MsgBox or a UserForm, for example, to get the user input. Then you have to build it into your code. So this

      (Rp_V_EDProjStatusUserDt_ts.entrydate>=StartDateResponse)

    should look more like this:

      sStartDateResponse = MsgBox("Enter the start date")
      (Rp_V_EDProjStatusUserDt_ts.entrydate>=" & StartDateResponse & ")"

    Of course, you should probably build some validation (is it a valid date) and conversion (convert the input to the date format expected by SQL Server) in there. The point is retrieving the user input, then building that into the query string.

    Sunday, December 31, 2006 5:40 PM
    Moderator
  • thanks Cindy - I don't see a 'please read first' - strange

    I will try the other location you have suggested.

    Happy New Year

    Tuesday, January 02, 2007 4:44 PM