Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Aug 2002
    Location
    Wichita, Kansas, USA
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Retrieve SLQ dbase Data thru Excel (2000)

    I need to retrieve data from an SQL dbase via the click of a command button.

    I got it to work creating an MS Query and having the command button call up the MS Query file and run it, but the MS Query file is a separate file I would have to have in a generic location on the WAN that all employees that use the spreadsheet would be able to access. This is a problem.

    Therefore, I would like to query the SQL data from vba code hooked to the command button. I took the Select statement created by MSQuery and copied it into a VBA module, then tried adding the other necessary code to open the dbase, run the query, and retrieve the data, looking at examples in the Excel VB help. I'm not getting any errors, or any results. Below is the module code:

    Sub ImportFromLAmacro()
    databaseName = "PCS.dbo.LALoanAppInfo"
    queryString = _
    "SELECT LALoanAppInfo.LoaAppID, LALoanAppInfo.AppDate, LALoanAppInfo.LastName, LALoanAppInfo.FirstName, LALoanAppInfo.MI, LALoanAppInfo.SS# FROM PCS.dbo.LALoanAppInfo LALoanAppInfo WHERE (LALoanAppInfo.LoanAppID=?)"
    chan = SQLOpen("DSN=" & databaseName)
    SQLExecQuery chan, queryString
    Set output = Worksheets("ImportFromLA").Range("A1")
    SQLRetrieve chan, output, , , True
    SQLClose chan

    End Sub

    Is there enough information here you can help me figure out what I have wrong?

    Thanks!
    Patty

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve SLQ dbase Data thru Excel (2000)

    The problem is hard to diagnose without your actual system.
    But you should not need to save the sql query, it should also be stored within the workbook (unless you need to enter parameters).

    Try recording a macro whilst you manually query the database. That should get you the proper syntax of the connection string.

    You could also use my QueryManager to display the sql string and the connection string from an existing (working) query. Find my query manager at:

    http://www.jkp-ads.com/QueryManager.zip
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Star Lounger
    Join Date
    Aug 2002
    Location
    Wichita, Kansas, USA
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve SLQ dbase Data thru Excel (2000)

    Thanks! I finally found somebody in house that helped me get the connection string right. Although we worked for a long time using the SQL commands, he ended up redoing the macro in ADO - which turned out much easier. ( When I looked up the SQL in the help file it gave me the SQL commands, not the ADO information, so that's where I started.)

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve SLQ dbase Data thru Excel (2000)

    Here is a good reference site about ADO:

    http://www.erlandsendata.no/english/...odao/index.htm
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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