foreign key problem and batch query processing

[Brandon McCombs]

Hello,

I have 2 questions. I'm new to Access and have a high level DB class
where the current project is simply to implement a db using Access. I'm
relatively familiar with SQL and so far haven't had any issues with the
actual queries. We have to create tables, populate them, and then run
queries against them. I've already run the queries and they run fine
however we need to turn in our results. Is it possible to put all my SQL
queries in a file and have Access run through the file and output the
results to another file or something along those lines? I'm prefer to
avoid having to copy and paste the results into excel or something else
in order to turn them in.

The second question involves creating the tables. I was able to get the
syntax correct for creating the tables without foreign keys, and I was
able to get the syntax correct even when specifying a foreign key as
long as I didn't specify "on update cascade on delete cascade". For some
reason Acccess would fail on "update" or if I put the "on delete" first
it would fail on "delete" keyword.

The command I'm using is:
CREATE TABLE temp ( TAGNUM INT NOT NULL,COMPID TEXT(20), EMPNUM INT,
LOCATION TEXT(20),PRIMARY KEY (TAGNUM), CONSTRAINT EMPNUM_FK FOREIGN KEY
(EMPNUM) REFERENCES Employee ON DELETE CASCADE ON UPDATE CASCADE);

and the error is "syntax error in constraint clause" and it highlights
"delete".

Can anyone tell me what is wrong?
thanks

 

[KARL DEWEY]

The answer to your first question is to click on menu TOOLS - Analyze -
Documenter - Queries - Select all - Options - SQL - Ok - Ok.

Click on the green "W" on the icon bar to move the data to Word as a RTF file.

Can not help with second question.

 

[giorgio rancati]

Hi,

the *ON DELETE CASCADE ON UPDATE CASCADE* command is supported in Sql 92
compatible syntax.
To see Tools menu-->Options-->Tables/Queries-->SQL Server Compatible Syntax
(ANSI 92)

otherwise you can also execute the command in vba using a connection.execute
statement

 

[Brandon McCombs]

Hi,

the *ON DELETE CASCADE ON UPDATE CASCADE* command is supported in Sql 92
compatible syntax.
To see Tools menu-->Options-->Tables/Queries-->SQL Server Compatible Syntax
(ANSI 92)

otherwise you can also execute the command in vba using a connection.execute
statement
----
Currentproject.Connection.Execute "CREATE TABLE temp ( TAGNUM INT NOT
NULL,COMPID TEXT(20), EMPNUM INT,
LOCATION TEXT(20),PRIMARY KEY (TAGNUM), CONSTRAINT EMPNUM_FK FOREIGN KEY
(EMPNUM) REFERENCES Employee ON DELETE CASCADE ON UPDATE CASCADE);"

thanks Giorgio. After I turned that option on it worked fine.

 

[Brandon McCombs]

The answer to your first question is to click on menu TOOLS - Analyze -
Documenter - Queries - Select all - Options - SQL - Ok - Ok.

Click on the green "W" on the icon bar to move the data to Word as a RTF file.

When I get to the point of going to Queries in the Documenter window
there is nothing listed so I can't select anything. When I click on
Options I see an SQL option already checked and I'm not sure where you
are referencing the green W in the icon bar unless its somewhere that I
haven't seen yet. What makes a query show up in the list of queries
within Documenter?

 

[Chris2]

Hello,

I have 2 questions. I'm new to Access and have a high level DB class
where the current project is simply to implement a db using Access. I'm
relatively familiar with SQL and so far haven't had any issues with the
actual queries. We have to create tables, populate them, and then run
queries against them. I've already run the queries and they run fine
however we need to turn in our results. Is it possible to put all my SQL
queries in a file and have Access run through the file and output the
results to another file or something along those lines? I'm prefer to
avoid having to copy and paste the results into excel or something else
in order to turn them in.

Brandon McCombs,

Here is some bare-bones VBA code to do the trick.

Go to Tools > Macro > Visual Basic Editor.

In the Project window pane, left-click open the Modules folder.

Right-click on the Modules folder, and pick Insert > Module

In the Properties window pane, change the Module name to
basPrintQueryDefSQL

In the Module's text window, paste in the following code:

Public Sub ShowQueryDefs()

Dim db As DAO.Database
Dim qds As DAO.QueryDefs
Dim qd As DAO.QueryDef

Set db = CurrentDb()
Set qds = db.QueryDefs

For Each qd In qds
Debug.Print qd.Name
Debug.Print qd.SQL
Next qd

Set qd = Nothing
db.Close
Set db = Nothing

End Sub

Open the Immediate window pane by going to View > Immediate.

You can run this at Run > Run Sub/User Form, or by pressing F5.

Note: The Immediate window pane can only hold a limited amount of
text before the oldest text starts getting dumped to make space for
the newest text. There are ways around this. If you need them,
post back.

The second question involves creating the tables. I was able to get the
syntax correct for creating the tables without foreign keys, and I was
able to get the syntax correct even when specifying a foreign key as
long as I didn't specify "on update cascade on delete cascade". For some
reason Acccess would fail on "update" or if I put the "on delete" first
it would fail on "delete" keyword.

The command I'm using is:
CREATE TABLE temp ( TAGNUM INT NOT NULL,COMPID TEXT(20), EMPNUM INT,
LOCATION TEXT(20),PRIMARY KEY (TAGNUM), CONSTRAINT EMPNUM_FK FOREIGN KEY
(EMPNUM) REFERENCES Employee ON DELETE CASCADE ON UPDATE CASCADE);

and the error is "syntax error in constraint clause" and it highlights
"delete".

Can anyone tell me what is wrong?
thanks

Yes, I can tell you.

MS Access does not support ON DELETE or ON CASCADE via the QueryDef
object. It can't be done this way.

It must be specified in the GUI or via DAO (I did see another poster
mention ADO, as well).


Sincerely,

Chris O.

 

[Brandon McCombs]

Brandon McCombs,

Here is some bare-bones VBA code to do the trick.

Go to Tools > Macro > Visual Basic Editor.

In the Project window pane, left-click open the Modules folder.

Right-click on the Modules folder, and pick Insert > Module

In the Properties window pane, change the Module name to
basPrintQueryDefSQL

Hi Chris, I followed what you said but got hung up on specifying the
module name because when I go to insert a module the submenu is all
greyed out and I can't do anythng. Both Module and Class Module are not
selectable. Any ideas? By the way, for some reason DAO won't install on
my system and I'm not sure why. I don't recall the error so I can't post
it but it's tried to install a few times already based on some things
I've tried to do and then it errors out. Also, is there by a chance a
non-programming way of doing batch queries? Oracle lets you do it and
Iknow Access isn't Oracle but it isn't that complex of a feature.

thanks

In the Module's text window, paste in the following code:

Public Sub ShowQueryDefs()

Dim db As DAO.Database
Dim qds As DAO.QueryDefs
Dim qd As DAO.QueryDef

Set db = CurrentDb()
Set qds = db.QueryDefs

For Each qd In qds
Debug.Print qd.Name
Debug.Print qd.SQL
Next qd

Set qd = Nothing
db.Close
Set db = Nothing

End Sub

Open the Immediate window pane by going to View > Immediate.

You can run this at Run > Run Sub/User Form, or by pressing F5.

Note: The Immediate window pane can only hold a limited amount of
text before the oldest text starts getting dumped to make space for
the newest text. There are ways around this. If you need them,
post back.



Yes, I can tell you.

MS Access does not support ON DELETE or ON CASCADE via the QueryDef
object. It can't be done this way.

It must be specified in the GUI or via DAO (I did see another poster
mention ADO, as well).

Actually it does support it via QueryDef after I followed Giorgio's
advice and turned on SQL 92 support.

 

[Chris2]

Chris2 wrote:

Hi Chris, I followed what you said but got hung up on specifying the
module name because when I go to insert a module the submenu is all
greyed out and I can't do anythng. Both Module and Class Module are not
selectable. Any ideas? By the way, for some reason DAO won't install on
my system and I'm not sure why. I don't recall the error so I can't post
it but it's tried to install a few times already based on some things
I've tried to do and then it errors out. Also, is there by a chance a
non-programming way of doing batch queries? Oracle lets you do it and
Iknow Access isn't Oracle but it isn't that complex of a feature.

thanks

Brandon McCombs,

When the Visual Basic Editor is opened, the Properties window pane
is open by default.

It is in the lower left-hand corder of the screen.

There are two tabs on it. Alphabetic, and Categorized. The
Alphabetic tab is in front by default.

There is only one property on the page. "(Name)"

Because your module is brand new, the name will be Module1.

Left click on the text of "Module1". This will select the text and
allow you to change the name.

If, for some reason, the property window pane isn't showing, go to
View > Properties Window, or press F4.

Note: You don't actually have to change the name of the module. It
won't affect the execution of the code example I gave.

As for DAO not installing on your system is, that is, odd.

In the Visual Basic Editor, go to Tools > References. Go down the
list looking for "Microsoft DAO . . ." and see what you find.

I know of no non-programming way of "batching" queries in MS Access.
You can use Access-macros, but I consider using them to be a form of
programming, and in any event, I don't think you can get at the SQL
string of a QueryDef from Access' macros (Access' macros are,
supposedly, on the way out).

Actually it does support it via QueryDef after I followed Giorgio's
advice and turned on SQL 92 support.

I'm running Access 2k, and that option is not available (and it's
good to hear they've fixed it later on).


Sincerely,

Chris O.