Excel VBA - How to connect to an encrypted SQLite database

Question

Dear All,

I have created an SQLite sample database and through the below code I'm able to copy the content of the table into an excel sheet:

Private Sub cmdConnect_Click() 

    Dim Conn As ADODB.Connection 

    Dim Rst As ADODB.Recordset 

    Dim Conn_String As String 

    Dim Database_path As String 

    Dim SQL_statement As String 

    Dim i As Long     

    Database_path = "D:\FOLDER\Development\SQLlite_VBA\DB_SQLite" 

    Conn_String = "driver=SQLite3 ODBC Driver;" 

    Conn_String = Conn_String & "Database=" & Database_path & ";" 

    Set Conn = New ADODB.Connection 

    With Conn 

        .Open Conn_String 

        .CursorLocation = adUseClient 

    End With                 

    SQL_statement = "SELECT * FROM 'Export_Table'"     

    Set Rst = New ADODB.Recordset 

    With Rst 

        .CursorLocation = adUseClient 

        .Open Source:=SQL_statement, ActiveConnection:=Conn 

    End With         

    With ThisWorkbook.Worksheets("Export") 

        .Cells.ClearContents 

        .Range("A2").CopyFromRecordset Rst 

        For i = 0 To Rst.Fields.Count - 1 

            .Cells(1, i + 1) = Rst.Fields(i).Name 

        Next i 

    End With                         

    Rst.Close 

    Conn.Close     

    Set Rst = Nothing 

    Set Conn = Nothing 

    ThisWorkbook.Worksheets("Export").Range("A2").Select     

End Sub

I have encrypted the above database by setting a password through DB Browser (SQLCipher)

Now I'm not able to find the correct Connection String to this encrypted database.

Could you help me?

Thanks

Answer 1

Hi,lucausa75

Thank you for consulting Microsoft Community.

To connect to an encrypted SQLite database using Excel VBA, you need to include the encryption password in your connection string. Since you used SQLCipher for encryption, your connection string should include the Password parameter. Here’s how you can modify your existing code:

Private Sub cmdConnect_Click() Dim Conn As ADODB.Connection Dim Rst As ADODB.Recordset Dim Conn_String As String Dim Database_path As String Dim SQL_statement As String Dim i As Long
Database_path = "D:\FOLDER\Development\SQLlite_VBA\DB_SQLite" Conn_String = "driver=SQLite3 ODBC Driver;" Conn_String = Conn_String & "Database=" & Database_path & ";" Conn_String = Conn_String & "Password=your_password;" ' Add your password here Set Conn = New ADODB.Connection With Conn .Open Conn_String .CursorLocation = adUseClient End With
SQL_statement = "SELECT * FROM 'Export_Table'"
Set Rst = New ADODB.Recordset With Rst .CursorLocation = adUseClient .Open Source:=SQL_statement, ActiveConnection:=Conn End With
With ThisWorkbook.Worksheets("Export") .Cells.ClearContents .Range("A2").CopyFromRecordset Rst For i = 0 To Rst.Fields.Count - 1 .Cells(1, i + 1) = Rst.Fields(i).Name Next i End With
Rst.Close Conn.Close
Set Rst = Nothing Set Conn = Nothing ThisWorkbook.Worksheets("Export").Range("A2").Select
End Sub Replace your_password with the actual password you set for your SQLite database.

If you encounter any issues, make sure that the ODBC driver you are using supports encrypted SQLite databases. You might need to check the documentation for the specific driver you are using to ensure it supports the Password parameter.

We look forward to your feedback on the results of the operation, if you need further assistance, please feel free to reply.

Cherry.Z|Microsoft Technical Support Specialist

Answer 2

Thanks for your feedback but now I get this error:

1. Any suggestion/solution?
2. Any alternative to SQLite that can be encrypted?

Thanks

Answer 3

Hi,LucaTramontana

Received your reply. If you can, it is recommended that you use lucausa75 to continue replying in this thread instead of LucaTramontana.

Regarding your first question:

The error message you’re encountering, -2147467259 (80004005) File is not a database (26), typically indicates that the connection string or the database file itself might not be correctly recognized as a valid SQLite database. Here are a few steps to troubleshoot and resolve this issue:

1. Verify the Database Path: Ensure that the path to your database file is correct and that the file exists at that location.
2. Check the Encryption: Make sure that the database is correctly encrypted and that you are using the correct password. Sometimes, re-encrypting the database using DB Browser for SQLite can help.
3. Update the Connection String: Ensure that your connection string is correctly formatted. Here’s an updated version of your connection string: Conn_String = "DRIVER=SQLite3 ODBC Driver;Database=" & Database_path & ";Password=your_password;"
4. Install the Latest ODBC Driver: Make sure you have the latest version of the SQLite ODBC driver installed. You can download it from here.

Regarding your second question:

Here are a couple of options:

1. SQLCipher: This is an open-source extension to SQLite that provides transparent 256-bit AES encryption of database files. It’s a good alternative if you want to stick with SQLite but need stronger encryption.
2. MySQL or PostgreSQL: Both of these databases support encryption and are widely used. They might be more complex to set up compared to SQLite, but they offer robust security features.

If you need further assistance, feel free to ask!

Cherry