Using MS Access 2003 Project with SQL Server 2005

Using MS Access 2003 Project with SQL Server 2005

• Sergio Ora

  According to a book that I am reading you do not have the ability to create or edit the objects you were able to with SQL Server 2000. You can run the Views and SP's but you cannot edit them from within Access. You can update and change data but you can not change table properties from within Access. The book is by apress "Beginning SQL Server 2005 Express" page 27.
  

• Hernan Gatta

  I found the following MS KB article when looking up a solution to this problem:

  http://msdn2.microsoft.com/en-us/library/ms165883.aspx

  Not that it is much use. To quote: "Delete the data connection to the newer SQL Server, and connect only to SQL Server 2000 or earlier versions."

  Right...

  I am reminded of the old joke - "Doctor, it hurts when I do this". "Then don't do that!"

  
  

• bakintunde

  Bill,

  Thanks for the tip. I did as you suggested and still receive the same error. Any other thoughts

  Regards,

  Mike Duke

  
  
  

• Koen Vd B

  I can't seem to reference a view or stored procedure from the VBA code of my Access 2003 form. I'm trying to export to a fixed length file and the source is a stored procedure. Here's the code:
  
  DoCmd.TransferText acExportFixed, "", "dbo.sp_vDetails", "C:\BFSUpload\BFSDetails.txt"
  
  I've tried to reference the stored procedure without qualifying the owner and it doesn't work either. I am able to reference the table that is used by the stored procedure and I can build a form with the stored procedure as the data source. The code just isn't able to work. I get an error stating the object cannot be found.
  
  

• KSC

  Make sure the OLE DB provider being used by Access is the new SQL Native Client code just released with SS 2K5.
  
  

• Linedata Mike

  Bill, Mike ,
  

  I have similar related problem.

  
  I am using MSSql Express 2005 (I uninstalled MSDE and installed MSSql express 2005) and MSAccess 2005.
  
  I have an ADP that I have pointing to a SQL Express 2005 db (a db made with MSDE and now held by SQL Express 2005) and when I design view a VIEW I get a warning :
  
  "this version of ADP doesn't support design changes with the version of MSSQL Server your Access project is connected to. See the Microsoft Office Update web site for the latest info and downloads. Your design changes will not be saved."
  
  I checked Update site and no update for Access 2003 for support to MSSql 2005

  I also started from scratch and made a new ADP with Access 2003 and then pointed it the MSSQL 2005 Express db and it all loaded up. Then I tried to design view a VIEW and got the same warning.

  when I first was installed SQL Express 2005 I had an MDB (not an ADP) with no links to ODBC or SQL and the install said to shutdown MSAccess so I assumed it was installing drivers for Acc2003 to SQL2005 . I also rebooted

  Thanks

  Luke

  
  

• MikeGdecat

  Well, isn't that a kick in the pants. I guess I'm going to have to tell my client they can't have MS SQL Svr 2005 after all.

  Thanks to everyone that posted.

  
  
  
• Using MS Access 2003 Project with SQL Server 2005