Can you try this code:
Dim sqlite As New SQLiteDb.Connection
With sqlite
.Open "Data Source=c:\temp\test.db"
If .State = slStateOpen Then
.Execute "CREATE TABLE [A3Test8CA_F] ([PATIENT_ID] INTEGER,
[ENTRY_ID] INTEGER, [READ_CODE] TEXT, [TERM_TEXT] TEXT, [START_DATE]
INTEGER, [NUMERIC_VALUE] REAL)", , slExecuteNoRecords
.Close
End If
End With
Set sqlite = Nothing
MsgBox "Done"
I tried it both with and without test.db existing, and it worked fine
both times.
Eric Pankoke
Founder
Point Of Light Software
http://www.polsoftware.com/
-----Original Message-----
From: RB Smissaert [mailto:[EMAIL PROTECTED]
Sent: Saturday, December 09, 2006 6:39 PM
To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
Subject: RE: [sqlite] Operation is not allowed when the object is closed
This is the full Sub that sets the connection, except for leaving out a
msgbox text:
Sub SetSQLiteConnection(Optional bNoRecords As Boolean, _
Optional strDBFile As String, _
Optional lPageSize As Long = -1)
Dim strDBPath As String
If Len(strDBFile) = 0 Then
strDBPath = ReadINIValue(strINIPath, _
"Add-in behaviour", _
"Path to SQLite database")
If bFileExists(strDBPath) = False Then
Exit Sub
End If
Else
strDBPath = strDBFile
End If
If Len(strDBFile) > 0 Then
If Not SQLiteConn Is Nothing Then
If SQLiteConn.State = slStateOpen Then
SQLiteConn.Close
End If
Set SQLiteConn = Nothing
End If
End If
If SQLiteConn Is Nothing Then
Set SQLiteConn = New SQLiteDb.Connection
SQLiteConn.ConnectionString = "Data Source=" & strDBPath
End If
On Error Resume Next
Do While SQLiteConn.State = slStateClosed
SQLiteConn.Open
Loop
If bNoRecords Then
SQLiteConn.Execute "PRAGMA synchronous=off;", , slExecuteNoRecords
SQLiteConn.Execute "PRAGMA encoding='UTF-8';", ,
slExecuteNoRecords
End If
If lPageSize > -1 Then
SQLiteConn.Execute "PRAGMA page_size=" & lPageSize & ";", ,
slExecuteNoRecords
End If
End Sub
And this is the relevant code leading up to the error:
SetSQLiteConnection True, strDBFile
With SQLiteConn
.BeginTrans
If SQLiteTableExists(strDBFile, strTable) = False Then
.Execute strCreateTable, 0, 128 <<< error here <<<
End If
strCreateTable is a valid SQL to create a table:
CREATE TABLE [A3Test8CA_F] ([PATIENT_ID] INTEGER, [ENTRY_ID] INTEGER,
[READ_CODE] TEXT, [TERM_TEXT] TEXT, [START_DATE] INTEGER,
[NUMERIC_VALUE] REAL)
RBS
-----Original Message-----
From: Kees Nuyt [mailto:[EMAIL PROTECTED]
Sent: 09 December 2006 23:27
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Operation is not allowed when the object is closed
On Sat, 9 Dec 2006 22:24:59 -0000, you wrote:
> Keep getting this error when trying to create a table
> in SQLite. There is a valid connection object and it
> is open and I have no idea what is causing this:
>
> Operation is not allowed when the object is closed.
> Error number: 3704
Please show us some code.
Did you check for any errors on the connection statement?
>Thanks for any advice
>
>RBS
>
>
>
>-----------------------------------------------------------------------
----
--
>To unsubscribe, send email to [EMAIL PROTECTED]
>-----------------------------------------------------------------------
----
--
--
( Kees Nuyt
)
c[_]
------------------------------------------------------------------------
----
-
To unsubscribe, send email to [EMAIL PROTECTED]
------------------------------------------------------------------------
----
-
------------------------------------------------------------------------
-----
To unsubscribe, send email to [EMAIL PROTECTED]
------------------------------------------------------------------------
-----
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
