SQLite3 ODBC Driver with VB6 errors

Question

I am using an SQLite ODBC Driver (from here http://www.ch-werner.de/sqliteodbc/) with my VB6 application. Everything works fine, as far as retrieving and saving data, however, if I try to get the list of table columns via the following command:

pragma table_info (myTableName)

The ADO call fails with 2 errors (found in Connection.Errors property)

Error #1: Description : "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." : String : cMisDataLayer.ExecuteRecordset

Error #2: Description : "Provider does not support the property." : String : cMisDataLayer.ExecuteRecordset

My question is why am I getting this error and is there a workaround to getting the column list for a table in SQLite?

Thanks.

Answer 1

While I have not worked with SQLite ODBC, I guess you can do a ADOX library - which I guess is used to get the schema information from the database.

This page might help.

Answer 2

Why not just use the ADO RecordSet Fields collection? Just do a SELECT * FROM table WHERE 1=0. When ADO gets a resultset it returns all the column information as well.

Private Sub cmdTest_Click()

   Dim conTest As ADODB.Connection
   Dim cmdTest As ADODB.Command
   Dim rstResults As ADODB.Recordset
   Dim fldCurrent As ADODB.Field

   Set conTest = New ADODB.Connection
   conTest.ConnectionString = "whatever your connection string is"
   conTest.Open

   Set cmdTest = New ADODB.Command
   cmdTest.CommandType = adCmdText
   cmdTest.CommandTimeout = 30
   cmdTest.CommandText = "SELECT * FROM myTableName WHERE 1=0"

   cmdTest.ActiveConnection = conTest
   Set rstResults = cmdTest.Execute()

   For Each fldCurrent In rstResults.Fields

      Debug.Print fldCurrent.Name & " " & CStr(fldCurrent.Type)

   Next fldCurrent

End Sub