• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 585
  • Last Modified:

Interfacing MS Access 2010 with MS SQL Server 2008

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
bejhan
Asked:
bejhan
  • 4
  • 3
  • 2
  • +2
4 Solutions
 
RimvisCommented:
Does the user has access to the database? Can you open it in Management Studio?
0
 
MacroShadowCommented:
ODBC;DRIVER={SQL Server};SERVER=[Server],[Port];DATABASE=[Database Name];UID=[Username];PWD=[Password]
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Independent Software Vendors: 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!

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
bejhanAuthor Commented:
@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
 
bejhanAuthor Commented:
I forgot to prefix all of my source table names with "dbo."

I will split the points since the question is moot.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
bejhanAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
bejhanAuthor Commented:
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!

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now