SQLite and Dropbox

By in Databases on .

I want to put a SQLite3 file in a shared Dropbox folder and run an Excel app with that as the backend database. I’m using the SQLite ODBC Driver and some VBA to drive a simple userform.

My theory is that the file access will be so short and infrequent that I won’t have any file locking problems.

Who wants to test it out? You need a Dropbox account, the aforementioned driver, and a copy of the workbook below. Oh, and Excel if you didn’t already figure that one out.

Install the driver. Leave a comment or send an email to dkusleika@gmail.com with your Dropbox info and I’ll share a folder with you. Then open the workbook and start adding and modifying records in the userform. Ideally, I’d like to coordinate a five minute period where a few of us agree to hammer on it and try to break it.

You can download SqliteContacts.zip

25 thoughts on “SQLite and Dropbox

  1. Jon

    Sure, just let me know when you want to do it so I can make sure to be around to do it. Of course, that’s assuming all the tools are free (which it appeared so).

  2. Dick Kusleika

    I think it’s your email address associated with your dropbox account. I put Jeff and Jon’s email in there (as well as those that emailed me privately), so we’ll see if that works.

    Let’s shoot for 10:40AM to 10:45AM CDT for editing. The database only gets updated when you close the form, so add a record, close, reopen, edit, close.

  3. Dick Kusleika

    I just added a bunch of records, but did not see any that I did not add. Post a comment here if you add a record.

  4. Jon

    Mine was giving me the error that I e-mailed you. error 430. Not sure if my ADO library is corrupt or if it is something different. I saw that it was checked in the references. :\

  5. Juan Pablo González

    Man, I missed the time slot. Darn doctor appointments ;) But I’m getting an error opening the connection:

    ?err.Number, err.Description
    -2147467259 [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

  6. Dick Kusleika

    Jon ran Code Cleaner and the resulting forced recompile fixed the ADO problem. So much for VBA being an interpreted language.

    We’re giving it another shot at 2:45 – 2:50PM CDT if anyone is up and running and interested.

  7. Dick Kusleika

    I fixed a bug, so re-download if you’re playing along. Jon inserted a record and in overwrite one of mine. I was changing the DbStatus property when I should have been. I also made the ADO stuff late bound in case you’re running Win XP.

  8. Dick Kusleika

    I did the same thing the other day JPG. I have a 64bit OS so why wouldn’t I download the 64bit driver. When it didn’t work, I read that you have to match the client, not the OS. I guess I could have posted that in the blog. :) Anyway, the 32bit and 64bit drivers are living side-by-side quite nicely.

  9. Dick Kusleika

    Try – fail – read manual – try again. That’s how men do it.

    I don’t see any new records.

  10. Dick Kusleika

    Dropbox says “can’t sync testdb.db file in use” Did I forget to close the connection? I don’t think so. It let’s me post a new contact. This is going to be a problem, I think.

  11. Dick Kusleika

    Crap. I had it opened in SQLite Manager, which is why it wouldn’t sync on mine. I guess I haven’t really given it a good test, but after that I’m starting to think that Dropbox just won’t be fast enough for this application. Even though we may only have the database locked for a couple of milliseconds, if Dropbox doesn’t sync before we both access it, it will conflict. Even if we change different records, Dropbox will not be able to reconcile. I think this would work for a single user type system, but I’ll need a cloud base database for what I want to do. Thanks for the help guys.

  12. Dick Kusleika

    I saw that. Xeround sounds like they have some bugs to fix. I was checking out Zeus which doesn’t look to bad. I also noticed that I get a free azure something or other with my msdn subscription. I’ll check that out too.

  13. Dick Kusleika

    It’s still out there so you can have at it. But I’m convinced that a file based synced database will never work. Too much latency in the sync process. I might use this system for something small and that’s only me or me and my family. But I was actually trying to do something somewhat robust. Not a ton of transactions, but spurts of activities all centered around the same small current group of records. I’m open to continue discussing it, but I don’t think sqllite on dropbox is going to do it.

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax