We help IT Professionals succeed at work.

Creating a Temp Table in MS Access

TechNinja2
TechNinja2 asked
on
1,876 Views
Last Modified: 2017-04-06
Amazing Experts!

I'm stuck.  I combined a bunch of tables into a union query and am trying to dump that query back into a table so I can add a checkbox field and make the query "editable" so to speak.  The problem is that the query has 5000+ records, but when I run the code, I only end up with 200+ records in the blank table.  I'm not getting an error message and I back checked that my blank table has the correct amount of fields.  (Note: I can't upload the database since it is client confidential, but I can generalize the content.)

I used this vba/SQL code in the OnLoad event of the form:
CurrentDb.Execute "INSERT INTO TBL_Blank  (Field1, Field2, Field3 ...)
SELECT Field1, Field2, Field3 ... FROM QRY_CombinedRecords;", dbFailOnError

Should I be using DAO.Database (see mwolfe02 response)?
http://stackoverflow.com/questions/6843017/how-to-insert-entire-dao-recordset-into-a-table-with-vba

or recordset (see Gord Thompson response).


I'm not sure why it doesn't just copy the 5,000+ records in the query and pastes them in the blank table I set up.

Any ideas?
Comment
Watch Question

Owner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
John TsioumprisIT Supervisor
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Why don't you make a passthrough query to select the records and use Access Query Designer to create a "Make Table Query" ...if something is wrong it should popup
CERTIFIED EXPERT
Top Expert 2009

Commented:
Instead of creating the temp table from scratch, you could create it (ensuring that all fields are of the correct data type, which is not guaranteed with a make-table query), and fill it from code.  Of course, clear it first with a SQL statement like this:

   strSQL = "DELETE * FROM tblOrderData_Temp"
   CurrentProject.Connection.Execute strSQL

OR

      CurrentDb.Execute strSQL, dbFailOnError

Open in new window

Author

Commented:
Thank you Dale.  I reviewed the structure based on your questions and it got me thinking about the relationships I used.  After changing relationships, it pulled the records I needed.  (It took me some time to figure out so thx for your patience.)

Thx all!
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
glad I could help.