7 Replies Latest reply on May 25, 2006 1:05 PM by Dan Bracuk

    Inserting data into an Access table

    Frec
      I'm working on a Cfinteface where a user can push a button and process an Access table. I'm running into issues where the data is being inserted (INSERT statement) into the Access table, however the data isn't inserted into the last/new row in the table. It ends up between some existing rows in a database.

      I run a delete statement on some data in the table before I run the insert and new row is always in the row where the data was deleted.

      Is there anyway I can make the INSERT go to the last row in the database?
        • 1. Re: Inserting data into an Access table
          Dan Bracuk Level 5
          How do you know what row into which it is going?
          Why do you even care?
          • 2. Re: Inserting data into an Access table
            jdeline Level 1
            The concept of first row and last row in a database table doesn't work. It is only important when you SELECT data and ORDER BY someField.
            • 3. Re: Inserting data into an Access table
              paross1 Level 2
              Sounds like Frec is thinking of a database as just kind of a big old spreadsheet. Probably missing the entire point of relational database. It doesn't matter where the data is physically in the database because YOU control that on the output when you query for the data, etc. As Dan says, why do you even care where it goes in the table? If you want it to be the "virtual" last record, then make sure that the reecord is assigned the highest primary key value, etc. then you can query for the "last" record and even sort the records in the output last to first, first to last, etc.

              Phil
              • 4. Re: Inserting data into an Access table
                Frec Level 1
                Let me break this down.

                This is an Access database. The table is manipulated (via my ColdFusion app) and the data is exported via an Macro action. The export specification generates a text file of the table.

                When I look at the table datasheet view I want it to be a snapshot of how the file will look (data rows,etc).

                Does this help?
                • 5. Re: Inserting data into an Access table
                  Frec Level 1
                  Thanks, I'm trying to use it as temp spreadsheet. There is a autonumber column and I can verify it's assigning the next number. When I open the table everything is placed as the code parsed however this one line is sitting in the middle of my (access table) or wanna be spreadsheet. Ha.
                  • 6. Inserting data into an Access table
                    paross1 Level 2
                    Yes, but wasn't the record assigned the next highest value in the autonumber field, so just ORDER BY that field in your query, and you will have your sorted list. Even if you are viewing this in Access via REPORTS, just put an order by statement in the query that drives your report (or form, etc.) (Wanting this to look that way in the datasheet view is kind of silly, when there are very simple ways to see it they desired way without having to do what you would need to do to actually reorder the data physically in a table.)

                    Phil
                    • 7. Re: Inserting data into an Access table
                      Dan Bracuk Level 5
                      quote:

                      Originally posted by: Frec
                      Thanks, I'm trying to use it as temp spreadsheet. There is a autonumber column and I can verify it's assigning the next number. When I open the table everything is placed as the code parsed however this one line is sitting in the middle of my (access table) or wanna be spreadsheet. Ha.

                      Build a select query in access and use that for your temp spreadsheet.