Connection String - SQL Server Express

  • Thread starter Thread starter Ross
  • Start date Start date
R

Ross

I am trying to establish and ADODB connection from Access 2003 to SQL Server
Express 2008.

This is the Syntax format that I found:

Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog=
Your_Database_Name;UserId=Your_Username;Password=Your_Password;"

Here is my vba code:

cn.Open "Provider=SQLOLEDB;Data Source=OWNER-PC\SQLEXPRESS;Initial
Catalog=ReportingDB;UserId=Owner-PC\Owner;Password=pswd2;"

My error is: "Invalid connection string Attribute"

How can I find and fix the error?

Many Thanks

Ross
 
First, you need to put a space in "User Id", second, the User Id won't work
with a Windows Account but only with a SQL-Server Login Account. If you
want to use a Windows account, you must use a Trusted Connection (ie, set
the Integrated Security to SSPI without setting both the the User Id and the
Password because the current credential will be used exclusively; see
http://www.connectionstrings.com/sql-server-2008 ).

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)
 
First, you need to put a space in "User Id", second, the User Id won't work
with a Windows Account but only with a SQL-Server Login Account. If you
want to use a Windows account, you must use a Trusted Connection (ie, set
the Integrated Security to SSPI without setting both the the User Id and the
Password because the current credential will be used exclusively; see
http://www.connectionstrings.com/sql-server-2008 ).

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)
 
Sylvain,

Thank you for this response. My new install SQL server express was wet up
with windows authentication. I simply added SQL server authentication and my
string worked perfectly (THE FIRST TIME)!

Thank you again.

Ross
 
Sylvain,

Thank you for this response. My new install SQL server express was wet up
with windows authentication. I simply added SQL server authentication and my
string worked perfectly (THE FIRST TIME)!

Thank you again.

Ross
 
I am having a similar problem. I would like to connect to a sql server installed in my local machine. I am using windows authentication to connect.

the string I have is below, but does not seem to get me connected.

"Provider=SQLOLEDB;Data Source=localhost\sqlexpress;Initial Catalog=RnrBooks;Integrated Security=SSPI;User ID=spike_laptop-PC\spike_laptop;Password=;"
 
Back
Top