Solved

Interfacing MS Access 2010 with MS SQL Server 2008

Posted on 2013-02-06
11
572 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

696 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