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?

Stored Queries and MSysObjects Error

Although there are not stored procedures in ACCESS, there are stored queries.

This is a web page that tells one how to do stored queries.

http://www.stardeveloper.com/articles/display.html?article=2001050101&page=1

I had a whole bunch of trouble getting around the MSysObjects problem.

'Record(s) cannot be read; no read permission on 'MSysObjects''

This page gave the following instructions on how to set the privileges.

http://forums.belution.com/en/sql/000/015/39.shtml

In MS Access
Select Tools-Options-View-Show-System objects, Click OK
(this makes the system object tables visible)

Select Tools-Security-User and group Permissions
Select MSysObjects from object names
Add 'Read Design' and Read Permissions

The basic problem is that MSysObjects is undocumented in ACCESS. That makes it hard to figure out what to do.

Graphical Front End for Queries

This tool would create routines that would be called from the programming language.

This could also be a tool that could be used on any database letting you look directly at the table and queries, etc.

Teach SQL or Query Design Window First

Which should be taught first?

Would it be better to teach creating with the Query Design Window first?

The Query Design window is limited. There should be a better way of doing this.

If you teach from the window you can teach the concepts without getting bog down in the details of the SQL language.

The Query Design window also gives an way of learn the underlining SQL it generates.

How to Understand the Query Design Window

I believe that the reason I found this so confusing in the beginning was that I did not have a good overview of what was going on.

You use the Query Design window to graphically create queries. It greatly simplifies the creation of most types of queries and also help avoid some of human errors that might enter in when the SQL for the query is created in text editor.

The window can create and edit several types of queries.

• Select Query
• Crosstab Query
• Make-Table Query
• Update Query
• Append Query
• Delete Query

These query options are under Query menu option. This menu option becomes available when a query is selected or created in design mode.

By default the window assumes you are creating a SELECT query. If you want a query other than the SELECT query, go the Query menu and select the type of query you want.

Depending on the query selected, there may be other information that has to be entered.

The Append query adds new records to a table using the INSERT SQL command.

Joins can be created for a Select query by adding the tables to the top section of Query Design window select the field to be joined on and than dragging the field to the matching field in the other dialog. The first dialog select is the left table and the second dialog is the right table. By default two table will be joined when placed in the upper table section of the window with they have a matching field label. The first table place in the section becomes the left table and the second table become the right table.

Although the design window may look the same between different queries, the SQL code generated and it functionality and be very different.

Is there some way that an interface could more clearly show the differences between types of queries?

There are also some other specialty types of queries that can be created by selecting one of the wizards rather than Design View in the New Query dialog.

This is a list of the options available in the New Query dialog:

• Design View
• Simple Query Wizard
• Crosstab Query Wizard
• Find Duplicates Query Wizard
• find Unmatched Query Wizard

The wizard can simplify the creation a very complex query. The following was created using the Duplicate Query wizard.

SELECT [TO], [SYSTEM]
FROM TOwWildcard
WHERE [TO] In (SELECT [TO] FROM [TOwWildcard] As Tmp GROUP BY [TO] HAVING Count(*)>1 )
ORDER BY [TO];

Moving Graphic Elements Within a Form

If I cannot get the column to line up, move the whole graphic with the and arrow keys to where the line up should be. This only happens if I move something the arrow keys and than try to get something to match moving it some other way. Probably need to make sure that when I do move, I move it to the proper grid line.

Creating An Append Query

To create an Append Query do the following:

1. Select a new query.
2. Add the table you are pulling from.
3. By the default the query is a ‘Select’ type.
4. Select the table you are to append or insert into
5. Select the fields to be insert into the table.
6. Drag the select fields from the section of the dialog to the field section in the bottom of the dialog. If the field match up correctly, you are done.

Note that the SQL created looks as follows:

INSERT INTO TOwWildCardWKey
SELECT TOwWildcard.*
FROM TOwWildcard;

The word append is not used in SQL.

Double clicking on the field to be include to automatically include the field in the bottom half of the dialog called the query design grid.