[sqlite] INSERT INTO with SELECT

[RB Smissaert]

Trying to move data from Interbase to SQLite via the ODBC driver and ADO and
having trouble to get the right syntax for the INSERT INTO statement.

This is what I have now, but it fails with the error: only one SQL statement
allowed.


Sub InsertIntoSQLLite()

   Dim cn As SQLiteDb.Connection
   Dim objCommand As ADODB.Command
   Dim ADOConn2 As ADODB.Connection
   Dim strConn2 As String
   Dim strSQL As String

   Set ADOConn = New ADODB.Connection

   strConn2 = "ODBC;" & _
              "DB=localhost:C:\Torex\Synergy\Meddata\S6000\db\s6.gdb;" & _
              "DSN=System 6000;" & _
              "UID=un;" & _
              "PWD=pw;"

   'All this is only to create the .db file
   'must be a better way avoiding this non-ODBC connection
   '------------------------------------------------------
   Set cn = New SQLiteDb.Connection
   cn.ConnectionString = "Data
Source=C:\RBSSynergyReporting\ReadCodes\ReadCode.db"
   cn.Open
   cn.Close
   Set cn = Nothing

   SetADOConn

  strSQL = "CREATE TABLE READCODE (SUBJECT_TYPE, READ_CODE, TERM30, TERM60)"

   Set objCommand = New ADODB.Command

   With objCommand
      .CommandText = strSQL
      .ActiveConnection = ADOConn
      .Execute
   End With

   Set ADOConn2 = New ADODB.Connection
   ADOConn2.Open strConn2

   strSQL = "INSERT INTO " & _
            "READCODE " & _
            "(SUBJECT_TYPE, READ_CODE, TERM30, TERM60) " & _
            "SELECT R.SUBJECT_TYPE, R.READ_CODE, R.TERM30, R.TERM60 " & _
            "FROM READCODE R " & _
            "IN """" [" & strConn2 & "]"

   With objCommand
      .CommandText = strSQL
      .ActiveConnection = ADOConn
      .Execute   '''' >> fails here << '''''''''''''''''''''''
   End With

End Sub


Any suggestions now this should be done?


RBS



-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------