ADO and SQL # Table 
Author Message
 ADO and SQL # Table

Hi

Can you create a temp table (#table1) using ADO and populate the #table1 with a
new query afterwards using the same connection.

Simple example:

Dim results As ADODB.Recordset
Dim query As ADODB.Command
Dim query1 As ADODB.Command

Set query = New ADODB.Command
Set query1 = New ADODB.Command

Set query.ActiveConnection = active_conn
Set query1.ActiveConnection = active_connSet

results = New ADODB.Recordset
query.CommandType = adCmdText
query.CommandText = "create #table(f1 int, f2 varchar(25))"
Set results = query.Execute()

query1.CommandType = adCmdText
query1.CommandText = "insert into #table(f1, f2) values(1,'test')"
Set results = query1.Execute()

Thanks Rupert



Sat, 12 Jul 2003 17:29:17 GMT  
 ADO and SQL # Table
You can, but the create and insert statements aren't going to return a
result, so simply use
    query.execute
instead of
    set result = query.execute()

Your create table statement also has an obvious error, the correct code is
    "create table #table(f1 int, f2 varchar(25))"

To view the result you'll have to add a "Select * from #table" to your code,
so that:

query1.CommandType = adCmdText
query1.CommandText = "Select * from #table"
Set results = query1.Execute()

BTW, you should use use
    results.open
instead of
    set results = query1.execute()

The latter method may cause you some problems because all properties of the
recordset, such as recordcount, may not be available.  At least it's caused
me some problems which were solved by simply using the results.open command.

Besides, there's no need to create more than one command object for what
you're trying to do.  Once you've executed one command on the object, it's
ready for a new command.

This works:
    Set query = New ADODB.Command
    query.ActiveConnection = active_conn
    query.CommandType = adCmdText

    query.CommandText = "create table #table(f1 int, f2 varchar(25))"
    query.Execute

    query.CommandText = "insert into #table(f1, f2) values(1,'test')"
    query.Execute

    Set result = New ADODB.Recordset
    result.Open "select * from #table", active_conn, , , adCmdText


Hi

Can you create a temp table (#table1) using ADO and populate the #table1
with a
new query afterwards using the same connection.

Simple example:

Dim results As ADODB.Recordset
Dim query As ADODB.Command
Dim query1 As ADODB.Command

Set query = New ADODB.Command
Set query1 = New ADODB.Command

Set query.ActiveConnection = active_conn
Set query1.ActiveConnection = active_connSet

results = New ADODB.Recordset
query.CommandType = adCmdText
query.CommandText = "create #table(f1 int, f2 varchar(25))"
Set results = query.Execute()

query1.CommandType = adCmdText
query1.CommandText = "insert into #table(f1, f2) values(1,'test')"
Set results = query1.Execute()

Thanks Rupert



Sat, 12 Jul 2003 18:21:49 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Deleting table ADO or SQL

2. Determine locking on SQL table with ADO

3. ADO with Stored Procedure and Temporary SQL Server Tables

4. temp tables - ADO - MS SQL Server

5. ADO, Oracle8 and PL/SQL table output

6. Inserting data from an ADO recordset into a SQL Server table

7. Link a SQL Server Table with ADO

8. treeview with same table using ado, sql

9. Getting tables name from SQL Server via ADO

10. Link SQL server table to Access using ADO

11. Creating Table with ADO Errors out on SQL statement

12. ADO connection using Foxpro table (DBF) - SQL

 

 
Powered by phpBB® Forum Software