ODBC Connection to SQL Server from Access 2010 Front-end

Posted on 2011-05-06
Medium Priority
Last Modified: 2012-06-27
On a Windows Server 2008 (on a 64bit machine) - Server Manager - Microsoft Corporation - Version: 6.0.6002.18005

I am trying to create passthrough queries in Access 2007 SP2 MSO and am unable to get the connection string right. I've attached screen shots of all the settings - that's I think may be useful... and also a shot of the error message I am getting.

Microsoft SQL Server Management Studio       10.50.1600.1
Microsoft Data Access Components (MDAC)      6.0.6002.18005
Microsoft MSXML                                      3.0 4.0 5.0 6.0
Microsoft Internet Explorer                        8.0.6001.19048
Microsoft .NET Framework                        2.0.50727.4211
Operating System                                    6.0.6002

Any help would be appreciated.

Thanks a lot!
Question by:Eileen Murphy
  • 3
LVL 22

Assisted Solution

by:Nico Bontenbal
Nico Bontenbal earned 2000 total points
ID: 35708612
It's a pass through query. Which means you should use SQL Server syntax. This is MS Access syntax. Start with
Select * from tblCarrierDatabase

Open in new window

as the query text, and work your way up from there, step by step.
LVL 22

Accepted Solution

Nico Bontenbal earned 2000 total points
ID: 35708630
Create your queries in Microsoft SQL Server Management Studio and then paste them in the passthrough query.

Author Comment

by:Eileen Murphy
ID: 35708640
You omit the dbo_??
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35708796
If you want the dbo try
Select * from dbo.tblCarrierDatabase

Open in new window

I suppose the table is called tblCarrierDatabase on SQL Server, not dbo_tblCarrierDatabase. Otherwise just start with
Select 1 as value

Open in new window

as the query. You'll see you'll get a result. It is not the connection that is the problem. It is the query.
LVL 23

Expert Comment

ID: 35711717
- have you got it working? i might be wrong, but the syntax might be like this:

SELECT tablename.columnname
FROM database.dbo.tablename


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
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: …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

829 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