Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Interfacing MS Access 2010 with MS SQL Server 2008

Posted on 2013-02-06
11
Medium Priority
?
584 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 400 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 400 total points
ID: 38862963
ODBC;DRIVER={SQL Server};SERVER=[Server],[Port];DATABASE=[Database Name];UID=[Username];PWD=[Password]
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 400 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 800 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 58
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 58
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 85
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

618 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