Connection Strings in ODBC - lockable?

[9tanstaafl9]

Question 1: I would like to distribute a sample of my workbook, with the ODBC connection hardwired to go to a specific directory on their C drive. I don't want the user to be able to use the Script Editor to change the ODBC connection string. (Or any other program.) Is there a way to lock this?

Question 2 (but related so I'm listing it here): Right now we set the ODBC connection when we create the workbook. We click on the worksheet, choose Import External Data and choose the Visual FoxPro ODBC driver and connect to our sample company. When I get a new user, I use MS Query to go in and edit the connection string (9 times...) -- is therees a better way to do this? Can I do it in the VBA code directly? Assuming I could I'm guessing this would solve my Question 1.

Thank you. I did see some posts regarding connection strings, but they were a bit above my head. Please speak slowly...

 

[9tanstaafl9]

Real question: can you use VBA to set ODBC connection string

Ok, sorry, I was really tired and I realize that part 1 of my question wasn't really a VBA question at all and I didn't explain it well anyway. My real question is:

Can you use VBA to set the ODBC connection string for queries? If so, how? I have 9 queries, all with same string. 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?

Sorry about posting poorly. Is there a way I can delete this whole thread and redo it correctly? I don't want to cross post and tick people off even more.

Thanks!

 

[9tanstaafl9]

Re: Real question: can you use VBA to set ODBC connection string

Ok, I'm going to risk the cross post. I think since I screwed up my initial question that's the only thing left to do since I'm not getting a response. If there is some way to delete a bad question could someone please tell me?

Sorry in advance for anyone I tick off by doing this, but I'm not sure as to the ettiquite here, and I really need an answer.

 

[9tanstaafl9]

Re: Real question: can you use VBA to set ODBC connection string

Ok, I'm going to risk the cross post. I think since I screwed up my initial question that's the only thing left to do since I'm not getting a response. If there is some way to delete a bad question could someone please tell me?

Sorry in advance for anyone I tick off by doing this, but I'm not sure as to the ettiquite here, and I really need an answer.

GOT SUCCESSFUL ANSWER TO SETTING THE CONNECTION STRING. Really nice people provided the code for me. See the following post:

http://www.mrexcel.com/forum/showthread.php?t=332627

(Not sure if I did that right. If not, sorry, my intentions were good. Someone please tell me what I was supposed to have done. Thx.)