Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    May 2003
    Location
    Dudley, West Midlands, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access project connect to sql server database (Access 2003)

    My 2003 access project is linked to a sql server 2000 database. I can create tables using both Windows authentication and via a sql logon with db_owner, but I cannot then add records or edit data via table datasheets. Can anyone shed some light on why, please?

  2. #2
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access project connect to sql server database (Access 2003)

    What are you using to access your data? ODBC's? ADO(.NET)? Which tables are you unable to add or edit? The one's using Windows Auth or SQL Auth? I'm not familiar using a project with sql, but Access, when accessing data via code, will try and authenticate as the logged-on user. Access will use the user associated with an ODBC when using linked tables via an ODBC.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  3. #3
    New Lounger
    Join Date
    May 2003
    Location
    Dudley, West Midlands, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access project connect to sql server database (Access 2003)

    gdrezek, I'm using the "Data Link Properties" dialog you get off the File/Connection menu in the Access database window, to connect to the SQL server d/b (this is the native OLE DB provider connection, as installed with Access 2003). I have the same difficulty trying to edit data in a table datasheet for both windows integrated and sql-specific logons, both of which have db_owner in the SQL server d/b. As I mentioned, I can create a new table via Access (and I can see this in SQL server Enterprise Manager), but cannot then edit rows in that table via the table datasheet.

  4. #4
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access project connect to sql server database (Access 2003)

    Until I can get home to Acc03 and try to duplicate this, please bear with a couple more questions and fair warning, some are fairly simplistic:
    Are you getting any error message(s); either as a popup or as a Status Bar message?
    What happens if you create the table using sqlserver then link to it?....and ensuring the following 2 question's answers are yes?
    Are you using a primary key?.....a timestamp field?

    I'll be able to try this in a few hours or so.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  5. #5
    New Lounger
    Join Date
    May 2003
    Location
    Dudley, West Midlands, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access project connect to sql server database (Access 2003)

    gdrezek, I've discovered that edit/change/delete is available in tables with at least 1 primary index, and not in tables without an index. You see, I'd created a new database in Enterprise Manager, imported two Excel spreadsheets (the tables were created without an index). Then I started creating other tables, from Enterprise Manager and Access, changing the connection logons, etc. I eventually noticed what was happening, because Access prompts to create a primary index and Enterprise Manager doesn't. Either Access or the OLEDB SQL Server connection driver must retrieve a recordset as a snapshot (non-updatable cursor), when the source has no index.

    Thanks for looking at this with me. I hope you've not wasted too much time!

  6. #6
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access project connect to sql server database (Access 2003)

    Glad it's solved. I also use a [timestamp] field with each table, in addition to a Primary Key, to help Access keep track of which records can be modified.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

Posting Permissions

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