We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Connection String with variable userid/password does not work

Medium Priority
1,044 Views
Last Modified: 2013-12-17
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

Comment
Watch Question

Commented:
Hi

To be able to capture the value of the parameters they must be "allowed" to deliver their value.

the current strin just resolves to a username equal to the specific characters of "uid" and for the password it resolves to "pwd"

to have the string resolve the value of these two parameters
they must not be placed inside a string..
You need something like this:

con.Open "Driver={Client Access ODBC Driver (32-BIT)};System=MYSYS;UID=" & uid & ";PWD=" & pwd
you can try to make a debug.print to check if the assigned value to your parameters resolve correctly  
e.g.
debug.print  "Driver={Client Access ODBC Driver (32-BIT)};System=MYSYS;UID=" & uid & ";PWD=" & pwd

cheers
cslarsen

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Gary PattersonVP Technology / Senior Consultant
CERTIFIED EXPERT

Commented:
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

Commented:
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

Commented:
I object,
the solution provided did capture a problem with the connection string.
cheers
cslarsen
Gary PattersonVP Technology / Senior Consultant
CERTIFIED EXPERT

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

- Gary Patterson
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.