Is there a secret in regard to file access (Excel-Security-Options)?
My file access fails.

SQLite3Open returned 0 SQLite3OpenV2 returned 0 SQLite3Close V2 returned 0 SQLite3Close returned 0 SQLite3Open returned 0 SQLite3OpenV2 returned 0 SQLite3Close V2 returned 0 SQLite3Close returned 0

Hey, thank you!

I.e. DateValue("1 Jan 2000") doesn't work on machine with Russian locale, for example.

If I try to run the "AllTests" method in the example project, excel crashes with the error:
"Excel has stopped working". Nothing else?

I'm using Excel 2013

Hi, I am trying to run the test but I get Error 53 FIle not found SQLite3_Stdcall.dll" Is there any dependency I am missing? I tried hard coding the dll directory but doesn't seem to help.

I use this wrapper in order to upload SQLite data to Excel. For security reasons, SQLite containers that I manage has password now (It was given by SQLite Browser with Cipher). The wrapper can not open these databases. Can you write an instruction that open encrypted databases, with a given password? Parameters of this procedure or function are the DB Handler, the DB username and password.
Thanks in advance for your help.

Hi,
i'm working with your .bas with excel. After lots of tries i found that you have not insert a stdcall for bind timestamp, because your stdcall for bind date converts date value in double.
There is a way to solve this issue?
Thank you!

I have a Excel VBA that works fine with the SQLITE DLL downloaded together with the SQLiteforExcel package. But if I replace them with the latest ones (3.33) I get error during sqlite3close operation. (Error 5). It seems some locks are not getting released.

Is it not advised to change DLL versions or is there a change in behaviour in newer SQLite?

Sorry for posting something that is not directly related to this module and I am using unsupported DLL obviously, but just wanted to raise this in case..

I have an error in my spanish excel

I just tried running the demo routine, and it crashed Excel four out of four times. I stepped through the code, and it crashes on the operative line of the SQLite3BackupStep routine.

I believe this is a result of the sqlite3_backup_init API declaration, which appears to return a Long data type, which is then passed to the BackupStep routine as the backuphandle (LongPtr). I changed the sqlite3_backup_init API declaration to return a LongPtr instead (as below) and it no longer crashes.

Private Declare PtrSafe Function sqlite3_backup_init Lib "SQLite3" (ByVal hDbDest As LongPtr, ByVal zDestName As LongPtr, ByVal hDbSource As LongPtr, ByVal zSourceName As LongPtr) As LongPtr

Does this support accessing sqlite3 databases encrypted with the SQLite Encryption Extension (SEE)? (Because SEE adds more C functions to the interface, I'm guessing "no" or at least "not yet".)

(This could also be done using SQLCipher, I guess.)

So how can I use this nice macro? the excel sheet seems to be blank. I'm not sure what this point means: "Examine and run the example test code in the SQLite3Demo module." what should I expect after I "run" the Sqlite3 VBA? I chose AllTests and it seems to execute silently but so what?
Could you please add a very simple example to use this macro to connect to a SQLite database with one table? All I want is to have a drop down list in excel that pulls values from the SQLite table. The XLSQLite.xlam example at http://www.gatekeeperforexcel.com/other-freebies.html seems useful but it only works for 32 bits and I have 64 bits. I dont want the complex GUI part. Just simple.
Thank you

Hi,

Many thanks for this work.
However, is it possible to use dot-commands in vba script code? i.e: .import file.csv

Many thanks

Hi. I want to use the row_number() window function but it is not available on the latest "SQLite for Excel" bundle from 2016 (SQLite 3.11?). Window functions first appeared on SQLite 3.25, now we are at 3.32.

I found that sqlite.com provide an extension file for adding extra function on sqlite, such as SQRT(). This function is really important for my daily operation. But I dont know how to add the extension to SQLiteForExcel. Is it possible to add those extension to SQLiteForExcel?

Thank you very much for contributing this amazing package. I am so happy to convert my old dbf files to sqlite and operating them by excel.

Would this still work on the latest vba engine? I know there were some changes to how 64 bit vba needed to use pointers and call windows api functions...

From an email suggestion:

I have noted that you have commented the following functions as untested:
sqlite3_stdcall_column_int64 and
sqlite3_stdcall_bind_int64

I have successfully managed to use your project to read and write full sized signed 64 bit integers using the little known decimal data type:
https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/data-types/decimal-data-type

I have hence created the following functions:

Public Function SQLite3ColumnInt64(ByVal stmtHandle As Long, ByVal ZeroBasedColIndex As Long) As Variant
SQLite3ColumnInt64 = CDec(sqlite3_stdcall_column_int64(stmtHandle, ZeroBasedColIndex)) * 10000
End Function

and

Public Function SQLite3BindInt64(ByVal stmtHandle As Long, ByVal OneBasedParamIndex As Long, ByVal Value As Variant) As Long
SQLite3BindInt64 = sqlite3_stdcall_bind_int64(stmtHandle, OneBasedParamIndex, CCur(Value * 0.0001))
End Function

In the first function, we read in the 64 bits into a currency data type as per your sqlite3_stdcall_column_int64. As I am sure you are aware this is a 2's compliment signed 64 bit integer with an annoying decimal place four numbers in.
To convert this to a decimal we use CDec and then multiply by 1000. You will note that the return type is a variant. This is because you cannot declare a decimal in VBA directly.
I.e. you cannot say Dim x as Decimal. Instead you have to say Dim x as Variant: x= CDec(0)

The second function reverses the process by taking in a variant (be this a Byte, Single, Double, Integer, Long, LongLong, Currency or Decimal), dividing by 10000 and then converting to a currency value before calling your sqlite3_stdcall_bind_int64.

We can easily convert an unsigned decimal to a signed decimal.

Private Function unsignedDec(signedDec As Variant) As Variant
Dim twoTo64 As Variant: twoTo64 = CDec(2 ^ 32) * CDec(2 ^ 32)
If signedDec < 0 Then
unsignedDec = signedDec + twoTo64
Else
unsignedDec = signedDec
End If
End Function

Sub testDec()

Dim x As Variant: x = CDec(-1)
Debug.Print unsignedDec(x)

End Sub

My company recently updated to office 365 and all my tools got vastly slower using this. Specifically running the DLL api functions to step thru the cursor, read the datatype and convert is many times slower. It's slightly less noticeable when data is only numeric, but I believe it's still slower. I believe this problem is related to the text encoding.

IOW, sqlite db works just as fast, but when you return a lot of data, it takes a lot longer to read it in to an array stepping thru the cursor (so to speak, using the step function). I'm not sure what is going on. I haven't found a fix yet, but it's sad because you can write such amazing solutions using this .. I read all excel tables in automatically to a database in memory as they're added. You'd never even know you basically have an excel db in RAM and I wrote my own sql editor with VBA forms.