Results 1 to 3 of 3
  1. #1
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    ODBC connections to SQL Server Express (Access 2K and up - SQL 2005)

    After struggling for some time to create an ODBC connection to a SQL Server 2005 Express so I could connect an Access MDB file to it using ODBC, I stumbled across some information that may well be useful to others. Two things must be done to successfully connect:<UL><LI>When creating the ODBC Data Source specify the SQL Native Client as the Driver.<LI>To enter the Server Name in the Microsoft SQL Server DSN Configuration dialog box you must specify the instance as well as the server name, i.e. MyServerSQLEXPRESS or .SQLEXPRESS if you are on the same PC as the SQL Server.[/list]Hope this helps others.
    Wendell

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: ODBC connections to SQL Server Express (Access 2K and up - SQL 2005)

    Thanks for the tip - having just downloaded the express versions of VB, C# and SQL 2005, I suspect you've just saved me a few headaches! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: ODBC connections to SQL Server Express (Access 2K and up - SQL 2005)

    Well, it appears there are some other headaches. According to a post in the MSDN forums, using an ADP from Access to connect to SQL Server Express, you are not allowed to make any design changes to the back-end database. The same is true for Access MDBs connecting via ODBC - you can't use any of the DDL commands to manipulate the back-end database design. I suspect we may see some problems with ODBC-direct and Pass-Through queries, but haven't done any testing yet.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •