Problem to connect Access 2003 to SQL Server 2005 Express

Question

Hello,

after successfully connect Access to the Server I want to add a table. I got an error message:

With this version of MS Office Access you are not able to make any drafts because this version of SQL server you are connected to does not support this. Check the MS Office update website for the newest downloads.

You habe connected to a version of SQL server which is newer than SQL server 2000.

Versions:

SQL Server runs on a MS Server 2003 SP1

Access 2003 (11.6566.6568 SP2) on an MS XP Pro SP1

all on actual patch level. Any ideas what's wrong?

:-) Klaus

 

 

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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.

Answer 6

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

---

Description:	Opened by Klaus.Lucas on 2006-01-09 at 08:47:56     Hello, I have a problem with SQL Server 2005 Express and Access 2003. I successfully connect Access with the DB. Now I select "Table" and "New". An error message appears telling me (my translation of the german messsage text): With this version of the Office Access no changes could be done because this version of the MS SQL server does not support this. Look for Office update on the web..... Then another message: You habe connected to an SQL server version which is newer than SQL server 2000.....     Edited by Klaus.Lucas on 2006-01-10 at 02:05:06     here the product version of the products: Client: Win XP german SP1 MS .Net Framewark 2.0 (I miss an entry under Manage like Framework 1.1 do) Access 2003 german SP2 (11.6566.6568) Server: Server 2003 Std. engl. SP1 Microsoft SQL Server Management Studio Express 9.00.1399.00 Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447) Microsoft MSXML 2.6 3.0 4.0 6.0 Microsoft Internet Explorer 6.0.3790.1830 Microsoft .NET Framework 2.0.50727.42 Operating System 5.2.3790 Resolved as By Design by Microsoft on 2006-01-13 at 09:08:04     The reson for the error seen is that the version of Access used hasn't been updated for SQL Server 2005. PLease see: http://msdn2.microsoft.com/en-us/library/ms165883.aspx Edited by Klaus.Lucas on 2006-01-14 at 01:08:18     I found that on the above link: >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> You have connected to a version of SQL Server later than SQL Server 2000. The version of Visual Database Tools that you are using with Visual Studio or Access was released before the version of SQL Server to which you are connected. For this reason, you might encounter problems. To correct this error: Delete the data connection to the newer SQL Server, and connect only to SQL Server 2000 or earlier versions. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Is that right that Access 2003 is not able to connect to MS SQL Server 2005? I also tried to set the compatibility lebel of the database to "SQL Server 2000 (80)" without any success (see opt.zip). Reopened by Klaus.Lucas on 2006-01-16 at 21:29:05     As Access 2003 was not able to connect to SQL Server 2005 the database should act as an SQL Server 2000 with the correct compatibility level. Resolved as By Design by Microsoft on 2006-01-24 at 17:40:03     The database compatibility level only controls a minor set of behaviors of the database. It doesn't fully emulate previous releases. Unfortunately, Access 2003 and earlier can't be used to design against SQL 2005. ADP projects should still "run". The same is the case with MDBs with Linked Tables to SQL 2005.
Steps to Reproduce:	Connect to a SQL Server 2005 with Access 2003
Actual Results:	no changes could be made
Expected Results:	from Access to manage tables   :-) Klaus

Answer 7

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

Answer 8

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.)

Answer 9

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.

Answer 10

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.

Answer 11

Can't you use an ODBC pass through query from access 2003 to SQL 2005? I think you can.

Answer 12

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.

 

Answer 13

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?".