SQLite and Dropbox

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 Comments

  1. Jeff Weir says:

    What dropbox info do you need?

  2. Jon says:

    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).

  3. Dick Kusleika says:

    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.

  4. Dick Kusleika says:

    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.

  5. Jon says:

    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. :\

  6. 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

  7. Dick Kusleika says:

    I can’t believe I haven’t run into that 430 error yet. http://support.microsoft.com/kb/2517589

    Juan, did you install the SQLite driver?

  8. Dick Kusleika says:

    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.

  9. Yep, I installed the 64bit version on my main machine. I just installed the 32bit on a virtual machine and it seems to be working.

  10. Dick Kusleika says:

    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.

  11. Dick Kusleika says:

    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.

  12. Good, seems like I got it working on that XP machine. Just added my first record there.

  13. Ah, good to know that Dick. Who reads manuals anyway??

  14. Dick Kusleika says:

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

    I don’t see any new records.

  15. As of 2.43pm I have 16 records, last one is “Yep”

  16. Dick Kusleika says:

    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.

  17. Dick Kusleika says:

    Problems.

  18. I’ve got 7 conflicts on my end, but was able to add some clients. I added a JPG248, but am missing JPG2481 and JPG2482.

  19. Jon says:

    That’s too bad. On my side I didn’t see any errors that said it wasn’t submitting properly.

  20. Dick Kusleika says:

    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.

  21. I was looking at a MySQL on the cloud yesterday and found this:

    http://xeround.com/

    They have a free version, with a DB of up to 10MB and 5 connections.

  22. Dick Kusleika says:

    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.

  23. Jeff Weir says:

    So game off?

  24. Dick Kusleika says:

    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.

  25. dan l says:

    late post: I messed with a similar idea a while back that met a similar end. Just sayin’

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: