?
Solved

Connection String with variable userid/password does not work

Posted on 2009-02-10
9
Medium Priority
?
938 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

0
Comment
Question by:FSOLL
  • 3
  • 2
5 Comments
 
LVL 15

Accepted Solution

by:
CSLARSEN earned 2000 total points
ID: 23608977
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
0
 
LVL 36

Expert Comment

by:Gary Patterson
ID: 23612310
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
0
 
LVL 15

Expert Comment

by:CSLARSEN
ID: 23710049
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
0
 
LVL 15

Expert Comment

by:CSLARSEN
ID: 23953529
I object,
the solution provided did capture a problem with the connection string.
cheers
cslarsen
0
 
LVL 36

Expert Comment

by:Gary Patterson
ID: 23959322
CSLARSEN provided a correct solution and should be awarded the points.

- Gary Patterson
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question