ODBC/ADO Procedure Limitation

An ODBC connection can be used to create a PROCEDURE; however, it cannot be used to delete.

An ADO connection can be used to both create and delete a PROCEDURE.

PROCEDURE Limitations

There are a limited set of SQL command that are valid to be performed in a procedure. CREATE is not one of them; however, the error message seems to indicate that DELETE, INSERT, PROCEDURE, SELECT and UPDATE are.

Query Design Window

One of the nice things about using the design windows is that you can create a massive query graphically. This avoid a whole lot of type, typos, and other sorts of human error.

Procedure Call

Procedure calls can be done with parameters. However, for Access 2003 using the keyword "PROCEDURE" creates a 'General SQL error'. Using "PROC" however, does work.

The following example worked in Borland.

str = "CREATE TABLE tblInvoices2 ("
" InvoiceID INTEGER NOT NULL,"
" InvoiceDate DATETIME NOT NULL)";
qry->Execute( str );

str = "INSERT INTO tblInvoices2 VALUES (1, '1/1/1999')";
qry->Execute( str );
str = "INSERT INTO tblInvoices2 VALUES (1, '1/1/1998')";
qry->Execute( str );

str = "CREATE PROC DeleteInvoices2 "
" (InvoiceDate DATETIME) AS "
" DELETE FROM tblInvoices2 "
" WHERE tblInvoices2.InvoiceDate < InvoiceDate ";

// create procedure
qry->Execute( str );

str = "EXECUTE DeleteInvoices2 '1/1/1999' ";
qry->Execute( str );

The final result was that the new table only contained the 1/1/1999 date.

SQL Under ACCESS

Fundamental Microsoft Jet SQL for Access 2000: http://msdn2.microsoft.com/en-us/library/aa140011(office.10).aspx

Intermediate Microsoft Jet SQL for Access 2000: http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx

These are a couple of technical articles that go into SQL under ACCESS.

Executing a Query

To execute a query in ACCESS from outside access, one uses the following syntax

EXECUTE qurey_id;

I would recommend that actual command be as follows:

EXECUTE [query_id];

This will help avoid any problems that might arise because the ACCESS ID can be syntactically invalid for SQL.

Using EXECUTE is also how a SQL procedure is called.

The following is a link to an example of using EXECUTE and VB: : http://www.devcity.net/Articles/34/msaccess_sp2.aspx

Table Present?

How do I tell if a table is present?