Creating temporary tables in MSAccess DB

Question

I'm almost ascertain there's a better way to do this but for right now I'll go with this. This is using WinForms, NOT on a webpage.

In Visual Basic 2010, I would like to create a form that uses a datagrid view pulling rows from a temporary table made at run time. When the "Submit" button of that form is clicked, the rows from the temporary table will be copied into the regular table. (This is being done as there is no ID# available until AFTER the form is submitted. The ID# is needed because the rows made in the temporary, as well as the entire form, will be associated with THAT number.)

So my question is, how can I dynamically create a temporary table in MSAccess OR how can I use a datagrid without associating it with a table?

Philippe Grondier · Accepted Answer · 2012-03-05 23:23:45Z

1

If your idea is:

to create an empty table inheriting its structure from another table, then
add records to this new table, and
finally insert these records in the original table,

you could do it this way:

SELECT * INTO tempTable FROM myTable WHERE myTable.id_MyTable IS NULL

(you are creating here an empty copy of your original table. The WHERE clause is here to make sure that your new table is empty ...)

you can then manipulate tempTable with your datagrid. At the end of the process, you can write the following:

INSERT INTO myTable SELECT * FROM tempTable
DROP temptable

The code was written 'on the fly', so I cannot garantee it, but the idea is here.

edited Mar 5, 2012 at 23:23

answered Feb 25, 2012 at 18:20

Philippe Grondier

11.1k3 gold badges37 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ChrisPadgham · Accepted Answer · 2012-02-25 04:59:33Z

0

You can execute a Make Table query to create the table.

SELECT "X" AS MyId, 1 AS F1, 1 AS f2 INTO MyTempTable;

answered Feb 25, 2012 at 4:59

ChrisPadgham

8705 silver badges4 bronze badges

Collectives™ on Stack Overflow

Creating temporary tables in MSAccess DB

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related