I am trying to create a temporary table or recordset in Visual Basic to massage data in and then use as the source for a DataReport object. I have tried several ways to execute the Create Table command that all fail with "The Open method of the (object) failed" or "The Execute method of the (object) failed". If I execute a 'Select * from Customers' command, it works.
There is supposed to be a way to create an empty recordset object and add fields to it, but I can't get that to work, either.
Here is some code.

Dim cmd As ADODB.Command
Set cmd = New Command
Dim cnx As ADODB.Connection
Set cnx = New ADODB.Connection
strcnx = "Provider = PervasiveOLEDB;Data Source=StarTracer;Password=<pwd>;User ID=Master;Persist Security Info=True"
cnx.CursorLocation = adUseClient
'use the open method of the connection object
cnx.Open strcnx

cnx.Execute "select * from customers", , adExecuteNoRecords
'this fails on Create Table but succeeds on Select *

'ok, now try the execute method of the command object
cmd.ActiveConnection = cnx
cmd.CommandType = adCmdText
cmd.CommandText = "create table #temp1 (fld1 integer)"
cmd.Execute
'Set rs = cmd.Execute()

What's your Create Table statement look like? Does the Create Table statement work in the PCC?

Thank you,
I've been using
create table #temp1 (fld1 integer)

as a simple test. If I go to 'tasks' in the PCC for the database and pick Execute SQL Query, the statement works and the table is created. If I try the same statement in the example in my first post, it fails. Similarly, if I create a stored procedure using 'select * from customers' it works, but if is substitute the create table statement, it says 'error checking sql statement' and 'select syntax error'

When I change my connect string to
"DSN=STKBB;Persist Security Info=TRUE"
the create table command works.

This makes two things that didn't work with my Pervasive btreive database until I stopped using my PervasiveOLEDB (the other was CONCAT and LEFT in a select statement). I'd like to use the same connect string everywhere.

THe problem you're running into is the fact that "#" is a reserved keyword. You really shouldn't use it in a table/field name. If you do use it, surround the field/table name with double quotes. For example:
sSQL = "create table " & chr(34) & "#temp" & chr(34) & " (fld1 integer)"