Re: need help w/ODBC and temp tables using VB/informix

hells@aol.com (Hells) wrote: >I am developing an app using VB and intersolves ODBC driver to connect to >an informix 5 database. When I create temp tables I am unable to join on >them. Anybody have any ideas or workarounds, so far VB and Informix have >not been able to help. The connection is opened with a VB data control. >Then using datcontrol.database.sqlexec(sqlstatement) the temp tables are >created and values are inserted. When I try to change the recordset to >join on the temp tables using the same data control, I get error 378, from >VB, unable to find table or query, followed by hte name of the temp table. >Please post answers here, or email me .... hells@aol.com >thanks >Rich I tried this as well in VB some months ago - it seems that the VB 3.0 data controls close the current session after each command (or at least temp table command). This was really weird to me since I could create temp tables in VBA for Excel using the XLODBC addin and then join to them, etc.. I got around the problem by using a tool called SQL OLE! from Intelligent Objects and not using the VB data control object. The SQL OLE! tool allowed me to send temp table creation commands and then perform joins on them as necessary. Another option would be to sink all the temp table processing in a stored procedure and then execute the procedure as the source of the data control (I'm pretty sure that will work). However, after my experiences with trying to get VB to interface with Informix through ODBC my decision was to dump VB and use Delphi and Powerbuilder instead (both of which are more expensive but both have native links at the 5.01 level to INFORMIX). How this situation will change with VB 4.0 is unkown though. I believe VB 4.0 will have a different method for accessing SQL databases - you might want to pester Microsoft on this.