• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Update an Access table

New Here ,
May 09, 2006 May 09, 2006

Copy link to clipboard

Copied

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?

TOPICS
Advanced techniques

Views

496

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
May 09, 2006 May 09, 2006

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
May 09, 2006 May 09, 2006

Copy link to clipboard

Copied

Thanks. I do have an autonumber field.

Would I add the sort (ORDER BY)? On my insert statement?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Mentor ,
May 09, 2006 May 09, 2006

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
May 09, 2006 May 09, 2006

Copy link to clipboard

Copied

>>>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?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Mentor ,
May 09, 2006 May 09, 2006

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
May 09, 2006 May 09, 2006

Copy link to clipboard

Copied

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?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Mentor ,
May 09, 2006 May 09, 2006

Copy link to clipboard

Copied

LATEST
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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation