Connect from Access via VBA using ADODB connection to SQL Server Express 2012

Question

I am trying to connect to a SQL Server 2012 Express from a test application within MS Access 2003. The Access database (frontend application) exists on the same server as the SQL Server instance.

I am currently trying to use ADO to connect. My code is listed below. When I run my test code, I receive an error message that states:  (800004005) Cannot open Database "ContactSales" requested by the Login. The login failed.

I can use SQL Server Management Studio to connect to this server (database) using Windows Authentication without a problem.

I changed the database name in the code below to 'master' and this works (connects). So why can I not connect to my ContactSales database in the same instance?

 Dim sSQL As String
 Dim rs As ADODB.Recordset
 Dim cn As ADODB.Connection
 
Dim strCS As String
 
 sSQL = "select contactid, lastname " & _
        "from Contacts " & _
        "where ContactID = 1021"
 
 Set cn = New ADODB.Connection
 
strCS = "Provider=SQLNCLI11;" _
          & "Server=QBSERVER\SQLEXPRESS;" _
          & "Database=ContactSales;" _
          & "Integrated Security=SSPI;" _
          & "DataTypeCompatibility=80;" _
          & "MARS Connection=True;"
 
cn.ConnectionString = strCS
 
cn.Open

Answer 1

Hi,

See if you are experiencing an orphaned user account issue.

http://msdn.microsoft.com/en-us/library/ms175475.aspx

http://www.dbadiaries.com/using-sp_change_users_login-to-fix-sql-server-orphaned-users

If this is not the case then please let me know.

Also an MVP or MSFT Eng should be replying soon as well.

Good Luck.

Frank.

---

Frank Garcia

Answer 2

Frank,

  Thanks for responding. I resolved the issue. I miss spelled the database name. I was missing a letter 's' between Contact and Sales. So the correct database name was 'ContactsSales' and not 'ContactSales'.

Yes, I feel like such an idiot.

Thanks for replying.

Answer 3

This happens with everyone anytime, so don't feel like it and enjoy coding.

Sarah

www.sqlservermanagementstudio.net

Answer 4

That's great to hear. Thank you for posting the solution.

Frank.

---

Frank Garcia