Hardcoded connection string works, built one doesn't (VB6 to SQL Server 2008 R2)

Question

I'm having trouble building a connection string that my VB6 program can use to connect to a SQL Server 2008 R2 database. When I hardcode the connection string in my program it works fine and I'm able to access the database. Here's the code that works:

gcnTheEstimator.Open "Provider=SQLNCLI10;Server=KEVIN-PC;Database=The_Estimator", "sa", ""

However, neither of the following work:

gcnTheEstimator.ConnectionString = "Provider=SQLNCLI10;Server=KEVIN-PC;Database=The_Estimator, sa"

gcnTheEstimator.Open

(Running it gives me this error message: Invalid Authorization Specification)

gcnTheEstimator.ConnectionString = Chr(34) & "Provider=SQLNCLI10;Server=KEVIN-PC;Database=The_Estimator" & Chr(34) & "," & Chr(34) & "sa" & Chr(34) & "," & Chr(34) & Chr(34)

gcnTheEstimator.Open

(Using a msgbox to show this connection string returns a connection string that is EXACTLY the same as the hardcoded one shown above that works. However, running it gives me this error message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.)

I've tried several other variations and none of them work.

I need to deploy my VB6 program at two other locations that are both using SQL Server 2008 R2, so I need to build their connection strings in my program. What am I doing wrong here? TIA

toby · Accepted Answer · 2011-04-01 20:20:28Z

2

gcnTheEstimator.Open is expecting 3 arguments, each separated by a comma (you are trying to provide all three in a single string variable). This should work:

dim connStr as string
dim usernameStr as string
dim passwordStr as string

connStr = "Provider=SQLNCLI10;Server=KEVIN-PC;Database=The_Estimator"
usernameStr = "sa"
passwordStr = ""

gcnTheEstimator.Open connStr, usernameStr, passwordStr

answered Apr 1, 2011 at 20:20

toby

9023 gold badges10 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kevin Over a year ago

That worked! I could have SWORN I tried that one too, but I guess I must not have. Thanks!

Collectives™ on Stack Overflow

Hardcoded connection string works, built one doesn't (VB6 to SQL Server 2008 R2)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related