Results 1 to 7 of 7
  1. #1
    LAazsx is offline Advanced Beginner
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Nov 2010
    Posts
    30

    Can't connect Access 2010 to SQL Server 2008 R2


    Hi, I've made an adp file using Access 2010. I can't seem to connect it to SQL Server. I've entered the server name and user name fields properly but i keep on getting the error
    "{DBNETLIB][ConectionOpen (Connect()).]SQL Server does not exist or access denied.". I tried using the PC name for server name and windows authentication just like how i login into SQL server. Any thoughts?

  2. #2
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Haven't ever created an adp file, but maybe one of these links will help:

    http://www.connectionstrings.com/sql-server-2008


    http://www.sqlstrings.com/SQL-Server...on-strings.htm

  3. #3
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    ADP in 2010? Yuch. I thought MS was ditching ADP support starting with MSAccess 2007. I'd personally consider just designing an accdb, create an ODBC DSN, and then link the SQL Server tables into the accdb frontend. I do this all the time without any problems and working with an accdb is much easier than an adp.

    You really don't gain a lot of benefits with an adp versus an accdb using linked tables. It's much easier working with an accdb versus an adp. I gave up on adp's back in the year 2000 since they were more hassle than anything.

    Otherwise have you tried using the IP address versus the name?

    Also keep in mind that MSAccess 2010 still hasn't worked out all their bugs. I've had problems with 2010 and reverted all our users back to 2007. SQL Server 2008 though is nice.

  4. #4
    LAazsx is offline Advanced Beginner
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Nov 2010
    Posts
    30
    Quote Originally Posted by pkstormy View Post
    ADP in 2010? Yuch. I thought MS was ditching ADP support starting with MSAccess 2007. I'd personally consider just designing an accdb, create an ODBC DSN, and then link the SQL Server tables into the accdb frontend. I do this all the time without any problems and working with an accdb is much easier than an adp.

    You really don't gain a lot of benefits with an adp versus an accdb using linked tables. It's much easier working with an accdb versus an adp. I gave up on adp's back in the year 2000 since they were more hassle than anything.

    Otherwise have you tried using the IP address versus the name?

    Also keep in mind that MSAccess 2010 still hasn't worked out all their bugs. I've had problems with 2010 and reverted all our users back to 2007. SQL Server 2008 though is nice.
    Oh. I thought adp is the required format for that. Thanks.

  5. #5
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    ADP is different than a normal accdb type file. ADP has a direct connection to the backend SQL Server tables whereas an accdb uses linked SQL Server tables.

    With an ADP, you can modify the tables directly within the ADP project instead of opening SQL Server. I'd personally still use SQL Server to do any table modfications (and again, avoid using an ADP alltogether.)

    An ADP typically requires 2-3 times to develop versus an accdb (or mdb). Mostly because everything has to be coded. In an accdb/mdb, you can take advantage of some of MSAccess's features to make thing easier and quicker for designing.

    I remember the first time I was training other developers on ADP. They deleted all the tables in the ADP (thinking they were the same as linked tables) and didn't realize that they had actually just deleted all the tables on SQL Server itself!

  6. #6
    Mgomp is offline Novice
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Dec 2010
    Posts
    8
    Quote Originally Posted by pkstormy View Post
    I'd personally consider just designing an accdb, create an ODBC DSN, and then link the SQL Server tables into the accdb frontend. I do this all the time without any problems and working with an accdb is much easier than an adp.
    Hello!
    I have the same problem using adp with 2010.
    After the new "web-lookalike" form and menu system, it is not getting any better...

    But, Could you please - step by step - tell me how you use an ordinary accdb as a front end to an sql? I have MS sql server express installed on my comp for testing.

    What I need to know is how you deal with all queries which (at least by the book....) should stay on the sql-server. And what type of connection you are useing. ...and other tips...

    Regards.

  7. #7
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    If you're using SQL Server Express, (I think but I'm not 100% sure) you can't distribute your MSAccess application for others to use since (again, I think) SQL Server Express doesn't support connections outside the computer it's on.

    But to set up a SQL Server linked MSAccess file:

    1. Create your SQL Server tables/fields/db.
    2. Create an ODBC DSN connection on the computer(s) (ODBC Administrator in Control Panel) you're going to have the application running on - (Important Note: Every ODBC DSN connecting to the SQL Server db MUST have the EXACT same ODBC DSN name - this is important.) I personally use code (in the code repository section on this site) to put code in my MSAccess application to automatically create the ODBC DSN when the MSAccess app is opened.
    3. Open MSAccess and link the tables (just as you would linking in another MSAccess table.) The difference is that you'll select ODBC Databases (ie. File -> Get External Data -> Link Tables, and then select ODBC Databases as the driver. You should then see the ODBC Manager popup to select the SQL Server db.)
    4. Continue designing your MSAccess application as normal. The SQL Server linked tables act like any other linked tables. Design your queries in MSAccess based on the linked SQL Server tables. You don't need to write Stored Procedures on SQL Server unless you want to learn a whole new process. Normal queries in MSAccess work just fine against the linked tables. I do this all the time without any issues.

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

Similar Threads

  1. Replies: 1
    Last Post: 09-26-2010, 04:17 PM
  2. .adp in access 2007 to SQl server 2008
    By NoellaG in forum Access
    Replies: 5
    Last Post: 09-07-2010, 09:18 PM
  3. Replies: 3
    Last Post: 07-15-2010, 05:53 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: 5
    Last Post: 03-29-2009, 07:20 AM

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