Using SQLite wirh Excel

[pcc]

I have an application using Excel 2000 to write data to, and read data from, an Access 2007 database (using, ADO, and SQL created within Excel VBA).
This works OK except that the volumes of data, and the fact that I am creating temporary tables and deleting them, means that the database bloats, and, when it gets to 2Gb, the system stops working. I am told that SQLite might be a solution, but have no experience of using this.
Does anyone use SQLite in this fashion, and if so, can you point me in the right direction to get started (eg syntax for the connection string to the database, any peculiar idiosynchracies in the SQL language that might be relevant etc?

Hope someone can help out, all information gratefully received.
Regards

 

[Ruddles]

Re: Using SQLite with Excel

Don't know about SQLite but isn't it possible to trigger a compact and repair from Excel?

 

[xenou]

I was thinking the same ... compact and repair regularly, if it's just bloat. SQLite is very different from Access and I've not used it much - feel free to find out more if you like. You can also use SQL Server Express (free edition). We've been chatting about SQLite the last few days in another thread:
http://www.mrexcel.com/forum/showthread.php?t=551558

This seems another interesting thread:
http://stackoverflow.com/questions/...-access-an-sqlite-database-in-microsoft-excel

This guy's a big SQLite fan:
http://blog.gobansaor.com/

 

[Norie]

I don't think SQLite is the answer.

It does have SQL limitations, one of which I found quite surprising.

That was, apparently, it only supports certain types of joins.

 

[pcc]

Thanks for replies. I can't compact the data base on the fly since that would require using a PC with Office 2007 intalled (which I have available).Trouble is that in my company Office 2007 is installed in Vista machines, and in the machine goes into screen lock due to inactivity, then when the systemis unlocked, Excel has frozen and has to be aborted. The Office 2k mashines do not have this problem, but I csn't use them to compact the later version of Access..