Compare two exact the same tables from different database

May 18, 2012 at 3:23 PM

Hi Govert,

I am looking for a way to compare two of the same table in different databases. I know how to do this in a loop one by one. But is there not an easier way to do this with a select statement?

SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE t2.Id = t1.Id) (same db)

I am struggeling with the VBA SQLITE syntax to achief this. Can you give me a clue?

Regards,

Henk

May 19, 2012 at 12:27 AM

Hi Henk,

I'm not really much of a SQLite expert. I'd suggest you download and experiment in the SQLite command-shell first. And then if you're stuck, maybe StackOverflow would be a good place to ask your question. Once you have the query working right, you can implement it in your Excel.

-Govert

May 22, 2012 at 10:16 AM

Hi Govert,

I managed to create the proper SQLite SELECT statement. I tested the statement in Navcat and it worked. When I try the same in SQLite for VBA then I get an error message.

It seems to have difficulties when I refer to a table or field with the database name (ie main.task or main.task.pk). Do you know if this is a limitation of the library? Or do I use the wrong reference?

'OPEN main database    RetVal = SQLite3Open("C:\SQLTEST\main.db", myDbHandle)

'ATTACH import database    dbFile = "C:\SQLTest\import.db"
RetVal = SQLite3PrepareV2(myDbHandle, "ATTACH DATABASE " & Chr$(34) & dbFile & Chr$(34) & " AS importdb", MyStmtHandle)

Debug.Print "Attach result: " & RetVal 'Result 0

RetVal = SQLite3Finalize(stmtHandle)   

Debug.Print "Finalize result: " & RetVal 'Result 0       

'COMPARE tables from different databases with identical fields and add result from SELECT statement to main database           

RetVal = SQLite3PrepareV2(myDbHandle, "INSERT INTO task SELECT * FROM importdb.task WHERE NOT EXISTS (SELECT * FROM main.task WHERE main.task.pk=importdb.task.pk)", MyStmtHandle) 

Debug.Print "SQLite3PrepareV2 returned " & RetVal 'Result 21

' Finalize (delete) the statement   

RetVal = SQLite3Finalize(MyStmtHandle)   

Debug.Print "SQLite3Finalize returned " & RetVal 'Result 0

 

Regards,

 

Henk

May 22, 2012 at 6:22 PM
Edited May 22, 2012 at 6:22 PM

Hi Henk,

The SQLite for Excel functions don't really process or change the SQLite calls - so the results and errors you get come directly from SQLite. In this case, the '21' returned from SQLite2PrepareV2 is defined in the SQLite module like this:

Public Const SQLITE_MISUSE     As Long = 21   ' Library used incorrectly

This means you are using the SQLite API incorrectly. From the snippets you post, I wonder if this might be the problem: You use the variable name 'MyStmtHandle" for the Attach, but then you call SQLite3Finalize with a variable called "stmtHandle".

Maybe a case where "Option Explicit" could help?

Otherwise you'll need to check your sequence of SQLite calls against the SQLite API documentation.

-Govert


May 23, 2012 at 12:16 PM

Hi Govert,

Thank you for your comment. It's not easy to learn scripting, sql and make a program work! Because I forgot a "Step" statement twice the importdb was not actually attachted and the INSERT command not executed. Now it works!

Regards,

Henk

 

'OPEN main database    RetVal = SQLite3Open("C:\SQLTEST\main.db", myDbHandle)

'ATTACH import database    dbFile = "C:\SQLTest\import.db"
RetVal = SQLite3PrepareV2(myDbHandle, "ATTACH DATABASE " & Chr$(34) & dbFile & Chr$(34) & " AS importdb", MyStmtHandle)

Debug.Print "Attach result: " & RetVal 'Result 0

RetVal = SQLite3Step(MystmtHandle)

Debug.Print "Step result: " & RetVal 'Result 101       

RetVal = SQLite3Finalize(MystmtHandle)   

Debug.Print "Finalize result: " & RetVal 'Result 0       

'COMPARE tables from different databases with identical fields and add result from SELECT statement to main database           

RetVal = SQLite3PrepareV2(myDbHandle, "INSERT INTO main.task SELECT * FROM importdb.task WHERE NOT EXISTS (SELECT * FROM main.task WHERE main.task.pk=importdb.task.pk)", MyStmtHandle) 

Debug.Print "SQLite3PrepareV2 returned " & RetVal 'Result 0

RetVal = SQLite3Step(MystmtHandle)

Debug.Print "Step result: " & RetVal 'Result 101       

' Finalize (delete) the statement   

RetVal = SQLite3Finalize(MyStmtHandle)   

Debug.Print "SQLite3Finalize returned " & RetVal 'Result 0

May 23, 2012 at 12:21 PM

I'm glad you got it to work!

-Govert