Problem to connect Access 2003 to SQL Server 2005 Express

Problem to connect Access 2003 to SQL Server 2005 Express

• drew_p

  I also have this problem.

  It is not possible to create any objects in SQL Server 2005 with Access 2003 with this driver (create an ODBC data source in the usual way). It will allow you to connect to SQl Server and read and write data to an existing table but that seems to be all.

  I have searched on office upddate for an update to Jet 4.0 or ODBC drivers required to solve this problem but I am unable to locate any.

  For what its worth Open Office 2.0 WILL create tables via ODBC although it comes up with an error which makes you think it's failed. However on checking with the Management Studio the table had been created but the test data hadn't been entered.

  BTW. I also have visual Studio 2003 and also will not work with SSE2005.

  I hope someone finds a solution to this soon.

  Thanks

  Ray

  
  

• Bijivb

  G'day,

  I've just been testing some an Access 2000 DB against a new SQL 2005 database and it all worked ok once I'd made the appropriate syntax changes for the queries. When I migrate the database to Access 2003 via creating a new Access 2003 database and importing all the objects I found the tables were all read only until I droped them all then created a new link to the SQL 2005 database.

  I've also got to distribute the ODBC drivers for SQL 2005 and all I can think is that the SQL Server installation file includes those drivers and a command line install of the workstation components might provide the answer. I've yet to test this theory and I'm not keen for users to have SQL Mgmt Studio as part of a deployment.

  I must admit I found ADP to be restrictive due to the lack of local tables, which I find I need to use to extract data in all the formats the clients need that data, so droping ADP support is fine by me.

  Our company has just resigned it's self to the fact that Access is now a very second class development platform based on the lack of .net support and competting reporting tools like SQL Reporting services. Not that they are better/worse, just MS doesn't seem interesting in Access evolving, from what I can see. It's a shame, because Access was an excellent solution for 80% of the database jobs in the business world.

  
  
  

• Neil Fraser

  I have the same problem. I have been using the server management studio express to do table design, however there are some things that are very difficult to do this way. For example, creating a combo box in a table. This is very simple in access yet next to impossible with the limited server management studio express. When can we expect a patch for access 03 to be able to do the table modifications to sql server 2005

  Thissucks.

  
  

• Jan Kučera

  Adps are getting hung out to dry. Check it out.
  
  http://www.databaseadvisors.com/gazette/sqlexpress.htm
  
  I had a Access front for a SQS 2k db and after I installed SQS 2005 I can simply look at the data in Acces--I can't change it. Also, dbos created in Access cannot be accessed in SQS 2005 (at least I have not found out how to view a diagram creatd with MSDE in SQS 2005.)
  
  
  
  
  
  

• arkiboys

  I don't know what's wrong but I would suggest also posting this problem on an Access forum. The error message coming from Access almost makes it sound like this is not a supported scenario. I would try to find out there.

  Hope this helps,
  Vaughn

  
  
  

• Pwint

  Hi, great point. I am also having difficulty opening ADP files in SQL Server (with visual studio 2005). What do you mean by "creating an ODBC connection using the SQL Native Client" Could you briefly state how to initiate this

  Thanks,
  Igor

  
  

• LtScho

  I was checking out your solution it fit my problem all the way up until I got to the MS Access part. Where I need to create the DSN first. The Native Client works fine, it did it's little pre-op text and returned successful.

  But, my question is this once you done that "How do you get to set up the DSN if Access doesn't see it in the domain for the server, from a client PC ".

  
  
  

• Adam Covington

  This is a the answer of from Microsoft:

  >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

  Bug ID: FDBK43607 Problem Type: Bug Status: Resolved Resolution: By Design Microsoft Status: Reviewed Opened Date: 2006-01-09 08:47:56 Opened By: Klaus.Lucas

  Product/Technology: SQL Server 2005 Version: SQL Server 2005 - Express Edition (32) Product Language: German Category: SQL Express OS: Windows XP SP2 Professional OS Language: German Submission Language: English

• rf09

  Hello Wendell,

  you're right I connect with an ADP because my plan is to move my small application from an MS SQL server 2000 to Express. Within SQL Server Management Studio I configured also that the DB act as an SQL 2000 server. Access 2003 ignore that. You're also right that I do the design work within SQL Server Management Studio but that's no so comfortable.

  :-) Klaus

  
  

• PublicError

  I can confirm that Access 2003 can handle pass through queries to SQL Server 2005 in all our Access 2003 solutions.

  The only troubles we encounted were the following

  1. Access VBA references broken - will break bound forms, especially around dates.
  2. Access 2003 sub form binding to local queries that hook into linked tables didn't work until you bound to the direct table. We had taken a neater approach with all Access 2003 forms to use a "qryfrmName" query as the datasource for all forms and sub forms. This basically failed with SQL 2005, so we just bound directly to tables and deleted those inbetween queries.
  3. Oh and the whole application ran a sh!t load faster, making us think we should have convinced the client to do it earlier.

  
  
  

• jv_getmore

  I've just tested the following process to ensure the SQL 2005 client components will get installed on a Windows XP machine.

  • Download and extract the installation image of SQL Server from Microsoft.
  • Review the document “RequirementsSQL2005.htm” in the extracted files.
  • Pre-install Microsoft Windows Installer 3.1. E.g. WindowsInstaller-KB893803-v2-x86.exe
  • Pre-install Microsoft .net Framework 2.0 (+ any service packs). E.g. dotnetfx.exe
  • Review the document “How to: Install SQL Server 2005 from the Command Prompt” for Microsoft SQL Server online books (from a server installation) or online.   Command line example
    C:\Windows\System32\msiexec /I "<CD or DVD Drive>\SQL Server x86\Servers\setup\sqlncli.msi" /qb /Lv c:\MSILogs\SQLClientLog.txt
  • Review the MSI command line options.
  • On Windows XP, Open the Control Panel -> Administrative Tools -> Data Sources (ODBC) and Click on the Drivers tab and confirm that there is an entry named "SQL Server Native Client" 2005.90.1339.00 (pre SQL SP1
  • Open Microsoft Access and add a linked table to a SQL Server 2005 instance. You will have to setup an ODBC DSN first.

  There is no re-boot necessary, thank god.

  Hope this helps.

  
  
  

• Robert Wakeland

  Are you connecting using an Access ADP or MDB file I suspect you are working with an ADP - so you might try creating an ODBC connection using the SQL Native Client and see if that lets you make design changes and additions. The other option is to use the SQL Server Management Studio Express to do table design work.
  
  

• Scionwest

  Can't you use an ODBC pass through query from access 2003 to SQL 2005 I think you can.
  
• Problem to connect Access 2003 to SQL Server 2005 Express