7 Replies Latest reply on May 9, 2006 1:41 PM by paross1

    Update an Access table

    Frec
      I'm working on a Cf inteface 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: Update an Access table
          jdeline Level 1
          The "last row of the database" is kind of subjective. Do you have an autonumber field? If you do, sort on it and your new row will appear as the last row.
          • 2. Update an Access table
            Frec Level 1
            Thanks. I do have an autonumber field.

            Would I add the sort (ORDER BY)? On my insert statement?
            • 3. Update an Access table
              paross1 Level 2
              What jdeline is trying to tell you is that first/last is meaningless in terms of data in a database table, since it isn't "stored" in any particular order. This doesn't work like a spreadsheet. Is this just an issue as to what it looks like when selected in a query, because you would control that with an ORDER BY clause where you would specify which field(s) to sort on and whether you wanted descending or assencding.

              In other words, why does it matter to you where this row is inserted into the table, especially if you are using an auto-incremented primary key?

              Phil
              • 4. Re: Update an Access table
                Frec Level 1
                >>>In other words, why does it matter to you where this row is inserted into the table, especially if you are using an auto->>>incremented primary key?

                This particular table is exported to a text file via a printer specification. So I need this table to look a certain way before it's exported to a text file.

                I'm doing some manipulation to the table data before the export and this insert is one of the queries.

                Is this clearer? Is there another recommendation. Should I export this to a spreadsheet first?
                • 5. Update an Access table
                  paross1 Level 2
                  If you doing your export from Access itself, why don't you just export a query of the table where you select all columns and order by your key, instead of exporting the table itself? Otherwise, select your data with an ORDER BY clause before you print/export it. Perhaps if you included how you were trying to do this we would have a better idea for making possible recommendations.

                  Phil
                  • 6. Re: Update an Access table
                    Frec Level 1
                    The below is what I'm generally doing:

                    I have an Access table (table A) with three column: field0, field1, field2

                    I use various queries to manipulate the data in this table (i.e. add custom line numbers, replace, sum specific fields, etc) Some of these things I can't figure out how to do in Access.

                    That's why I use ColdFusion. After my program runs it's ready to export to a text file.

                    Would you know a better way to take the Access file and export it to Excel a ColdFusion array?
                    • 7. Update an Access table
                      paross1 Level 2
                      In ColdFusion you might try selecting your data with a query where you order by the appropriate fields, then display the data in a table using a CFCONTENT tag with TYPE="application/msexcel" to get it into a spreadsheet format for you to save. Since you aren't very clear where you are trying to "export" from (ColdFusion or Access), I'm not sure what to recommend specifically. Show what you are doing so that we can see if it makes sense, or gives us an idea where you might be having trouble.

                      Phil