Microsoft Access

[Anders]

Is it possible to run separate queries consecutively using Microsoft Access with just one command and for those queries to share parameters entered by the user?

The version of Access is 97 unfortunately

Thanks.

[oyster]

You can do that in an Access Basic module easily.

If you'd asked me back in 1997, I could probably have written the code in less time than it took to reply, but my Access is rather rusty these days.

You can runs queries consecutively in a macro, but I can't remember how to (or even if it is possible to) parameterize queries in a macro.

[Anders]

Access Basic module, I shall look it up, thanks.

Solutions do not have to be Access specific; I am not a fan myself after using it. Is there any particular SQL code that I can use?

[oyster]

All SQL databases come with some way of sending raw SQL commands to the server from a command prompt, and a set of libraries that allow you to send SQL commands from any programming language.

With MS SQL Server, the command line tool is called iSQL, and a simple batch file would allow you to run several queries with a set of shared parameters. If you need anything more complex, then you could call the SQL routines from within a simple VB app.

Basically, pick any programming language, and any SQL server, and it will be able to do what you want.

[directhex]

All SQL databases come with some way of sending raw SQL commands to the server from a command prompt, and a set of libraries that allow you to send SQL commands from any programming language.

With MS SQL Server, the command line tool is called iSQL, and a simple batch file would allow you to run several queries with a set of shared parameters. If you need anything more complex, then you could call the SQL routines from within a simple VB app.

Basically, pick any programming language, and any SQL server, and it will be able to do what you want.

if you're going as far as to write your own app, then you may find obdc a bit more efficient and backend-generic than stringing together command line sql INSERTs

[oyster]

yeah, sorry, I didn't mean to suggest that stringing together command line SQL commands was a sensible way of doing this in VB. Each language will have its own libraries for SQL access, and ODBC is the most common, although MS is pushing .NET on us all these days, so you could buy a couple of books on C# or VB.NET and ADO.NET, then get paid silly money for having .NET development experience.

[Anders]

Thank-you for the info.

Do you know of any examples of iSQL being used? Been looking around in Google but not come across anything yet.

ODBC, I seem to remember doing something like that at Uni, more Googling for me.

[directhex]

odbc is a generic database backend allowing app A to read/write generic sql from database backend B (be it mysql, mssql, or msjet) pretty much transparently.