Using temp table and append query?

[NewShoes]

Hey all,

I'd like to copy data from an excel spreadsheet and paste it into a temp table in Access and then hit a button which will run an append query and append all the data in the temp table to a permenant table. Can anyone push me in the right direction on how to create a temp table?

Many thanks!
-NS

 

[CJ_London]

do you mean physically copy and paste?

If so, highlight and copy the data in Excel, then go to Access, click on the create tab and then table, then paste.

Alternatively, click on the external data tab, select Excel and follow the prompts to import the data.

Alternatively, in code, use TransferSpreadsheet to import the data

 

[NewShoes]

Thanks for your reply CJ. Those are all good ways but not quite what I'm after. Basically, every week, I want a user to be able to open up the database, launch a form that have a datasheet view on it (empty tho). They will then copy from Excel and paste into this empty datasheet. They will then click a button which will append the pasted data to a normal table. When the user has done this, the data in the temporary table will then be discarded.

Hope this makes sense (it may actually be a temp table I need, it just seemed to make sense).

Thanks,
-NS

 

[CJ_London]

Your question was about how to create a temp table.

However if you are now talking about using a form look at my second paragraph in my first post and instead of opening a new table, just copy and paste into your form. The only requirement is that your temp table needs to already exist and be bound to the form and the columns are in the same order and type between spreadsheet and form - you set the order of columns in the form.

Please be clear about your requirements. You initially wanted to paste into a temp table, now into a form. You've also added the requirement to delete the table afterwards (which I would modify this to delete the contents of the table afterwards). My point is, explain clearly exactly what you want to do, what you have tried so far and why that didn't work for you, including any error messages etc.

Your question was about how to create a temp table. So either look at my first paragraph above or the second paragraph of my first post. There is nothing special about a temp table, it is just a table called 'temp' or whatever you want to call it.

If you want to move into the realms of disconnected recordsets etc (which doesn't require a table per se, but still requires all fields to be properly defined and a mass of code to then update another table). I suggest you google it, understand the implications and decide which way you want to go.