asked on

Connection String with variable userid/password does not work

I have a connection string to AS400/iSeries with SQL to extract data from AS400 table. WHen I hard-code the userid and password as part of the string, it works perfectly. But when I try to use variables to pass userid/password I get
Runtime error --- Automation Error

Here's the string:
con.Open "Driver={Client Access ODBC Driver (32-BIT)};System=MYSYS;UID=uid;PWD=pwd"
 
uid and pwd were DIM as string

Open in new window

ASKER CERTIFIED SOLUTION

CSLARSEN

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gary Patterson, CISSP

To expand a bit on what CLARSEN said:

If it is inside of quotes, it is a literal, and VB won't attempt to resolve it. You get what you type.
If it is outside of quotes, it is a variable, and VB will substitute the contents of the variable when it resolves the expression.

Dim uid As String = "Gary"
Debug.Print "uid" // Resolves to "uid", since it is inside quotes
Debug.Print uid // Resolves to "Gary" since it is outside quotes.

Just use the concatenation symbol "&" when you need to tack together quoted literals and variables, as in CLARSEN's excellent example above.

- Gary Patterson

CSLARSEN

Hi
Any luck with the suggestion?

uid="Your_own_username"
pwd= "Your_password"
con.Open "Driver={Client Access ODBC Driver (32-BIT)};System=MYSYS;UID=" & uid & ";PWD=" & pwd

cheers
cslarsen

CSLARSEN

I object,
the solution provided did capture a problem with the connection string.
cheers
cslarsen

Gary Patterson, CISSP

CSLARSEN provided a correct solution and should be awarded the points.

- Gary Patterson