Hi,

i have workbooks with multiple database queries on various sheets within the
workbook. If I set it to not store the password with the query I am prompted
for a username and password. The problem is, if I run the code
ActiveWorkbook.RefreshAll i have to enter my user name and password for each
query the first time it runs. I have setup a userform to capture the username
and password, but now I want to pass this into each query prior to the
refresh.

when I record a macro for creating the initial query it captures all the
code necessary, but when I perform a refresh where I have to enter a username
password, it does not show this code for the username/password being
recorded...

I have tried code like:

ActiveWorkbook.RefreshAll (Connection:= _
"ODBC;DRIVER={Microsoft ODBC for Oracle}; _
UID=' & myUsername & ";PWD=' & myPassword & ";SERVER=mv;")
and
ActiveSheet.QueryTables (Connection:= _
"ODBC;DRIVER={Microsoft ODBC for Oracle}; _
UID=" & myUsername & ";PWD=" & myPassword & ";SERVER=mv;")

but I keep getting an error message indicating I need an = sign...

thoughts?

Simon

Not sure, but try putting a double-quote ", so:

"ODBC;DRIVER={Microsoft ODBC for Oracle}; _
becomes
"ODBC;DRIVER={Microsoft ODBC for Oracle};" _

and

UID=' & myUsername & ";PWD=' & myPassword & ";SERVER=mv;")
"UID=' & myUsername & ";PWD=' & myPassword & ";SERVER=mv;")


arunkhemlai



"Simon Shaw" wrote:

Hi,

i have workbooks with multiple database queries on various sheets within the
workbook. If I set it to not store the password with the query I am prompted
for a username and password. The problem is, if I run the code
ActiveWorkbook.RefreshAll i have to enter my user name and password for each
query the first time it runs. I have setup a userform to capture the username
and password, but now I want to pass this into each query prior to the
refresh.

when I record a macro for creating the initial query it captures all the
code necessary, but when I perform a refresh where I have to enter a username
password, it does not show this code for the username/password being
recorded...

I have tried code like:

ActiveWorkbook.RefreshAll (Connection:= _
"ODBC;DRIVER={Microsoft ODBC for Oracle}; _
UID=' & myUsername & ";PWD=' & myPassword & ";SERVER=mv;")
and
ActiveSheet.QueryTables (Connection:= _
"ODBC;DRIVER={Microsoft ODBC for Oracle}; _
UID=" & myUsername & ";PWD=" & myPassword & ";SERVER=mv;")

but I keep getting an error message indicating I need an = sign...

thoughts?

Simon