ODBC Connection to SQL Server from Access 2010 Front-end

Posted on 2011-05-06
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:Ei0914
    LVL 22

    Assisted Solution

    by:Nico Bontenbal
    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

    Create your queries in Microsoft SQL Server Management Studio and then paste them in the passthrough query.

    Author Comment

    You omit the dbo_??
    LVL 22

    Expert Comment

    by:Nico Bontenbal
    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

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

    SELECT tablename.columnname
    FROM database.dbo.tablename


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    737 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

    21 Experts available now in Live!

    Get 1:1 Help Now