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


"timmg" wrote:

On Jan 13, 6:32 pm, Jac Tremblay wrote:
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