Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035

    .adp in access 2007 to SQl server 2008

    Hi,



    I have some .adp projects and could create new ones with Access 2007 (2003 format) on SQL Server 2005.
    Since I upgraded to SQL server 2008, the Access .adp creation process can't find the SQL server anymore. Although it is possible to establish an ODBC link to the SQL serve, I can't create a new .adp project to it.
    So I searched MSDN and the office download centre for available office upgrades, but couldn't find the one that would enable me to create .adp projects on a SQL server 2008.
    So maybe one of you knows the link to the update I need?

    Thx
    NG

  2. #2
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    If I recall when working with ADP's a few years back, I thought there was a problem with ADP's and one of the SQL Server versions which Microsoft never cared to fix and I recall that it had to do with the ODBC connection. I don't design ADP's anymore since it's much faster to simply develop an mdb with SQL Server linked tables and if I want the same speed I would get out of an ADP, I simply design the forms in an unbound fashion (ie. functions to write/retrieve data to/from unbound fields on the form.) There really wasn't a significant performance difference in an ADP versus an mdb using unbound forms (I was working with millions of records and noticed only milliseconds in speed difference). The one thing an ADP has an advantage of over an mdb is that you work directly with the SQL Server tables in an ADP (I had one of my developers delete all the tables in an ADP thinking they were just linked and he was actually deleting the tables off the SQL Server which I had to restore backups of then.)

    I haven't seen Microsoft giving as much support to ADP's as much as they should have and it wasn't worth twice the coding time developing an ADP versus an mdb. I can write code for an mdb twice as fast as an ADP and using SQL Server linked tables is much easier (you still establish an ODBC DSN but there isn't the same type of issues as with an ADP).

    I'm not telling you to abandon ADP's but I did and was glad I did and never returned to writing ADPs. My guess is that Microsoft is slowly abandoning support for ADPs but I could be wrong since I haven't created one in years.

  3. #3
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Thanks for the reply, but it's not really a speed issue why I need to use an ADP, but a large part of the business rules are implemented using SQL server procedures and I need to manipulate those from within my application, hence the need for an adp project.

    greetings
    NG

  4. #4
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    You can also easily call/interact with a stored procedure within an mdb. I can't recall the syntax offhand but I used to do it all the time within my mdb.

  5. #5
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    just found the problem! The TCP/IP protocol of the SQL server wasn't enabled. Enabled it through the configuration tools, stopped and restarted the SQL server, and all works fine again. Thanks pKStormy for your advice, it's certainly worth considering it!

  6. #6
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    Thanks for the post on the solution! That probably would've helped me years ago when I was working with ADP's although I think I was making some other assumptions with your post that I shouldn't have been.

    Something to keep in mind if there's ever another post with a similar problem.

    Thanks again.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access and SQL Server?
    By evander in forum Database Design
    Replies: 2
    Last Post: 07-31-2010, 07:47 PM
  2. Replies: 3
    Last Post: 07-15-2010, 05:53 PM
  3. Access Frontend to Sql server db
    By ksukat in forum Import/Export Data
    Replies: 0
    Last Post: 06-08-2010, 01:35 PM
  4. Convert Access 07 ADO code to SQL Server Express 2008
    By jrdnoland in forum Programming
    Replies: 8
    Last Post: 04-14-2010, 05:57 PM
  5. Replies: 0
    Last Post: 11-17-2009, 02:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums