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

I'm having trouble building a connec开发者_JS百科tion 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

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