INSERT INTO with recordsets created in memory

[AccessVB]

I need help on this big time!!! I read in a book that we could create recordsets into memory using access/vb. I was able to do this and called the recordset temptradestime. Now i want to add data to this temptradestime using the INSERT INTO SQL command. When i try to do this i get the error- Run time error 3192 "Could not find output table 'temptradestime'"

I know this is because its not an actual table, but is there a way to fix this?
Any sort of help will be highly appreciated.

 

[pmessana]

I have never created recordsets in memory, instead, I usually just use Temp Tables, they would be much easier to handle and debug anyway.

 

[JeremyNYC]

Temp tables are usually a bad idea, as they cause massive bloating of your database, which can lead to corruption.

I don't think it's possible to insert into a recordset. A recordset can contain fields from many tables, and it would be hard to know where the new record would go.

What you want to do is insert directly into one or more of the tables underlying your recordset.

If you post your code here, I'm sure you'll get some more specific advice.

Jeremy =============
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[AccessVB]

TempTradesTime here is the temporary recordset i have created in memory. The code originally used to run the sql statement, insert a line into a temporary table, loop and insert the next line and so on. This takes a long time. What i want to do is insert all line sinto TemptradesTime and then write the whole block at once to the final table as i think this might save me some time.
TradeforContractsSQL = "SELECT * FROM qryTradesforContracts WHERE (" & DateStr1 & DateStr2 & DateStr3 & DateStr4 & &quot"
TradeSQL = "SELECT [Security ID], PortfolioID FROM (" & TradeforContractsSQL & &quot"
TradeSQL2 = &quot; GROUP BY [Security ID],PortfolioID HAVING (((Sum([Bond Original Face]*IIf([Buy],1,-1)))<-0.1 Or (Sum([Bond Original Face]*IIf([Buy],1,-1)))>0.1))&quot;
MySQL = &quot;INSERT INTO TempTradesTime( SecurityID, PortfolioID, [Date] ) SELECT [Security ID], '&quot; & PortfolioID & &quot;' AS PortfolioID, #&quot; & CurrentDate & &quot;# AS [Date] FROM &quot;
TempTradesTime.Open
DoCmd.RunSQL MySQL & &quot;(&quot; & TradeSQL & TradeSQL2 & &quot&quot;
TempTradesTime.Close
Next
MakeTradesTimeTable = IIf(TimePeriod >= 0, True, False)
End Function

 

[vbajock]

I don't see where the object TempTradesTime is orginating.
I don't see how the SQL engine could execute an Insert if the target table did not actually exist in the database. If you want table structure data to exist in memory only you are going to have to use looping code feeding your data into multi-dimensional arrays. That gets a little beyond the scope of a chat room. If there is a better way to do it, I'd love to here about.

 

[scking]

You DON'T use SQL on recordsets but use the method .AddNew. You do use SQL to manage data in tables or queries.

Dim rst As DAO.Recordset
rst.AddNew
rst!Field1 = Anyvariable
rst!Update

-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[vbajock]

That code returns an object variable error.

 

[GummowN]

In reply to JeremyNYC re Temp Tables.

Temp tables can cause bloating but can also be an essential part of a database if you are dealing with large datasets. It is not uncommon for me to produce queries returning >100000 records from multiple ODBC linked SQL tables all containing >5000000 records.

Normally I will perform a number of queries on top of the original and often return that against 1 or a number of the original tables. The fastest and most reliable method is to produce a local table. This method of local storage allowed a bulk request to run in 15 minutes instead of 11 hours!!!

As for the recordset question make sure that you are using an updateable recordset? If not then you will not be able to add or update a record.

 

[JeremyNYC]

GummowN,

Yes, clearly that is a good use of temp tables. But reading the original post, I doubt very much that the data set being used is anything like the ones you discuss.

Also, I certainly hope that when you're using a temp table of over 100,000 records you're doing so in a database built just for holding those temp tables. I would never have a temp table that big in eitehr my front end or my normal back end.

Jeremy =============
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.