[code]// -------------------------------------------------------------
// – I can create a local instance of SQLite and it works
// – however it’s only accessible from that one method
// -------------------------------------------------------------
Dim varInMemorySQLiteDB as New SQLitedatabase
If not varInMemorySQLiteDB.connect Then
MsgBox(“Error: cannot create in memory db”)
Return
Else
varInMemorySQLiteDB.commit
EndIf
// ----------------------------------------------------------------------
// – I have read various blogs and posts that in order
// – to have this accessable globally, create this in a Module or Class
// – I’ve tried various combinations and dot notations but to no avail
// – This is my latest attempt
// – can someone please guide me? Thanks, Ben
// ----------------------------------------------------------------------
// – In the IDE:
// ---------------------------------
globalModuleInMemorySQLiteDB
Properties
InMemorySQLiteDB [Type: SQLiteDatabase]
[Scope: Global]
// --------------------------
// – In the App.open event
// --------------------------
Dim InMemorySQLiteDB As New SQLiteDatabase
If not InMemorySQLiteDB.Connect Then
MsgBox("Error: cannot create in memory db")
Return
Else
InMemorySQLiteDB.Commit
End If
// -----------------------------------------
// – In a method on a different window
// -----------------------------------------
MethodInADifferentWindow_UseInMemoryGlobalModule
Dim sql as String
sql = "Select column from Table;"
If not InMemorySQLiteDB.Connect Then <-- nil object exception
MsgBox("Could not connect to inMemorySQLite db")
Return
Else
InMemorySQLiteDB.SQLSelect(sql)
End If
[/code]
Hi Benjamin,
Apart from the Module, Class or a simple property hanging from the App object
where is the SQLite instance pointing to the SQLite database file on disk? Probably that is what is failing to you? ( I mean, assigning the FolderItem before Connect)
Javier
When you dim
a variable it’s local to the method it’s in. So when you dim it in App.Open, you’re only able to use it in App.Open. You have to store it as a property on the module, and the connect to that module property rather than the local variable every time.
modDB as Module
DB as SQLiteDatabase
App.Open
modDB.DB = new SQLiteDatabase
dim bConected as Boolean = modDB.Connect
// Handle errors if needed
MethodInADifferentWindow
dim rs as RecordSet = modDB.DB.SQLExecute("SELECT * FROM Users")
Thanks Tim!
I was missing this line:
App.Open
modDB.DB = new SQLiteDatabase
Just for clarity, what you had done was redefined a local to the method variable named InMemorySQLiteDB
because you used dim
. In the App.Open
event you had access to two variables named InMemorySQLiteDB that were SQLite Databases.
The local one was instantiated, the global module one was not. This is why you were able to connect in App.Open
, but the other method was giving you a nil object exception. For even more confusing fun, you’d still be able to access the global module one as well if you used the full namespace (globalModuleInMemorySQLiteDB.InMemorySQLiteDB
).
[quote=422546:@Javier Menéndez]Hi Benjamin,
Apart from the Module, Class or a simple property hanging from the App object
where is the SQLite instance pointing to the SQLite database file on disk? Probably that is what is failing to you? ( I mean, assigning the FolderItem before Connect)
Javier[/quote]
That is not actually needed as the title mention, it is an InMemory Database and not a Disk file database .