Backup API does not work on 64-bit Excel

[SoerenBusse]

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

[govert]

Are you perhaps using the 64-bit version of Excel?

[SoerenBusse]

Yop

[govert]

Are you running the SQLiteForExcel_64.xlsm? Did you copy the x64 directory and that version of SQLite3.dll too? On which line does it crash?

[SoerenBusse]

I've downloaded the whole repository. Then I started the *_64.xlsm file. There's no line. Whole excel is crashing.

[govert]

I think it might be a problem with the backup API. You can comment out the line calling TestBackup inside Sub AllTests(). I'll have a look - the declaration of the backup API was added later, and might not be right for 64-bit.

[R-J-L]

Confirming 64 bit Excel crash on running AllTests. Yes indeed, you can comment out calling TestBackup and have the other tests run. Takes > minute to run the other tests. Trouble traced to SQLite3.dll, sqlite3_backup_step function.

Module Sqlite3Demo.bas
    Sub AllTests
        SQLite3Initialize 'in Module Sqlite.bas
            LoadLibrary ' SQLite3.dll
        SQLite3BackupStep 'in Module Sqlite3.bas, the line that crashes Excel
            sqlite3_backup_step 'in Module Sqlite.bas, calls the function in the dll
                contained in SQLite3.dll

[govert]

I wonder what's wrong though - the declaration looks fine to me, comparing with the documentation here: https://www.sqlite.org/c3ref/backup_finish.html

[R-J-L]

I have zero background in C, or any languages using pointers. (and its been decades since I could PEEK or POKE ...) I have a medium level background in VBA programming. My background in SQL is only as an end user, not as a DBA or DB programmer. So, not sure I can be of much help here?

myBackupHandle = SQLite3BackupInit(myDbBackupHandle, "main", myDbHandle, "main")
In my case returns a large negative number for myBackUpHandle? (Dim myDbHandle As LongPtr) Next statement follows:

    If myBackupHandle <> 0 Then
        RetVal = SQLite3BackupStep(myBackupHandle, -1)
        Debug.Print "SQLite3BackupStep returned " & RetVal
        RetVal = SQLite3BackupFinish(myBackupHandle)
        Debug.Print "SQLite3BackupFinish returned " & RetVal
    End If

Since myBackUpHandle is a negative number the statement above runs. The call in the above statement:
SQLite3BackupStep() leads to the function that actually bombs.

So, I'm checking with you, can myBackupHandle be negative, and is -1 number of pages O.K.? any issues with VBA itself (LongPtr resolving to LongLong in 64-bit versions of Office?) http://stackoverflow.com/questions/24095500/making-long-variables-work-in-64-bit-and-32-bit-excel-vba#24098334

Another novice question - can we use the Object Browser (perhaps by some type of reference) to browse around the contents of the DLL?

[arildj78]

Did you ever figure out what was going on here? This line in TestBackup() causes the crash: RetVal = SQLite3BackupStep(myBackupHandle, -1)

I'm running SQLiteForExcel_64.xlsm on Excel 2016 64-bit

[govert]

@arildj78 I've not taken a closer look yet. I'd be interested if you find anything.

[arildj78]

Ok. I'm not sure if I need this function yet. I've never used SQLite and just barely SQL. I'm now trying to use Excel as front-end for a DB project. I'll update you if I stumble over the solution.

[jikosan]

Sqlite3_64.bas or SQLiteForExcel_64.xlsm module "Sqlite3" sqlite3_backup_init()As Long → As LongPtr

before 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 Long After 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

good job!

[govert]

@jikosan Cool - thanks for spotting that!