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:

 

[Fazza]

Re: bourbon. Thanks for the tip.

 

[9tanstaafl9]

Maybe this will be a good input method:

Fazza,

I have a question about the input method code you gave me. The dialog box that opens up wants me to select a single file, when what I really need to select is the entire directory where all the tables reside. Is there a way to tweak your code to make that work?

A second question, and this applies to the other code you provided as well. It works fine on some of my reports, but on others (with different queries) it pops up a dialog box that says, "A Machine data source is specific to this machine and cannot be shared. 'User' data sources are specific to a user on this machine. 'System' data sources can be used by all users on this machine, or by a system-wide service." -- any idea what that means? If not, let me know and I'll post this as a separate question.

Thanks again,
Jennifer

 

[Fazza]

Hi, Jennifer.

It was Alexander's code, not mine. Maybe change to this, and add your own error checking,

Sub ChangeDatabase()
 
Dim varDBPath
 
varDBPath = Application.GetOpenFilename(Title:="Choose Database")
 
If varDBPath = False Then
    Exit Sub 'Cancelled
Else
    Call FazzaCode(Left$(varDBPath, InStrRev(varDBPath, Application.PathSeparator) - 1))
End If
 
End Sub

Re the other question, I can only guess. The connection string contains other information that is likely different on some machines/queries. I'd need to see some of the other connection strings to be sure. Suspect a solution would be to replace the existing path with the new path - as opposed to the current code which sets the entire connection by joining together a leading section, the path, and a trailing section. The leading and trailing sections may not be correct for all queries, I guess. So can you look at some of the other connection strings & work that out? It would mean a different text operation, OK? Only replace the path keeping the existing other sections of the connection string, not like now which joins some fixed strings either side of the path. These fixed strings are probably incorrect for the queries. Again, I am only guessing.

HTH, Fazza

 

[9tanstaafl9]

My bad. Still sick here... It said Fazza in the code... Thanks again to you BOTH.

Forget my earlier question about the machine code. I didn't do the queries, and the person who did told me they were just copies, but they really weren't. I assumed instead of checking closely, and I know what happens when you assume...

FYI the change you made to the code works great. I just have to select a file in the directory and it knows what to do. Thank you again!

Jennifer

 

[Fazza]