Solved

Interfacing MS Access 2010 with MS SQL Server 2008

Posted on 2013-02-06
11
555 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

813 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now