SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict djh's Avatar
    Join Date
    Apr 2000
    Location
    Long Beach, CA
    Posts
    333
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL server and Access 2000

    Hello -

    I'm using an Access 2000 adp file to connect to a sql server. I was wondering if anyone knows (if it's possible) how I can restrict the tables that are shown from that adp file. Ideally, I would like it so that only one table is shown by default.

    My dilemma is this: we have a table that's updated frequently, and we would like those changes to be real-time on our site. But since our SQL server houses so much other important information, I don't want a person who's updating the stuff to by chance mess around with stuff they shouldn't be.

    Anyone have any ideas?

  2. #2
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Create adp file and build a view in it to access only those table(s) neccassary.
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com

  3. #3
    SitePoint Addict djh's Avatar
    Join Date
    Apr 2000
    Location
    Long Beach, CA
    Posts
    333
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Westmich -

    that's what I'm trying to do... how do you restrict the views? I can't find anything...

    thanks...

  4. #4
    SitePoint Zealot
    Join Date
    Mar 2001
    Location
    Los Angeles
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Create a SQLServer user and asign it permissions to the tables or views you want it to be able to see. Then log in with that user when you access SQLServer from MS Access.
    bsacks
    http://www.middlepeak.com

  5. #5
    SitePoint Addict djh's Avatar
    Join Date
    Apr 2000
    Location
    Long Beach, CA
    Posts
    333
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hey bsacks -

    thought of that already...

    our SQL server is with a web host, and we already have one user login. i think I've read somewhere that you can't have more than one login per database, no?

    i asked the host and they said no, that's not possible. Are they just simply wrong?

    -dave

  6. #6
    SitePoint Zealot
    Join Date
    Mar 2001
    Location
    Los Angeles
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah they are very wrong. But, they may only allow one login per customer. Anyway, I don't know access that well, but You could try creating a new database with linked tables through odbc. Then if you can't limit user permissions in access to that database. You could only link the tables you want people to be able to update.

  7. #7
    SitePoint Addict djh's Avatar
    Join Date
    Apr 2000
    Location
    Long Beach, CA
    Posts
    333
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    bsacks, you're the bomb. i did the linked tables, and it worked great!

    you must be right about the SQL server login... do you see any advantages of doing it that way?

    the access linked tables works fine for me, but just thought i'd ask anyway...

    thanks for your help.

  8. #8
    SitePoint Zealot
    Join Date
    Mar 2001
    Location
    Los Angeles
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The advantages would be better security and more administrative control. But, for a hosted solution your setup is pretty standard. I wouldn't worry as long as you keep control over who gets access. And keep that password safe. Good luck.
    bsacks
    http://www.middlepeak.com


Bookmarks

Posting Permissions

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