Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello. (Access 2007)
    I've started an ADP but am having trouble connecting to my server on my desktop pc.

    I have SQL Server (SQLEXPRESS). In the configuration I can start it as Local System
    or Local Service. Which do I need? Do I then need to supply a username and pasword?

    SQLEXPRESS also shows an Agent and Browser. Do I need these?

    Currently I can start a service but it doesn't appear in the list of server names in the Access
    Data Link Properties.

    Thanks for any guidance, Andy.

  2. #2
    Administrator
    Join Date
    Mar 2001
    Location
    St Louis, Missouri, USA
    Posts
    23,572
    Thanks
    5
    Thanked 1,057 Times in 926 Posts
    SQL Agent by default is set to start manually. It is used to execute jobs, send alerts, etc. I'd just leave it alone unless you are sure you need it to start automatically.

    SQL Browser is used manage client connections. I recommend you leave it alone.

    You have to make sure that your firewall is configured to allow access to SQL Server.

    I recommend you not use builtin accounts to start SQL Server. There is a whitepaper at Microsoft SQL Server 2008 White Paper: Security Overview for Database Administrators which explains in more detail.

    Joe
    Joe

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you. But I just want to use SQL Server on my PC to learn about it. So I'm hoping to just connect to it from an Access adp.

    If I select Local Service and it is running, why wouldn't it appear in my list of available servers in the adp connection dialog? Andy.

  4. #4
    Administrator
    Join Date
    Mar 2001
    Location
    St Louis, Missouri, USA
    Posts
    23,572
    Thanks
    5
    Thanked 1,057 Times in 926 Posts
    Quote Originally Posted by andrewgibsonsw View Post
    Thank you. But I just want to use SQL Server on my PC to learn about it. So I'm hoping to just connect to it from an Access adp.

    If I select Local Service and it is running, why wouldn't it appear in my list of available servers in the adp connection dialog? Andy.
    That is why you need to check all the security settings. What TCP ports are you using? What users have you configured the SQL Server db to allow access?

    Joe
    Joe

  5. #5
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks again. Looks like I'll have to do a load more studying before I can begin to use the server.

    In Access 2003 I just used username sa and password 'admin' and I was up and running.. Ah well, Andy.

  6. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Are you using SQLEXPRESS 2008 or 2005.


    Often you can type the name in the server box even if it is not there.
    Usual notation is PCName\SQLEXPRESS if it is the default instance.


    I recently had a similar issue.
    I rebuilt the main pc here (after being mugged by an MS Update)
    I have SQL Developer 2005 and Access 2007 under Windows 7 Ultimate.

    When I tried to create an Access adp and link to existing sql server
    I was getting similar connection problems.
    I tried PCName, IP address, localhost
    All no joy, and yet SQL Enterprise manager connected fine.

    Then instead of linking to an existing sql database, I used the wizard to
    create a new database. It did this ok, and after that there were no problems.

    The other thing I heard reported somewhere (cannot remember where),
    was that Access will only connect if SQL Browser service is running.
    I have not been able to verify if this was valid though.

    Sadly just another example of what a damaged effort Access 2007 is
    as a database development environment (subjective opinion that one).
    Andrew

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    Andy,

    I'd start with setting up and ODBC connection so that you can attach the SQL Tables to an Access MDB Database. ADPs are dissapearing in the next version of Access.

    Start at. Control Panel>Administrative Tools>Data Sources.

    Go to the System DSN Tab and click Add.

    Select SQL Server from the list and click Finish.

    Enter a name and Descritption and then

    The trick, as Andrew suggests, is to enter the Server as PCName\SQLEXPRESS, (it won't appear in the list) click next

    use "With Windows NT Authentication" then Next, Next and Finish.

    Test the Data Source.

    This new ODBC connection is available in Access by going to Get External Data> ADBC Datasource. Select the link option and then you''ll find it on the Machine Data sources tab.

    You can now select the tables you want to attach then work away as you would in any Access Database.

    See how you go with that.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Quote Originally Posted by kentg View Post
    Andy,

    ADPs are dissapearing in the next version of Access.
    Are you sure about this? Where can i read up on this?

  9. #9
    Administrator
    Join Date
    Mar 2001
    Location
    St Louis, Missouri, USA
    Posts
    23,572
    Thanks
    5
    Thanked 1,057 Times in 926 Posts
    Quote Originally Posted by patt View Post
    Are you sure about this? Where can i read up on this?
    I don't see any mention of ADP's being deprecated with Access 2010 at Changes in Access 2010.

    Joe
    Joe

  10. #10
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I don't think Projects are being removed, they are just making them
    harder to find and set up like they did on 2007.
    Removal of support I don't think would be wise.
    On the other hand ....

    However, this article implies that adp support remains, but not sure about how much of the new SQL 2008 Features

    Access 2010 Info
    Andrew

  11. #11
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    Pardon my Dyslexia - DAPs are being deprecated not ADPs. Sorry. Probably a bit of personal mindset. I've never really seen a future for ADPs (I think I got it right that time). I've always felt the same about Access Security and replication as well and since they are both gone I guess I expected ADPs to go with them.

    I'm currently developing an Access 2010 application with SQL Server 2008 and apart from the occassional Beta Bug it's quite nice.

    SQL Server is one of those MS technologies that tries to do way too much resulting in a less than easy user experience. Finding the Server name is just one example of the frustrations new users of SQL Server will come across. I'ts one of those technologies where experience really matters.I guess this keeps DBAs in work.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •