Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    We have an SQL database connected to Access via an ODBC link. What is the best way to achieve the following in Access 2007 or in SQL Server 2005 Management Studio?

    1. Insert a blank record in a table, with a specified key.
    2. Copy a record (or range of records) from one table and insert it between existing records in another table, with a specified key (or range of keys).
    3. Copy a record (or range of records) from one table and paste it over existing records in another table, with a specified key (or range of keys).

  2. #2
    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 think you need to provide more information regarding the actual required process
    and the nature of the data insert

    What is the Specified Key? Is it an Identity field (auto number)
    Are there other Required (NON NULL) fields in the tables

    1 Can be done with an Append Query in Either OR VBA

    2 Might also be done with an Append Query, BUT it depends upon the Keys
    and if it is allowed to insert between existing records.
    If you mean copy and Insert as in Excel then NO
    Addition of Records is at the end of the existing data.
    The viewed record position depends upon sort order

    3 Is going to be an Update Query in Either OR VBA

    I think you need to post an example of what you are hoping to achieve
    Andrew

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply.

    Yes, the primary key would be an identity (autonumber) field.
    Some other fields may also be foreign keys.
    Some fields allow nulls and some do not.

    Example scenarios would be as follows.

    1. A record has been deleted from the table (e.g., key = 2), so it might be desirable to insert a blank record between remaining ones (e.g., between keys = 1 and 3) and then enter data into the fields manually.

    2. As above, but one or more missing records (e.g., keys = 2 - 4) to be copied from another table (e.g., a backup copy) and inserted between remaining ones (e.g., between keys 1 and 5). Because the key sequence is used for relations between tables, missing records need to be inserted with their original key field values rather than appended using the next autonumber key field values.

    3. As above, but one or more records to be copied from another table and pasted over existing ones.

    We would prefer to do this in Access, unless it is easier in SQL Server.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    In general, what you are describing is considered poor form in relational database design. Autonumber keys cannot be inserted into a table in Access - it will fail if you try to run a query that does that. As a general rule, a key should be nothing more than that, and the fact that one is missing isn't an issue - as long as there are no records in other tables that refer to that key value. And that's where referential integrity comes in. If you have it properly designed then it won't let you delete a table if there is another table that refers to that key.

    In order to do what you describe, you would need to make your key a number that you control the value of - so that means having some code that can check and see what the next sequential number is - the most common is a DMax() function to find the current highest key and then add 1 to it. But if you want to fill in missing key values it gets a good deal more complex. You essentially need a table with all of the possible values and then do a join to find the lowest (DMin()) missing value. Very tricky in the real world.

    Finally, older versions of SQL Server would let you enter a missing key, but 2005 and 2008 have removed that capability as far as I can tell. And if you tried to insert an record with an existing key, it will fail as that would create a duplicate key. Bottom line is you don't really want to do that.
    Wendell

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply. This is not a design issue but a maintenance issue. We do not need to insert records as a normal procedure, only if they are accidentally deleted.

  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
    As long as the Identity does NOT already exist in the destination table in SQL 2005 and 2008
    you can insert data back into a table with Identity (that is missing)

    by specifying SET IDENTITY_INSERT .... ON (as in earlier Spurious Locks Thread)

    Code:
    SET IDENTITY_INSERT TableToAppendTo ON
    INSERT INTO TableToAppendTo (IDentityField,TextField1,NumberField2,BitField3)
    VALUES(3,'SomeText',12.05,0)
    You can also Insert From Table A to Table B by Using a SELECT rather than a VALUES SQL Command

    NOTE... YOU MUST HAVE a field list in the Insert Into Statement you cannot use TableName.*

    ADDED 2nd April ... This is the MS SQL Syntax Not Access
    Andrew

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply.

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Quote Originally Posted by Murgatroyd View Post
    Thanks for your reply. This is not a design issue but a maintenance issue. We do not need to insert records as a normal procedure, only if they are accidentally deleted.
    In that case you should probably consider using a logical delete rather than a physical delete. One way to do that is to create a bit (boolean) field that indicates the record has been deleted. Then you create a view in SQL Server that only shows the records that are not deleted. You can ODBC link to a view just as you do a table, and the user can't tell the difference. Then you can create a maintenance form that looks at the table, and lets you do a logical undelete if it is necessary to restore a record. Taking that approach avoids having to do the sort of thing Andrew described (it's good to know that still works in 2005/2008), and it also solves the problem of taking snapshots to restore from. One final thing would be to create a Delete trigger for the table so that a record cannot physically be deleted. We used this approach for years and had excellent success with it.
    Wendell

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply. In Excel, I can use basic copy, paste and insert functions to restore one or more accidentally deleted rows from a worksheet if need be. I was hoping that something similar would be possible with one or more accidentally deleted records from a table in Access or SQL Server Management Studio.

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    The difference between Excel and Access in that regard is that there is no referential integrity capability in Excel - it is not a relational database. If you tell us a bit more about your application (did it replace a series of Excel workbooks?) then we may be able to offer some other alternatives.
    Wendell

  11. #11
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Tables are related by the contents of key fields, so if a record is accidentally deleted, we need to replace it with its original key value, not the next autonumbered value.

    For example, a product table has the product number as a key field. If the table has five records (101, 102, 103, 104, 105) and record 103 is accidentally deleted, then we need to reinsert it with its original key value (103) not the next autonumbered value (106).

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    If you setup referential integrity in your SQL Server database, then it won't allow you do delete a record if there is a related record in another table. It sounds like your SQL Server database needs some constraints setup to prevent those situations from happening.
    Wendell

Posting Permissions

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