Make table from Cross tab with Parameter Fails

[Rikknor]

All,

Access 2007 SP2

Using a make table query based on a cross tab query to create/populate a table.

The cross tab is based on a couple of other select queries which use form references to get parameter information.

The cross tab itself with defined parameters works.

The added dynamic parameter was a recent change. Previously, the queries the cross tab was based on did not have any "dynamic" parameters. The make table query worked as expected.

i.e. (Before Parameters) Messages with warnings on
1. Warning you're going to run an action query.
2. The existing table will be deleted.
3. Copying xxx records to the table


now with the added parameter the messages are
1. Warning you're going to run an action query.
2. Table already exists

Well, of course the table exists, the delete step was skipped !!

Yes, deleting the table before hand works but, does not solve the problem.
Also All other make table queries work as expected regardless of Parameter usage.

Just wondering if anyone else has seen this or is it just me because scouring the web doesn't yield much info on this.

 

[SydneyGeek]

To run a crosstab with parameters (from VBA) you need to define the parameters.
On the Design tab click Parameters (at the right end) and you will get a dialog. Enter, in the left column, each parameter EXACTLY as it appears in the criteria grid; in the right column, select the data type.

Denis

 

[Rikknor]

Denis,

Thank you for your reply but, perhaps I wasn't very clear. The cross tab query with parameters works fine. No problems with the cross tab. I have defined the parameters in the cross tab correctly for name and type.


It is the Make table query that is based on the cross tab that fails when there are parameters in the cross tab. When there were no parameters in the crosstab, the make table query works correctly.

As I stated in the original post, running the make table query, if messages are on ,then the messages are (with no parameters defined in the cross tab)

1. A warning that I'm about to run an action query (Expected)
2. A warning that the existing table will be deleted (Expected)
3. A notification that X number of records were written to the new table (Expected)


If the Crosstab has parameters then the messages from running the make table are

1. A warning that I'm about to run an action query (Expected)
There is no warning about the previous table being deleted (because it was in fact not deleted)
2. An An error that the create table portion of the make table query failed. "File already exists" (Totally Unexpected)

What happend to the delete portion of the make table query?

If I "Pre" delete the table before the make table query runs, all is good. This is the work around I have used to get the application into production.

I just don't understand why the delete portion of the make table query is skipped when the cross tab has parameters.

Every other make table query in this application, whether that query has parameters or not, works as expected.

Is there a property or setting I should look to? Something with the query definitions? I'm grasping here. I'm not really sure there is an answer to this but I appreciate your time and effort.

 

[SydneyGeek]

Hi Rick, I tried to reproduce your problem but couldn't... it works for me.

I ran it in code, which lets you suppress the warnings. To give a idea, here are 3 functions:

Function MakeTable_1()
    DoCmd.OpenQuery "qmakQtrOrdersByProd"
End Function

Function MakeTable_2()
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qmakQtrOrdersByProd"
    DoCmd.SetWarnings True
End Function

Function MakeTable_3()
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qmakQtrOrdersByProd_Param"
    DoCmd.SetWarnings True
End Function

The first runs the make-table on the full crosstab. So does the second but you don't get the warnings.
The third uses a crosstab with a parameter. Run the code and enter the parameter, and the table (identical for all 3 routines) is replaced and re-created.

So... no real answer to your question but it shouldn't be happening. However, if you really do need to delete the table you can do that in code too.

1. Alt+F11 to go to the code window, then Tools > References and check the reference for the Microsoft ADO Ext. 2.x For DDL And Security (x is the highest number on your system, most likely you will have 2.7 or 2.8)

2. Add this line before the code that runs the make-table:

    DoCmd.RunSQL ("DROP TABLE [Your Table Name]")

Denis

 

[Rikknor]

Thanks Denis. I was hoping it wasn't just me with this issue. But the lack of infomation on the web and your confirmation, indicates that somehow my install has a quirk. If I find an answer I'll post a followup to this thread.