Access ODBC to SQL Server 2012

    General discussion

  • Has anyone tried linking an Access front end to SQL Server 2012 using ODBC linked tables? I don't have 2012 installed yet, so can't try it myself.

    I am currently using Access 2007/2010 front end with SQL Server 2008 R2, and in profiler I see the warning message when Access generated SQL is executed:
    "The use of more than two-part column names will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use it."

    Just wondering if we will still be able to use Access as a front end to SQL Server 2012.

    Tuesday, May 29, 2012 1:20 PM

All replies

  • Post the example of SQL command, I have 2012 RC0 installed and will be able to try later. But the answer is 100% yes, we will. Maybe there are some minor changes we should be aware of.

    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog

    Tuesday, May 29, 2012 1:43 PM
  • Hi Andrey,

    No special command. Just link a table and open it.
    Not that is should make a difference, but I use DSN-less connections.

    Tuesday, May 29, 2012 2:14 PM
  • Hi Alphonse,

    so it works well with RC0. But one question, what does this more than two-part column names actually mean? Should I test the column name which consists of 3 words? I'm confused a little what exactly should be depricated according to the message.

    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog

    Tuesday, May 29, 2012 7:17 PM
  • Thanks Andrey,

    If you run SQL Profiler and open an ODBC linked table in Access, you will see something like

    SELECT "dbo"."tblEmails"."EmName" FROM "dbo"."tblEmails" in the trace textdata column

    Just before it, will be the deprication warning (if your trace includes warnings).

    "dbo"."tblEmails"."EmName" is a three part column name. And (of course) we have no control over the SQL that Access sends to SQL Server.

    Tuesday, May 29, 2012 7:32 PM
  • Seems there is no Profiler in my current installation. Good reason for upgrade from RC0 to release version. I'm not sure when but I'll be back and right after getting a Profiler, interesting observation.

    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog

    Tuesday, May 29, 2012 8:36 PM
  • I typically use profiler whenever building any query other than real simple ones. Sometimes Access can surprise one in how it constructs the SQL to send to SQL Server. So, I've been seeing that message for some years now and keep wondering what will happen when SQL Server no longer allows that syntax.

    If you want to read something else interesting, read this thread when you have some time.

    Tuesday, May 29, 2012 9:30 PM
  • Alphonse,

    quick question: do you know whether Profiler is available in Express edition of SQL Server or should I install Standard edition at least?

    BTW, I can't see such warning working with SQL Server 2005 and Access 2003. But the request sent to the server has the same syntax, e.g. SELECT "dbo"."tCities_dic"."CityID" FROM "dbo"."tCities_dic"

    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog

    Wednesday, May 30, 2012 2:25 PM
  • I'm pretty sure Profiler is not included with Express.

    I installed the Express engine so I would have the same edition as client, and the tools from Developer edition.

    I have all editions with my MSDN subscription, but if you don't have one, Developer edition is quite cheap.

    I'm guessing that the warning started with 2008, or maybe your trace is just not showing warnings.

    Wednesday, May 30, 2012 5:20 PM
  • Alphonse,

    finally, I have SS2012 (yes, developer edition from MSDN sunscription) with Profiler. So where should I switch on these warnings? By default there are no any additional info. 

    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog

    Sunday, June 10, 2012 3:29 PM
  • This is from 2008 R2. Don't know if its changed in 2012.

    Trace Properties (available when starting a new trace) > Events Selection tab

    Check 'Show all events'. Then under the Events column, there is a section for Deprecation. Check everything.

    Sunday, June 10, 2012 4:40 PM
  • Ok, checked'em all. Still see no warning.

    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog

    Sunday, June 10, 2012 6:36 PM
  • Andrey,

    I did a little more playing around and it seems that the message only appears the very first time the table is open in a session. If I close the table and re-open it, there is no deprecation announcement.

    Sunday, June 10, 2012 7:16 PM
  • Either this deprication was depricated itself :), or I'm still doing smth wrong. The first case is preferable.

    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog

    Sunday, June 10, 2012 8:01 PM
  • Interesting.

    Which native client are you using? I am using 10.0.

    The only other variables that I can think of is that I am using DSN-less connection with SQL Server Authentication and NOT saving password. However, I can't see any of that making a difference.

    Do you have SQL Server 2008 R2 installed? If so, could you try with that?

    Sunday, June 10, 2012 8:09 PM
  • My driver is 11.0 which comes with SS2012 installation package. The connection is DSN-less as well but with Windows Authentication.

    Unfortunately, I had a lot of conflicts and issues while attempting to install 2012. Finally, I had to remove 2008 R2 and even Visual Studio. AFAIR, somebody else said here that he had 2008 R2 installed. 

    And the last clarification: I should just open a linked table and trace exactly this event, nothing special?

    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog

    Monday, June 11, 2012 9:31 AM
  • Yes, that's it. Only the warning seems to appear on only the very first time the table is opened.

    I'll probably be installing 2012 myself within the next several weeks, so I'll see what happens then also.

    Monday, June 11, 2012 12:45 PM