I need a database that can be accessed by more than 1 user at a time

[computergroove]

I have seen bits and pieces about using sqlite with autoit. Can I get info from and then write info to a sql database using autoit and sqlite? What are the limitations?

[jchd]

As it comes out of he box, SQLite _may_ have issues with a database shared by several users over a LAN. Of course sharing over the Internet is out of question (for any engine).

The actual cause lies in bugs in network protocols implementations (especially SMB file locking) of almost every OS available. SQLite by itself can't do much against that.

In practice however and depending on the load you envision you may have success if you follow simple rules and the number of users and the nature of transactions and their volume is reasonable and consistent with the speed and load of your network. It all depends of your actual context, which you didn't explain.

There are several solutions to satisfactorily circumvent this problem in all cases, many of them listed here.

OTOH you may have as many as needed processes/users sharing an SQLite DB on the same computer. But still follow simple transaction rules.

[computergroove]

This will be for a college. Students will login to the app and answer several questions. After the questions are answered then the app will print all of the content to paper. If someone logs in and has to leave and come back, the application needs to retain their previous answers. A login with administrator privileges will be able to manually modify the data by adding or removing student info. The program has a pretty big scope and I wanted my contact to use an html based solution but he wants a standalone app. The SQL database will be on a server on the LAN and several computers will run the app and access the data on the server. My initial thought is that Autoit is not a good fit for this but if it can do it then I want to move forward with it.

[saywell]

MySQL should meet your needs.

There's an Autoit UDF for it, too.

William

[jchd]

Thanks for explaining this, which makes the picture much clearer.

First and if AutoIt is your cup of tea, I see no good reason why you should switch to something else. As an example, I first tried AutoIt and found it had excellent features to develop applications helping us to run our busines. For most purposes the platform offers great flexibility with few drawbacks.

Now for the DB sharing, I wouldn't recommend a "bare feet" use in your case. From what I understand, the application ressembles much a form to fill/update/display/print. This is quite different from several users performing concurrent rad/update/write on multiple tables with complex relations between them. Even if you can have a number of students using the application (hence the DB) at the same time, I suspect the queries and updates would be fairly simple. Of course you're likely to need a student table with a couple of like data but they look to be mostly lookpup tables to me.

What you can do without going too deep in a blind alley is try two routes: one is using the other is using that ODBC driver. In your case and givent that the workload seems fairly low, I would favor the first solution as it relies on simple code you have control over. Christian Verner ODBC is very good but more oriented toward integration with the language it was developped for.

Another approach will be to switch to a client/server DB engine, like PostgreSQL or MySQL but they need much more administrative care than the single_file_no_fuss way SQLite uses. As another poster just said, there is a MySQL UDF available.