SQL using IF THEN ELSE

[Carl]

Hi,

Using MS Access 2000, is it possible to run a UPDATE or INSERT SQL query
using some form
of conditional IF THEN ??

for example:

SELECT * FROM Books
IF EXISTS(Select Books.ID = 1)
THEN (UPDATE Books.Stock VALUES(10))
ELSE (INSERT Books.ID Books.Stock VALUES(1,10)

I've googled for hours and found no soultions.
TIA.

[Arno R]

"Carl" <mail2carl@_remove_yahoo.com> schreef in bericht news:tT*****************@newsfe6-gui.ntli.net...

Hi,

Using MS Access 2000, is it possible to run a UPDATE or INSERT SQL query
using some form
of conditional IF THEN ??

for example:

SELECT * FROM Books
IF EXISTS(Select Books.ID = 1)
THEN (UPDATE Books.Stock VALUES(10))
ELSE (INSERT Books.ID Books.Stock VALUES(1,10)

I've googled for hours and found no soultions.
TIA.

You need syntax like:
If SomethingIsTrue Then
RunUpdateQuery
ELSE
RunInsertQuery
End if

Or in (air)code:
Dim db as DAO.Database
Dim rst as DAO.recordset
Dim strSQL as string
strSQL="SELECT * FROM Books WHERE ID = 1
Set db=Currentdb
set rst=db.OpenRecordset(strSQL)
if not rst.EOF Then 'Record exists
rst.edit
.... 'Do your thing here
rst.Update
else 'Create new record
rst.AddNew
rst!BookID = 1
.... 'Do your thing here
rst.Update
end if
rst.close
set rst=Nothing

Arno R

[David W. Fenton]

"Carl" <mail2carl@_remove_yahoo.com> wrote in
news:tT*****************@newsfe6-gui.ntli.net:

Using MS Access 2000, is it possible to run a UPDATE or INSERT SQL
query using some form
of conditional IF THEN ??

for example:

SELECT * FROM Books
IF EXISTS(Select Books.ID = 1)
THEN (UPDATE Books.Stock VALUES(10))
ELSE (INSERT Books.ID Books.Stock VALUES(1,10)

I've googled for hours and found no soultions.

Jet SQL does not support multiple SQL statements, or conditional
code of this nature (of course, you can use IIf() for items in a
SELECT clause, though).

In Access, you can only do this with VBA code, as Arno has
suggested.

However, assuming you're updating one table from another one, you
could also do it with two queries that you run in succession that
would not overlap if you use the proper joins. For the UPDATE, you'd
use an inner join between the table1 and table2, which limits the
records updated to those that exist in both tables. For the insert,
you'd want an outer join where you returned the records in your
update table where the ID is Null in the target table. This would
mean that only the records that don't already exist would get
inserted.

Then you could execute both queries in succession and get exactly
the same result as you would with your single query with a
conditional structure. It would also probably be faster, since your
example has to re-execute for each row, whereas the queries I'm
suggesting would operate on sets.

Of course, if your input criteria are not coming from a table, then,
yes, you have to do it in code.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Carl]

Thanx Arno,David

So VBA procedure is best option in ms access 2000.

I will be executing the query from a perl script using the DBI::ODBC module.
However, I have yet to find a way of running stored procedures or VBA code
from the perl script.
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.90...

"Carl" <mail2carl@_remove_yahoo.com> wrote in
news:tT*****************@newsfe6-gui.ntli.net:

Using MS Access 2000, is it possible to run a UPDATE or INSERT SQL
query using some form
of conditional IF THEN ??

for example:

SELECT * FROM Books
IF EXISTS(Select Books.ID = 1)
THEN (UPDATE Books.Stock VALUES(10))
ELSE (INSERT Books.ID Books.Stock VALUES(1,10)

I've googled for hours and found no soultions.

Jet SQL does not support multiple SQL statements, or conditional
code of this nature (of course, you can use IIf() for items in a
SELECT clause, though).

In Access, you can only do this with VBA code, as Arno has
suggested.

However, assuming you're updating one table from another one, you
could also do it with two queries that you run in succession that
would not overlap if you use the proper joins. For the UPDATE, you'd
use an inner join between the table1 and table2, which limits the
records updated to those that exist in both tables. For the insert,
you'd want an outer join where you returned the records in your
update table where the ID is Null in the target table. This would
mean that only the records that don't already exist would get
inserted.

Then you could execute both queries in succession and get exactly
the same result as you would with your single query with a
conditional structure. It would also probably be faster, since your
example has to re-execute for each row, whereas the queries I'm
suggesting would operate on sets.

Of course, if your input criteria are not coming from a table, then,
yes, you have to do it in code.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Carl" <mail2carl@_remove_yahoo.com> wrote in
news:LC*****************@newsfe6-gui.ntli.net:

So VBA procedure is best option in ms access 2000.

I will be executing the query from a perl script using the
DBI::ODBC module. However, I have yet to find a way of running
stored procedures or VBA code from the perl script.

Well, you can't do that.

If you can use COM components from perl, then you could automate
Access and do it, but I don't think that makes any sense at all.

I was assuming your Access users were going to do the importing.

I have no suggestions for how you'd do this in perl or via ODBC. I
would think the code structure would be pretty much identical, but
the details would be different.

I don't understand why you'd *want* to do it from perl. Why
manipulate Jet data with non-native tools?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Carl]

The Access database will be stored on a webserver and the perl script
will be executing all the database queries and returning the results to the
clients web browser.

As I have limited access to the Webserver and no SQL server, Perl and
ODBC are the only soultions at the moment. I could use Perl OLE and ADODB,
but this would only support Windows servers.

The Perl DBI driver is excellent for administering Access databases on
both Unix and Windows webservers.

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.90...

"Carl" <mail2carl@_remove_yahoo.com> wrote in
news:LC*****************@newsfe6-gui.ntli.net:

So VBA procedure is best option in ms access 2000.

I will be executing the query from a perl script using the
DBI::ODBC module. However, I have yet to find a way of running
stored procedures or VBA code from the perl script.

Well, you can't do that.

If you can use COM components from perl, then you could automate
Access and do it, but I don't think that makes any sense at all.

I was assuming your Access users were going to do the importing.

I have no suggestions for how you'd do this in perl or via ODBC. I
would think the code structure would be pretty much identical, but
the details would be different.

I don't understand why you'd *want* to do it from perl. Why
manipulate Jet data with non-native tools?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Carl" <mail2carl@_remove_yahoo.com> wrote in
news:ss***************@newsfe4-gui.ntli.net:

The Access database will be stored on a webserver and the perl
script will be executing all the database queries and returning
the results to the clients web browser.

As I have limited access to the Webserver and no SQL server, Perl
and ODBC are the only soultions at the moment. I could use Perl
OLE and ADODB, but this would only support Windows servers.

The Perl DBI driver is excellent for administering Access
databases on both Unix and Windows webservers.

You're *not* administering an Access database -- you're
administering a *Jet* database.

Access is separate from the db engine, and has its own set of
objects that are stored in Jet tables within an MDB file. You may
have used Access to create the Jet database, but if you're using it
only for data storage, you just aren't using Access in any
significant degree.

And on your web server, only Jet is involved.

Now, you don't say how the data is getting to the end users. If
you're sending them the MDB file, you could put VBA code in it along
with a UI and you could have them perform the operations I outlined
by opening the db in Access (and it's meaningfully an Access db if
you're putting code and forms in it).

But, really, all that I outlined was the VBA method for executing
SQL against the data tables. You should be able to do this via ODBC,
using perl's functions to do the comparisons necessary to determine
if a field needs to be updated.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Carl]

True, I think I will now decide to use Perl functions to do the IF THEN ELSE
code
part of the query and then just execute the required UPDATE or INSERT query.
I'm not going to get involved with the VBA side of it...I dont know VBA all
that well
yet.

The MDB database is permanently stored on the webserver, users will not have
access
to the MDB file itself...only me. The web users (clients) will be accessing
the database
through web pages...simple searches and updates etc....just think of it as
an online
shopping database with Perl doing all the processing.

Thnx for your advice.

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.90...

"Carl" <mail2carl@_remove_yahoo.com> wrote in
news:ss***************@newsfe4-gui.ntli.net:

The Access database will be stored on a webserver and the perl
script will be executing all the database queries and returning
the results to the clients web browser.

As I have limited access to the Webserver and no SQL server, Perl
and ODBC are the only soultions at the moment. I could use Perl
OLE and ADODB, but this would only support Windows servers.

The Perl DBI driver is excellent for administering Access
databases on both Unix and Windows webservers.

You're *not* administering an Access database -- you're
administering a *Jet* database.

Access is separate from the db engine, and has its own set of
objects that are stored in Jet tables within an MDB file. You may
have used Access to create the Jet database, but if you're using it
only for data storage, you just aren't using Access in any
significant degree.

And on your web server, only Jet is involved.

Now, you don't say how the data is getting to the end users. If
you're sending them the MDB file, you could put VBA code in it along
with a UI and you could have them perform the operations I outlined
by opening the db in Access (and it's meaningfully an Access db if
you're putting code and forms in it).

But, really, all that I outlined was the VBA method for executing
SQL against the data tables. You should be able to do this via ODBC,
using perl's functions to do the comparisons necessary to determine
if a field needs to be updated.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Carl" <mail2carl@_remove_yahoo.com> wrote in
news:JO*****************@newsfe6-gui.ntli.net:

The MDB database is permanently stored on the webserver, users
will not have access
to the MDB file itself...only me. The web users (clients) will be
accessing the database
through web pages...simple searches and updates etc....just think
of it as an online
shopping database with Perl doing all the processing.

Well, I obviously misunderstoond your entire scenario!

I don't even understand the need to do what I suggested now -- it
was entirely based on the idea of getting website updates out to
users who had Access only, which is why I've programmed this kind of
thing so many times in the past.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc