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
What dropbox info do you need?
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).
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.
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.
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. :\
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
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?
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.
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.
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.
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.
Good, seems like I got it working on that XP machine. Just added my first record there.
Ah, good to know that Dick. Who reads manuals anyway??
Try – fail – read manual – try again. That’s how men do it.
I don’t see any new records.
As of 2.43pm I have 16 records, last one is “Yep”
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.
Problems.
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.
That’s too bad. On my side I didn’t see any errors that said it wasn’t submitting properly.
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.
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.
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.
So game off?
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.
late post: I messed with a similar idea a while back that met a similar end. Just sayin’