ODBC question

[Jac Tremblay]

Hi,
I want to know if it is possible to configure an ODBC connection with all
the required parameters including user name and password and use it from
where it is stored on the drive.
Any comments would be appreciated.
Thanks.

 

[timmg]

Hi,
I want to know if it is possible to configure an ODBC connection with all
the required parameters including user name and password

yes, absolutely. I use a lot of pivot tables that are based on ODBC
connections. When you create the PT you can tell Excel to save the
password - then when you refresh, it just goes out and does it.

I also have a lot of PTs that I inherited from a predessor - I have to
form with a few buttons and a text box. One button reads the
connection string

Private Sub cmdGetString_Click()
' Show source Query
Dim pt As PivotTable, sMsg As String
On Error GoTo err_Handler

Set pt = ActiveCell.PivotTable
sMsg = pt.PivotCache.Connection

txtCmdTxt.Value = sMsg
Exit Sub

Another lets you reset it:

Private Sub cmdSetString_Click()
'Set the connection string to the value of the text box
Dim pt As PivotTable
On Error Resume Next

If msgbox("Update connection string to list", vbOKCancel, "Confirm
update") = vbOK Then
Set pt = ActiveCell.PivotTable

pt.PivotCache.CommandText = Me.txtCmdTxt.Value
pt.RefreshTable
End If
End Sub

It's pretty crude code, but it works. Hope that gives you an idea

and use it from
where it is stored on the drive.

I don't quite understand that question, sorry.

hth,

Tim

 

[Jac Tremblay]

Hi Timmg,
Do you know where this connection object is stored on your hard drive or
what its name is? I do not.
For example, the datetime picker control that one may use on a form is
stored in the C:\Windows\System32\ and is called mscomct2.ocx.
Can someone change that control (set its parameters) and save it under
another name? I do not think so.
That is the same with a connection object, I guess. It cannot be configured
and stored under a different name to be used after by some other procedure.
This question was raised in some discussion where I work and I wanted some
confirmation (is it possible or not?). A simple reasoning gave me the answer.
You helped me with your comment.
So afaik, this closes the case.
Thank you for your answer and have a nice day.