Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2006
    Posts
    3

    Unanswered: MS Access front-end to SQL back-end db

    Hi, I'm pretty new to all this and need some serious guidance. I'm in the
    process of converting a Microsoft Access database to work with a SQL
    database. The SQL database has already been created and I can create new
    and modify records through the Access front-end but when I go to delete and
    rename I get an error 'it didn't add 1 records to the table due to key
    violations'. This, I'm assuming, is because I need to do cascade updates??
    I don't know how to go about this. I didn't design either the Access
    database nor the SQL database. It's quite a small database with only 26
    tables, 8 of these are the only ones used per transaction and the rest are
    never edited through the Access front-end. I was to just change the queries
    and forms to point to the linked SQL tables instead of the previous Access
    tables. At the moment the code is deleting / renaming records from each
    table in separate consecutive statements. Is there any code I can use that
    can do this all at once and avoid the key violation errors or what would be
    the best way to accomplish this? Any help would be greatly appreciated.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You need to find out what kind of key restraints are placed on the backend. It could be as simple as lack of primary keys. ADO in particular gets pissy if you try to alter a record in a table that does not have a primary key defined. The other thing to check is foreign key restraints between tables. You could be breaking the relational structure of the database inadvertantly.

    Also, have a look at access data projects (.adp), they make life easy when dealing with MSSQL through Access
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Teddy comes from the Disconnected World.

    In addition to his comments, you will find that Access won't alter data in linked tables without a unique constraint (doesn't actually need to be a primary key) either defined within SQL Server or when you lnked the tables in Access.

    I am not quite sure what you mean by delete and rename - are you describing a single process? Also, if by rename you mean altering a field's value then you may be changing the value of a unique constraint to something that already exists, violating the constraint\ key.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2006
    Posts
    3
    Thanks so much for your replies. I basically reversed the order the table records were inserted to delete and it seems to be deleting ok. The rename is a different story. I'm renaming the transaction ID which is renaming the main key in all 8 tables. I'm using UPDATE and setting the ID in each table to the new ID but is obviously not working no matter what order I'm doing it in.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by psychick
    Thanks so much for your replies. I basically reversed the order the table records were inserted to delete and it seems to be deleting ok. The rename is a different story. I'm renaming the transaction ID which is renaming the main key in all 8 tables. I'm using UPDATE and setting the ID in each table to the new ID but is obviously not working no matter what order I'm doing it in.
    Is the ID an identity column?
    Is it a primary key or have another unique constraint (either solely on it or as a member of a composite set of columns)?
    Are you getting an error message and if so - what?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also...
    Are you able to effect these changs via Query Analyser?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2006
    Posts
    3
    To be honest, I don't know how to access that information. I am getting the same key violation error. Should this be a case of renaming in right order or is it more complex than this?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi psychick

    Is there a dba in your organisation you can ask or the person that set up the SQL Server db?

    If not you can open up the linked table in Design view (don't worry about the warning).
    Is there a field(s) with a small key next to it\ them? If so - what field(s)? And what are the data types of the field(s)? In particular we are looking for an Autonumber (aka Identity in SQL Server).

    Using Query Analyser is an attempt to figure out if Access is the source of the problem or merely the messenger.

    Please also let us know what Access tells you - does it report an error? Does it carry on as if everything is fine but the data does not change?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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