Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Can VBA be used to set ODBC connection string for query?

This is a discussion on Can VBA be used to set ODBC connection string for query? within the Excel Questions forums, part of the Question Forums category; Hi. Can someone tell me how to use VBA to set the ODBC connection string for queries? My workbook has ...

  1. #1
    Board Regular 9tanstaafl9's Avatar
    Join Date
    Mar 2008
    Location
    Near Seattle
    Posts
    444

    Default Can VBA be used to set ODBC connection string for query?

    Hi. Can someone tell me how to use VBA to set the ODBC connection string for queries?

    My workbook has 9 queries, all needing the same string. I would like to have a msgbox pop up, let them enter the directory name, and have that change all the odbc conneciton strings automatically. Can anyone point me in the right direction?

    The message box would be a nice touch, but is not necessary, I can change it in the VBA editor if needed.

    Currently, they connect using a Visual FoxPro ODBC driver. We setup the connection when we made the queries. Now that they are in on the worksheets, I want to be able to edit them with VBA instead of using the script editor (which is very slow).

    Thanks!


    Note, I screwed up my original question and I apologize for cross-posting. My original question was posted under the title: Connection Strings in ODBC - lockable? -- note I would still like to know if they are lockable/protectable, but that was a secondary issue... Sorry!

  2. #2
    Board Regular
    Join Date
    Jul 2008
    Location
    Northern Calif.
    Posts
    94

    Default Re: Can VBA be used to set ODBC connection string for query?

    If you do a macro record of a data refresh operation that should pretty well capture exactly what the db connection and SQL was. Dont mind how it looks and be very careful if you try to edit it. It should end up with a Query refesh. If you run the module you will essentially force whatever code there is to the query. Most of the time our connection string does not change but our SQL does.

    The connection string has to be exactly correct, of course when you carve out the part that changes, its replacement needs to go in to the script with the

    " & variable_name & "

    If there are only a few options I would have the users pick from a list so they get it right. Of course you can stack these query refresh commands one after the other.

    Is this what you had in mind??

  3. #3
    Board Regular 9tanstaafl9's Avatar
    Join Date
    Mar 2008
    Location
    Near Seattle
    Posts
    444

    Default Re: Can VBA be used to set ODBC connection string for query?

    Quote Originally Posted by pd_it_guy View Post
    If you do a macro record of a data refresh operation that should pretty well capture exactly what the db connection and SQL was. Dont mind how it looks and be very careful if you try to edit it. It should end up with a Query refesh. If you run the module you will essentially force whatever code there is to the query. Most of the time our connection string does not change but our SQL does.

    The connection string has to be exactly correct, of course when you carve out the part that changes, its replacement needs to go in to the script with the

    " & variable_name & "

    If there are only a few options I would have the users pick from a list so they get it right. Of course you can stack these query refresh commands one after the other.

    Is this what you had in mind??
    Thanks for your reply. I think you understand what I want, but unfortunately I don't understand your answer. I'm new at this and your answer was way above my head. I tried running the macro recorder and pressing the data refresh button, but all I got was
    Code:
    Selection.QueryTable.Refresh BackgroundQuery:=False or ActiveWorkbook.RefreshAll
    I'm assuming that's not what you meant. Then I tried creating a new workbook and recording the process of importing data, and on a sample import I got the following:

    Code:
     
    Sub Macro5()
        With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
            "ODBC;DSN=Visual FoxPro Tables;UID=;;SourceDB=u:\MB\ABC Company;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine" _
            ), Array(";Null=Yes;Deleted=Yes;")), Destination:=Range("L5"))
            .CommandText = Array( _
            "SELECT actpay.recnum, actpay.vndnme, actpay.shtnme, actpay.contct, actpay.addrs1, actpay.addrs2, actpay.ctynme, actpay.state_, actpay.zipcde, actpay.fedidn, actpay.steidn, actpay.resnum, actpay.actnum" _
            , _
            ", actpay.licnum, actpay.usrdf1, actpay.usrdf2, actpay.phnnum, actpay.pagnum, actpay.faxnum, actpay.cllphn, actpay.homphn, actpay.e_mail, actpay.dscrte, actpay.dscdte, actpay.duedte, actpay.lgrdft, act" _
            , _
            "pay.cdedft, actpay.typdft, actpay.stsdft, actpay.wrndft, actpay.bal199, actpay.ytdact, actpay.lstact, actpay.begbal, actpay.endbal, actpay.vndtyp, actpay.prt199, actpay.minsts, actpay.taxdst, actpay.i" _
            , _
            "ntrnl, actpay.cmprte, actpay.utxrte, actpay.hotlst, actpay.ordtyp, actpay.orddsc, actpay.contyp, actpay.condsc, actpay.ntetxt, actpay.imgfle, actpay.dupchk, actpay.rfptyp, actpay.rfpdsc, actpay.sepchk" _
            , "" & Chr(13) & "" & Chr(10) & "FROM actpay actpay")
            .Name = "Query from Visual FoxPro Tables"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .Refresh BackgroundQuery:=False
        End With
    End Sub
    The part I want to have users be able to change is only the section: "u:\MB\ABC Company" to something like "X:\MB\DEF Company"

    If the above code actually appeared in any module, I would know how to change it, but the code for the queries doesn't seem to exist anywhere that i can find in the VBA editor. The only way I can see the queries again is to use MS Query Editor. (I tried macro-recordering that, but it said it couldn't perform that operation.)

    I'm sorry if I'm just being extremely dense, but I just don't understand queries at all, and googling it and reading other posts is just making me more confused. Any pointers would be appreciated...

  4. #4
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    7,972

    Default Re: Can VBA be used to set ODBC connection string for query?

    Nearly there. Something like below, maybe? Untested. F

    Code:
    Sub Macro5()
      'changes all queries in workbook
     
      'No error checking provided
      'input this however you like
      Const strPath As String = "u:\MB\ABC Company"
     
      Dim qt As QueryTable
      Dim wks As Worksheet
     
      For Each wks In ActiveWorkbook.Worksheets
        For Each qt In wks.QueryTables
          With qt
     
            .Connection = Join$(Array( _
                "ODBC;DSN=Visual FoxPro Tables;UID=;;SourceDB=", _
                strPath, _
                ";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;" _
                ), vbNullString)
     
            'if you don't want to refresh, omit line following
            .Refresh BackgroundQuery:=False
          End With
        Next qt
      Next wks
     
      Set qt = Nothing
      Set wks = Nothing 
    End Sub

  5. #5
    Board Regular 9tanstaafl9's Avatar
    Join Date
    Mar 2008
    Location
    Near Seattle
    Posts
    444

    Default Re: Can VBA be used to set ODBC connection string for query?

    Quote Originally Posted by Fazza View Post
    Nearly there. Something like below, maybe? Untested. F

    Code:
    Sub Macro5()
      'changes all queries in workbook
     
      'No error checking provided
      'input this however you like
      Const strPath As String = "u:\MB\ABC Company"
     
      Dim qt As QueryTable
      Dim wks As Worksheet
     
      For Each wks In ActiveWorkbook.Worksheets
        For Each qt In wks.QueryTables
          With qt
     
            .Connection = Join$(Array( _
                "ODBC;DSN=Visual FoxPro Tables;UID=;;SourceDB=", _
                strPath, _
                ";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;" _
                ), vbNullString)
     
            'if you don't want to refresh, omit line following
            .Refresh BackgroundQuery:=False
          End With
        Next qt
      Next wks
     
      Set qt = Nothing
      Set wks = Nothing 
    End Sub
    THANK YOU! THANK YOU! THANK YOU! (And yes, I am yelling!)

    Wow! That works perfectly, and I wouldn't (at least for a year or more) have been able to figure it out myself, so I truly appreciate your just writing the code for me. Wow. This is great.

    I had about 50 people I had to change 9 queries for, every time I update the report, which is frequently. Thank you so much!

    Jennifer

  6. #6
    Board Regular
    Join Date
    Jul 2008
    Location
    Northern Calif.
    Posts
    94

    Default Re: Can VBA be used to set ODBC connection string for query?

    The only thing left to do is to get that code in a place to be run. You can either take what is there, either your example, or Fazza's, and copy that into a module, or you can re-record the entire thing yourself right into a module. I usually get something to work with by recording data/ refresh data/ edit query/ (get to the query screen) and return the data to MS Excel. That will get you the block you need. Then, as was suggested, make that one part of the connection string that needs to change the variable that is selected, as however you want, by some sort of user input.

    Then when you run the module it forces the connection string and SQL back into MS Query and it runs. The last version of the query is what remains embedded 1n the sheet and can be refreshed by itself. The only thing you are changing is the connection variable. You can still have it refresh on file open, copy formulas, as need be.

    See if you can get that code to run and get data.

  7. #7
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,167

    Default Re: Can VBA be used to set ODBC connection string for query?

    Maybe this will be a good input method:

    Code:
    Sub ChangeDatabase()
    Dim varDBPath
    
    varDBPath = Application.GetOpenFilename(Title:="Choose Database")
    If varDBPath = False Then
        Exit Sub 'Cancelled
    Else
        Call FazzaCode(varDBPath)
    End If
    
    End Sub
    '-------------------------------------
    Sub FazzaCode(ByVal strPath As String)
    'changes all queries in workbook to point to database selected by user
    'No error checking provided
     
      Dim qt As QueryTable
      Dim wks As Worksheet
     
      For Each wks In ActiveWorkbook.Worksheets
        For Each qt In wks.QueryTables
          With qt
     
            .Connection = Join$(Array( _
                "ODBC;DSN=Visual FoxPro Tables;UID=;;SourceDB=", _
                strPath, _
                ";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;" _
                ), vbNullString)
     
            'if you don't want to refresh, omit line following
            .Refresh BackgroundQuery:=False
          End With
        Next qt
      Next wks
     
      Set qt = Nothing
      Set wks = Nothing
    
    End Sub

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  8. #8
    Board Regular 9tanstaafl9's Avatar
    Join Date
    Mar 2008
    Location
    Near Seattle
    Posts
    444

    Wink Re: Can VBA be used to set ODBC connection string for query?

    Quote Originally Posted by Alexander Barnes View Post
    Maybe this will be a good input method:

    Thank you so much. I will have to try it in the morning. I have a cold and am sicker than a dog and have had a few too many hot toddies and can't concentrate. That, and I'm looking for earplugs for my nose...

  9. #9
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    7,972

    Default Re: Can VBA be used to set ODBC connection string for query?

    Jennifer,

    If those hot toddies contain a little alcohol - they would in Australia! - I can only suggest they won't help with the cold. Ask me how I know! I tried to have a few rums one evening to stop an embryonic cold... missed a party that I was supposed to attend and woke up feeling much sicker than any two dogs. Glad the code is appreciated. It might be suitable in an add-in. Then all your users could have the add-in and run it to change the source database. Hope you're healthy again very soon. Regards, Fazza

  10. #10
    Board Regular 9tanstaafl9's Avatar
    Join Date
    Mar 2008
    Location
    Near Seattle
    Posts
    444

    Default Re: Can VBA be used to set ODBC connection string for query?

    Quote Originally Posted by Fazza View Post
    Jennifer,

    If those hot toddies contain a little alcohol - they would in Australia! - I can only suggest they won't help with the cold. Ask me how I know! I tried to have a few rums one evening to stop an embryonic cold... missed a party that I was supposed to attend and woke up feeling much sicker than any two dogs. Glad the code is appreciated. It might be suitable in an add-in. Then all your users could have the add-in and run it to change the source database. Hope you're healthy again very soon. Regards, Fazza
    I only had two, and actually, I feel much better this morning. Mine had bourbon not rum, maybe that's the secret?

    Thanks again for everything,
    Jennifer

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com