Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2002
    Posts
    27

    Question Unanswered: VBA code to run a sql command in Excel

    I am trying to build the code to return a single value to a specific cell in excel. The query works fine in Access but is to complex for MSQuery and I need to be able to do one of two things.

    1) Have the query in Access with parameters and then have excel run the query with various parameters and return value to a cell.

    2) Have excel run a sql statement on multiple tables from my Access2000 database.

    I cannot seem to work out the correct code to connect to the database and return the value. Any help would be much appreciated.

    Bentley

  2. #2
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122
    The code below works from Access and it sets the value of cell A1 in Sheet1 of workbook "bbb.xls" to the value of "FieldName" returned by AccessQueryName.
    I assumed your query returns (only) one record every time.

    Sub ExcelCommand()
    Dim myapp, myws, myrs
    Set myapp = CreateObject("Excel.Application")
    myapp.Workbooks.Open "bbb.xls"
    Set myws = myapp.Workbooks("bbb.xls").Worksheets("sheet1")
    Set myrs = CurrentDb.OpenRecordset("AccessQueryName")
    myws.Range("A1")=myrs!FieldName
    Set myrs = Nothing
    myapp.Workbooks("bbb.xls").Close acSaveYes
    Ste myws = Nothing
    Set myapp = Nothing
    End Sub

    HTH

    Dan

  3. #3
    Join Date
    Jul 2002
    Posts
    27
    DannyY,

    sorry my question was badly ordered.

    My ideal solution is to run code from Excel. I have several sheets that adjust based on the change on a few parameter fields. I have reached the point where MS Query won't run the query so I want to run SQL in code to return a QueryTable of Data (which happens to be one cell everytime) and return it to a particular field/range (range of 1 cell).

  4. #4
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122

    tried in Excel, but...

    ....(strangely) the OpenCurrentDatabase method fails AFTER opening the database (!!!). So I have first to explain this to myself...

    Sub ControlAccess()
    Dim myapp, mydb, myrs, mysqlstr

    mysqlstr="Select ... Where TableName.FieldName=" & Range("A1")
    Set myapp = CreateObject("Access.Application") 'this works
    Set mydb = myapp.OpenCurrentDatabase("C:\Path\File.mdb")
    'the funny thing is that the database is opened, but mydb is not initialized! And this is the most important line in the code, because it gives you full control over the database through automation...

    'The rest of the code depends on mydb variable, so I couldn't test it

    Set myrs = mydb.OpenRecordset(mysqlstr)
    Range("B1")=myrs!fieldname
    myrs.Close
    Set myrs = Nothing
    myapp.CloseCurrentDatabase
    Set myapp = Nothing
    End Sub

    I looked and looked and looked and looked again and can't find the bug... Everything is by the book! Tried with a reference set to Microsoft Access, without it and got the same result...

    However, I posted a similar answer in another forum and the poster said it worked, so it's worth trying it...
    Regards,

    Dan

  5. #5
    Join Date
    Jul 2002
    Posts
    27
    same line didn't work for me!



    This is where I have got to myself: only problem is that the SQL statement works in Access giving me a result of 2568 and gives a result of 0 in excel! The other SQL line I commented out works fine!


    CODE BELOW:


    Sub adoimport()
    Dim cnt As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim stDB As String
    Dim stSQL As String
    stDB = "L:\marketing information system\organonMIS.mdb"


    'cmbIn.Clear
    'txtUtDatum.Caption = ""

    cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & stDB & ";"

    'stSQL = "SELECT count(dbo_ACTIVITY.ACC_ID) AS TOTALCOUNT FROM dbo_ACTIVITY"
    stSQL = "SELECT Count([dbo_ACTIVITY].[ACT_ID]) AS CountOfACT_ID FROM dbo_TBV_CALLTYPE, dbo_ACC_TGTPRD, dbo_ACTIVITY WHERE [dbo_ACC_TGTPRD].[ACC_ID] = [dbo_ACTIVITY].[ACC_ID] AND [dbo_TBV_CALLTYPE].[TBV_CODE]=[dbo_ACTIVITY].[CALL_TYPE] AND ((([dbo_TBV_CALLTYPE].[TBV_LABEL_GB]) Like 'DGM*' Or ([dbo_TBV_CALLTYPE].[TBV_LABEL_GB]) Like 'Face to Face*' Or ([dbo_TBV_CALLTYPE].[TBV_LABEL_GB]) Like 'trade*' Or ([dbo_TBV_CALLTYPE].[TBV_LABEL_GB]) Like 'Lit/Sam Drop*' Or ([dbo_TBV_CALLTYPE].[TBV_LABEL_GB]) Like 'video*') And (([dbo_ACC_TGTPRD].[TER_ID])='1201'))"


    With rst
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .Open stSQL, cnt, , adCmdTable
    End With

    MsgBox (rst.GetString)
    Set rst = Nothing
    Set cnt = Nothing
    End Sub

  6. #6
    Join Date
    Jan 2002
    Location
    UK
    Posts
    67
    There is a note specified in the Help for this Command,
    Try using the opendatabase instead of openCurrentDatabase.


    Don't confuse the OpenCurrentDatabase method with the DAO OpenDatabase method. The OpenCurrentDatabase method opens a database in the Microsoft Access window. The DAO OpenDatabase method returns a Database object variable that represents a particular database, but doesn't actually open that database in the Microsoft Access window.

Posting Permissions

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