Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Front End (Access 2003)

    This may be the wrong forum for this question, but will take a shot anyway in hopes of getting lucky.
    I recently set up SQL 2000 on my home Pc to get smart about using SQL as a back end for Access dBs. During my trials I had no problems with linking a SQL table to an Access front end and running updates inserts etc. . However when I tried the same process at work in a net work situation the linked table linked only as "read only". I have mega permissions on the SQL Server. The only difference is the authentication method. At work it is SQL based and at home Windows Log in method. Does this seem amiliar to any one? If this is the wrong place for this question, a direction to go for for advice would be very helpful.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access Front End (Access 2003)

    A couple of possibilities come to mind immediately. At home you are using the standard SQL server security where you are assigned a userid and password that is unique to SQL Server. In your network environment, you are almost certainly using what is called Integrated security where any connection to SQL Server is managed by the NT domain login, and that login must be explicitly defined for the database you are working in. If not, you are probably going in as Guest who would have very limited priviledges at best. A second issue is the kind of connection you are using. If it is an ODBC connection, the login you use is specified in the ODBC data source created with the ODBC Data Source Administrator. If on the other hand you are using ADO to connect, you must specify the details about the connection each time you connect to a database. Finally, a question: Are you using an MDB file or an ADP file to try to work with SQL Server. If the file is an MDB, and you are working with ODBC connections, the SQL Server table (or view) must have an index or it will not be updatable.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Front End (Access 2003)

    First , thanks for your response. Actually just the opposite. I am using Windows login authentication at home and SQL authentication at work. I will visit the index solution you mentioned. Since my original post, I used the upsize wizard in Access 2003 with a stand alone dB, but it still gave me un-updateable tables(record sets) in the access front end. My gut tells me that the authentication is the key to solving this problem but I have a stubborn team leader.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access Front End (Access 2003)

    Are you using an ADP or an MDB? If it's an MDB, when you connect to a table without a primary key, Access will warn you and suggest you specify a unique identifier. If you aren't getting that message, then it isn't an indexing issue. If you are playing with ADPs, it must surely have to do with the authentication that goes on while you are making a connection. You might also benefit from knowing that Microsoft is no longer pushing ADPs as a preferred solution - mostly because a large majority of users continue to work with ODBC connected tables in an MDB. The ADP version will continue to be supported, so in cases where that is the right choice there isn't a concern, but my instincts tell me that there won't be lots of enhancements to the ADP model.
    Wendell

  5. #5
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Front End (Access 2003)

    Thanks inserting indexes as you suggested solved the problem. You help is invaluable as always. Many thanks.

Posting Permissions

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