Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    8

    Unanswered: Create Table fails in VB code

    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()

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    What's your Create Table statement look like? Does the Create Table statement work in the PCC?
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Sep 2004
    Posts
    8

    Create Table statement

    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'

  4. #4
    Join Date
    Sep 2004
    Posts
    8

    Change provider works

    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.

  5. #5
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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)"
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •