File Access

[Steviey]

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!

[govert]

I'm not sure what you're asking. All your functions are returning 0, which means OK... The demo routine tries to create a file in a temp directory, maybe that fails?

[Steviey]

Does the connection to Sqlite3.dll depend on any firewall settings? Are there any security - options to switch in Excel? By the way, I modified the path to a location that makes sense and commented out the kill statement. TestFile = ThisWorkbook.Path + "\x64\TestSqlite3ForExcel.db3" I also get run time error 53.

[govert]

No, the Sqlite3.dll is juat a library file that is read into the Excel process. There is not network stuff involved at all. I don't know of any security options that affect these modules. Error 53 means file not found. At which line to you get this? You say your file access fails, but all your calls returned 0, meaning OK. So I don't really know where you are stuck. Good luck...!

[pts925]

Hi Govert, Any reason the exec statement is not included? I have a VBA program that updates many entries based on calculated values within excel, and calling Update many times through prepare, step, finalize is extremely slow, wondering if grouping into one "exec" command would be significantly faster. Any advice is much appreciated! Thanks, Tom

[govert]

@pts925 From the documentation for sqlite_exec:

The sqlite3_exec() interface is a convenience wrapper around sqlite3_prepare_v2(), sqlite3_step(), and sqlite3_finalize(), that allows an application to run multiple statements of SQL without having to use a lot of C code.

It's tricky to implement in VBA if we're to support the callback function, and I doubt it would make much difference to the performance. You can do the same with regular API calls, and those will be extremely fast anyway. And performance bottlenecks you have in doing updates are due to the database, not the C API calls.

For better insert or update performance you should use transactions - these are called as regular statements.

[pts925]

Wow, using a transaction much much better (clearly I am a novice). Thanks so much for your help!