CODEWITHSUNDEEP
vb.netvisual-studio-2010ms-access
Paul Williams
Paul Williams

Reputation: 1598

Creating temporary tables in MSAccess DB

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?

Upvotes: 0

Views: 6281

Answers (2)

Philippe Grondier
Philippe Grondier

Reputation: 11138

If your idea is:

  1. to create an empty table inheriting its structure from another table, then
  2. add records to this new table, and
  3. 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.

Upvotes: 1

ChrisPadgham
ChrisPadgham

Reputation: 870

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

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

Upvotes: 0

Related Questions