Acces Temp Table creation

Topic

New Reply

I have been using a recordset to define a chunk of data I want to work with. We have decided that a temp table would be better so we could query against the smaller piece.

I have forgotten the syntax for building the TempTable. This is what I came up with but it isn’t right:

Code:

YvString = "SELECT * FROM YAV_Fc [B][COLOR=darkred]INTO YAVtemp[/COLOR][/B]"
    YvString = YvString & " WHERE ((Country="
    YvString = YvString & Chr(34) & Country & Chr(34)
    YvString = YvString & ") AND (MPG=" & Chr(34) & MPG & Chr(34) & ") AND "
    YvString = YvString & "(BasicM=" & Basic & ") AND "
    YvString = YvString & "(MG=" & Chr(34) & MG & Chr(34) & "))"
    YvString = YvString & " ORDER by CalenPer,Priority,ABCquality;"
    DoCmd.RunSQL YvString

The variables are correct and worked when I was writing to RecordSet. I am using the astericks (*) because there are so many fields to pull.

Reply | Quote

Replies

Try

Code:

 YvString = “SELECT YAV_Fc.* [B][COLOR=darkred]INTO YAVtemp[/COLOR][/B]  FROM YAV_Fc ”

Reply | Quote

Hi MacroAlan,

I have a sample database that is based on code provided by Access MVP Dirk Goldgar, which shows how to easily create a temporary work database using a “template” table, or any SELECT query (including crosstab queries). There’s only one little restriction that involves the VBA editor option needs to be set to Break on Unhandled Errors, as opposed to Break on all Errors. If you distribute your application in the compiled .mde (.accde) form, then you need not worry about how this VBA option is set on a user’s machine. The sample is located here:

http://www.accessmvp.com/TWickerath/downloads/tmpwrkdb.zip

Tom Wickerath
MS Access MVP
2006 – 2011

Reply | Quote