Multiple queries

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