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

[9tanstaafl9]

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!ray:

 

[pd_it_guy]

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??

 

[9tanstaafl9]

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

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:

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...

 

[Fazza]

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

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

 

[9tanstaafl9]

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

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

 

[pd_it_guy]

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.

 

[xenou]

Maybe this will be a good input method:

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

 

[9tanstaafl9]

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...

 

[Fazza]

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

 

[9tanstaafl9]

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