Solved

Interfacing MS Access 2010 with MS SQL Server 2008

Posted on 2013-02-06
11
560 Views
Last Modified: 2013-02-12
I have an MS Access 2010 front-end application that I want to interface with an MS SQL Server 2008 back-end database. The application uses DAO.

What connection string should I be using to refresh the table links?

Below are the connection strings I've tried:
DRIVER=SQL Server;SERVER=[Server],[Port];DATABASE=[Database Name];UID=[Username];PWD=[Password]

ODBC;DRIVER=SQL Server;SERVER=[Server],[Port];DATABASE=[Database Name];UID=[Username];PWD=[Password]

Open in new window

I get some error about login failed for the user. However, I'm able to login to the SQL Server database using the same credentials with MS SQL Server Management Studio.
0
Comment
Question by:bejhan
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 19

Assisted Solution

by:Rimvis
Rimvis earned 100 total points
ID: 38862962
Does the user has access to the database? Can you open it in Management Studio?
0
 
LVL 27

Assisted Solution

by:MacroShadow
MacroShadow earned 100 total points
ID: 38862963
ODBC;DRIVER={SQL Server};SERVER=[Server],[Port];DATABASE=[Database Name];UID=[Username];PWD=[Password]
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 100 total points
ID: 38863314
If you already have linked tables, one way to review the current format of the connection string is to do this in the Immediate window:

?Currentdb.TableDefs("OneOfYourLinkedTables").Connect

Press Enter, and you should see the connection string in the Immediate window. That should give you insight on the proper way to build a connect string.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 200 total points
ID: 38863890
Your SQL server is probably setup to use trusted connections, so rather then passing a username password in the connection string, it will need to have:

Trusted_Connection=yes;

in it.  It will look something like this:

    tdfCurrent.Connect = "ODBC;DRIVER={SQL Native Client};DATABASE=" & _
                        DatabaseName & ";SERVER=" & ServerName & _
                        ";Trusted_Connection=Yes;"

 I would suggest to troubleshoot you try setting up a DSN and make sure you can connect with a DSN.   At that point, you can create a linked table using the DSN and test that way.

Finially, you can convert that to a connection string by using the code here:

Using DSN-Less Connections
http://www.accessmvp.com/DJSteele/DSNLessLinks.html

These are the steps I walk through when I have a problem with a connection string.

 Last, a good place for connection strings is: http://www.connectionstrings.com/

Jim.
0
 
LVL 1

Author Comment

by:bejhan
ID: 38870410
@Rimvis: Yes, I can log in to Management Studio with those credentials and access the database.

@MacroShadow: I didn't have any luck with the connection string you suggested.

@LSMConsulting: Before being moved to the Access 2010 format, this application used an Access 2003 back-end database so the linked tables won't give any insight.

@JDettman: What is the difference between the SQL Server and SQL Native Client drivers?
I will try your DSN suggestion tomorrow.
0
 
LVL 1

Author Comment

by:bejhan
ID: 38874360
I forgot to prefix all of my source table names with "dbo."

I will split the points since the question is moot.
0
 
LVL 57
ID: 38875768
<<What is the difference between the SQL Server and SQL Native Client drivers?
>>

 The native driver is supposed to be better optimized for SQL then the default ODBC one is what I've been told.

 I've never tested if there really is a difference.  I always use the ODBC one.

Jim.
0
 
LVL 57
ID: 38875773
FYI I just checked and the difference is that the SQL Native driver has support for features in SQL2005 and up.

SQL Driver supports the feature set of SQL 2000.

I have to say though, I've never run into problems with the regular driver, but then I typically don't stray into database specific features.   I like to keep calls generic.

Jim.
0
 
LVL 1

Author Comment

by:bejhan
ID: 38881093
SQL Native Client driver does not seem to work for me. Is must only be for the situation where you are running the server on the same machine as the application.
0
 
LVL 84
ID: 38881636
Is must only be for the situation where you are running the server on the same machine as the application.
No, the Native Client works on any machine, if it's installed on the machine (and supported on the platform, of course).
0
 
LVL 1

Author Comment

by:bejhan
ID: 38881704
Ah my machine doesn't seem to have the Native Client driver, I guess I'll just stick to the SQL Server driver.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

790 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