RunSQL - MakeTable Query

[The Merg]

I am using the DoCmd.RunSQL command to execute a MakeTable query. I get
prompted with the standard "your current table will be deleted" and "xx rows
will be added to your table" prompts. Is there anyway to have it
automatically accept those options and move on?

Thanks,
Merg

 

[Gina Whipp]

Try..

DoCmd.SetWarnings False 'Turns them off
DoCmd.RunSQL etc...
DoCmd.SetWarnings True 'Turns them back on

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index.htm

 

[The Merg]

Thanks. Easy enough.

- Merg

 

[boblarson]

A few things you should think on though -

1. DoCmd.SetWarnings is something I would avoid if at all possible as you
can find yourself without warnings if something glitches before you turn them
back on. AND, it affects ALL of your databases, not just the current one.

2. If you MUST use DoCmd.SetWarnings be sure to put an error handler in that
procedure and make the very first line of the error handler
DoCmd.SetWarnings True
so you turn them back on.

3. DoCmd.SetWarnings is NOT necessary in 99% of the time if you run your
query by using the alternate:
CurrentDb.Execute strSQL, dbFailOnError

I would suggest using this instead of DoCmd.SetWarnings.

Also, there is a fourth consideration here.

Why are you using code to make a table? Does the definition change each
time? Or, if it is the same, you should just delete all records from it and
then append new records to it. This will save you on massive database bloat
that can occur when you keep deleting and remaking tables.

 

[The Merg]

I have a form that the user can select what criteria is needed for a report
that displays a chart. I've found out, unfortunately, that it is basically
impossible to change the RowSource dynamically at runtime for a chart. What
I did instead was create a dummy table for the chart to be based off. Then
when the user selects the criteria on the form, it creates the MakeTable SQL
statement and executes it to populate the table. In the SQL statement, I
rename the fields from the original table to be a standard name in the dummy
table. The RowSource is as follows:

TRANSFORM Count([Topic]) AS [CountOfTopic] SELECT [Demographic] FROM
[Demographic / Topic] GROUP BY [Demographic] PIVOT [Neighborhood];

"Demographic" is either Age ranges, Years Lived at an address, or Gender.

I guess what I could do is just run a SQL statement to delete the current
rows in the table and then use an Append SQL statement to add the new data
to the table. Either way, I'll still get those warning dialogs. I'll also
look at the "CurrentDB.Execute strSQL, dbFailOnError" command as that will
probably give me the same result then without the warning dialogs.

Thanks,
Merg

 

[Gina Whipp]

Is there a reason you couldn't base the chart of a query? Empty a table and
append still creates bloat, especially if this is going to be a repeat
highly used function.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index.htm

I have a form that the user can select what criteria is needed for a report
that displays a chart. I've found out, unfortunately, that it is basically
impossible to change the RowSource dynamically at runtime for a chart. What
I did instead was create a dummy table for the chart to be based off. Then
when the user selects the criteria on the form, it creates the MakeTable
SQL statement and executes it to populate the table. In the SQL statement,
I rename the fields from the original table to be a standard name in the
dummy table. The RowSource is as follows:

TRANSFORM Count([Topic]) AS [CountOfTopic] SELECT [Demographic] FROM
[Demographic / Topic] GROUP BY [Demographic] PIVOT [Neighborhood];

"Demographic" is either Age ranges, Years Lived at an address, or Gender.

I guess what I could do is just run a SQL statement to delete the current
rows in the table and then use an Append SQL statement to add the new data
to the table. Either way, I'll still get those warning dialogs. I'll also
look at the "CurrentDB.Execute strSQL, dbFailOnError" command as that will
probably give me the same result then without the warning dialogs.

Thanks,
Merg

--
Today's problems don't worry me,
I haven't solved yesterday's yet.

A few things you should think on though -

1. DoCmd.SetWarnings is something I would avoid if at all possible as you
can find yourself without warnings if something glitches before you turn
them
back on. AND, it affects ALL of your databases, not just the current
one.

2. If you MUST use DoCmd.SetWarnings be sure to put an error handler in
that
procedure and make the very first line of the error handler
DoCmd.SetWarnings True
so you turn them back on.

3. DoCmd.SetWarnings is NOT necessary in 99% of the time if you run your
query by using the alternate:
CurrentDb.Execute strSQL, dbFailOnError

I would suggest using this instead of DoCmd.SetWarnings.

Also, there is a fourth consideration here.

Why are you using code to make a table? Does the definition change each
time? Or, if it is the same, you should just delete all records from it
and
then append new records to it. This will save you on massive database
bloat
that can occur when you keep deleting and remaking tables.
--
Bob Larson
Free MS Access Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

 

[The Merg]

The issue I had with the query is that the fields being used change
depending on what the user picks as criteria for the chart. Since I can't
change the RowSource of the chart dynamically, I needed a way to have a
static RowSource, but the data underlying the RowSource needed to change
dynamically. My solution was a table in which the data in the table changes.

- Merg

--
Today's problems don't worry me,
I haven't solved yesterday's yet.

Is there a reason you couldn't base the chart of a query? Empty a table
and append still creates bloat, especially if this is going to be a repeat
highly used function.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index.htm

I have a form that the user can select what criteria is needed for a
report that displays a chart. I've found out, unfortunately, that it is
basically impossible to change the RowSource dynamically at runtime for a
chart. What I did instead was create a dummy table for the chart to be
based off. Then when the user selects the criteria on the form, it creates
the MakeTable SQL statement and executes it to populate the table. In the
SQL statement, I rename the fields from the original table to be a
standard name in the dummy table. The RowSource is as follows:

TRANSFORM Count([Topic]) AS [CountOfTopic] SELECT [Demographic] FROM
[Demographic / Topic] GROUP BY [Demographic] PIVOT [Neighborhood];

"Demographic" is either Age ranges, Years Lived at an address, or Gender.

I guess what I could do is just run a SQL statement to delete the current
rows in the table and then use an Append SQL statement to add the new
data to the table. Either way, I'll still get those warning dialogs. I'll
also look at the "CurrentDB.Execute strSQL, dbFailOnError" command as
that will probably give me the same result then without the warning
dialogs.

Thanks,
Merg

--
Today's problems don't worry me,
I haven't solved yesterday's yet.

A few things you should think on though -

1. DoCmd.SetWarnings is something I would avoid if at all possible as
you
can find yourself without warnings if something glitches before you turn
them
back on. AND, it affects ALL of your databases, not just the current
one.

2. If you MUST use DoCmd.SetWarnings be sure to put an error handler in
that
procedure and make the very first line of the error handler
DoCmd.SetWarnings True
so you turn them back on.

3. DoCmd.SetWarnings is NOT necessary in 99% of the time if you run your
query by using the alternate:
CurrentDb.Execute strSQL, dbFailOnError

I would suggest using this instead of DoCmd.SetWarnings.

Also, there is a fourth consideration here.

Why are you using code to make a table? Does the definition change each
time? Or, if it is the same, you should just delete all records from it
and
then append new records to it. This will save you on massive database
bloat
that can occur when you keep deleting and remaking tables.
--
Bob Larson
Free MS Access Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


:

Thanks. Easy enough.

- Merg

--
Today's problems don't worry me,
I haven't solved yesterday's yet.


Try..

DoCmd.SetWarnings False 'Turns them off
DoCmd.RunSQL etc...
DoCmd.SetWarnings True 'Turns them back on

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index.htm

I am using the DoCmd.RunSQL command to execute a MakeTable query. I
get
prompted with the standard "your current table will be deleted" and
"xx
rows will be added to your table" prompts. Is there anyway to have it
automatically accept those options and move on?

Thanks,
Merg

 

[Gina Whipp]

Merg,

I have a form that opens on top of the report that once the user changes the
parameters on the from the report, which is open behind the form is set to
refresh. Have you tried doing it that way?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index.htm

The issue I had with the query is that the fields being used change
depending on what the user picks as criteria for the chart. Since I can't
change the RowSource of the chart dynamically, I needed a way to have a
static RowSource, but the data underlying the RowSource needed to change
dynamically. My solution was a table in which the data in the table
changes.

- Merg

--
Today's problems don't worry me,
I haven't solved yesterday's yet.

Is there a reason you couldn't base the chart of a query? Empty a table
and append still creates bloat, especially if this is going to be a
repeat highly used function.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II

http://www.regina-whipp.com/index.htm

I have a form that the user can select what criteria is needed for a
report that displays a chart. I've found out, unfortunately, that it is
basically impossible to change the RowSource dynamically at runtime for a
chart. What I did instead was create a dummy table for the chart to be
based off. Then when the user selects the criteria on the form, it
creates the MakeTable SQL statement and executes it to populate the
table. In the SQL statement, I rename the fields from the original table
to be a standard name in the dummy table. The RowSource is as follows:

TRANSFORM Count([Topic]) AS [CountOfTopic] SELECT [Demographic] FROM
[Demographic / Topic] GROUP BY [Demographic] PIVOT [Neighborhood];

"Demographic" is either Age ranges, Years Lived at an address, or
Gender.

I guess what I could do is just run a SQL statement to delete the
current rows in the table and then use an Append SQL statement to add
the new data to the table. Either way, I'll still get those warning
dialogs. I'll also look at the "CurrentDB.Execute strSQL, dbFailOnError"
command as that will probably give me the same result then without the
warning dialogs.

Thanks,
Merg

--
Today's problems don't worry me,
I haven't solved yesterday's yet.


A few things you should think on though -

1. DoCmd.SetWarnings is something I would avoid if at all possible as
you
can find yourself without warnings if something glitches before you
turn them
back on. AND, it affects ALL of your databases, not just the current
one.

2. If you MUST use DoCmd.SetWarnings be sure to put an error handler in
that
procedure and make the very first line of the error handler
DoCmd.SetWarnings True
so you turn them back on.

3. DoCmd.SetWarnings is NOT necessary in 99% of the time if you run
your
query by using the alternate:
CurrentDb.Execute strSQL, dbFailOnError

I would suggest using this instead of DoCmd.SetWarnings.

Also, there is a fourth consideration here.

Why are you using code to make a table? Does the definition change
each
time? Or, if it is the same, you should just delete all records from
it and
then append new records to it. This will save you on massive database
bloat
that can occur when you keep deleting and remaking tables.
--
Bob Larson
Free MS Access Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


:

Thanks. Easy enough.

- Merg

--
Today's problems don't worry me,
I haven't solved yesterday's yet.


Try..

DoCmd.SetWarnings False 'Turns them off
DoCmd.RunSQL etc...
DoCmd.SetWarnings True 'Turns them back on

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index.htm

I am using the DoCmd.RunSQL command to execute a MakeTable query. I
get
prompted with the standard "your current table will be deleted" and
"xx
rows will be added to your table" prompts. Is there anyway to have
it
automatically accept those options and move on?

Thanks,
Merg

 

[David W. Fenton]

The issue I had with the query is that the fields being used
change depending on what the user picks as criteria for the chart.
Since I can't change the RowSource of the chart dynamically, I
needed a way to have a static RowSource, but the data underlying
the RowSource needed to change dynamically. My solution was a
table in which the data in the table changes.

You can create a QueryDef and save it on the fly. This will add a
little bit of bloat to your database, but not nearly as much as
deleting either a whole table or deleting all the records in the
table.

I know exactly the problem that you're encountering and a saved
QueryDef written in code was the solution I used. Then you hardwire
your graph to the saved QueryDef.

On the subject of replacements for DoCmd.RunSQL, I offer my SQLRun()
function after my sig. The key thing is that if you're using
..Execute with the dbFailOnError option (as you should), you have to
have an error handler for recovery when the operation fails. Rather
than writing this in every subroutine in which you use
dbFailOnError, you can use the SQLRun() code instead. Indeed, if
you're using DoCmd.RunSQL, you can simply do a search/replace
operation to replace it with SQLRun (which was the intention behind
my writing it).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Public Function SQLRun(strSQL As String) As Long
On Error GoTo errHandler

CurrentDB.Execute strSQL, dbFailOnError
SQLRun = CurrentDB.RecordsAffected

exitRoutine:
Exit Function

errHandler:
MsgBox Err.Number & ": " & Err.Description, vbExclamation, _
"Error in SQLRun()"
Resume exitRoutine
End Function

 

[The Merg]

Making a QueryDef and then just replacing it with one that contains the
criteria that the user selects would work. Then I just set the chart to use
that QueryDef as you stated. It's been a while since I've coded in Access...
How do I take a SQL statement and use that to create a new QueryDef saved in
Access? Basically, what's the equivalent MakeQuery method?

Thanks for the help,
Merg

 

[The Merg]

The reason this won't work for me is that the parameters are not changing on
the report, but the fields I am using themselves are changing. My chart can
be based on Age Ranges vs. Topic 1, but then the next time it might be Years
Lived Ranges vs. Topic 3. Access won't let me change the fields being used
for the X and Y Axis dynamically.

- Merg

--
Today's problems don't worry me,
I haven't solved yesterday's yet.

Merg,

I have a form that opens on top of the report that once the user changes
the parameters on the from the report, which is open behind the form is
set to refresh. Have you tried doing it that way?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index.htm

The issue I had with the query is that the fields being used change
depending on what the user picks as criteria for the chart. Since I can't
change the RowSource of the chart dynamically, I needed a way to have a
static RowSource, but the data underlying the RowSource needed to change
dynamically. My solution was a table in which the data in the table
changes.

- Merg

--
Today's problems don't worry me,
I haven't solved yesterday's yet.

Is there a reason you couldn't base the chart of a query? Empty a table
and append still creates bloat, especially if this is going to be a
repeat highly used function.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II

http://www.regina-whipp.com/index.htm

I have a form that the user can select what criteria is needed for a
report that displays a chart. I've found out, unfortunately, that it is
basically impossible to change the RowSource dynamically at runtime for
a chart. What I did instead was create a dummy table for the chart to be
based off. Then when the user selects the criteria on the form, it
creates the MakeTable SQL statement and executes it to populate the
table. In the SQL statement, I rename the fields from the original table
to be a standard name in the dummy table. The RowSource is as follows:

TRANSFORM Count([Topic]) AS [CountOfTopic] SELECT [Demographic] FROM
[Demographic / Topic] GROUP BY [Demographic] PIVOT [Neighborhood];

"Demographic" is either Age ranges, Years Lived at an address, or
Gender.

I guess what I could do is just run a SQL statement to delete the
current rows in the table and then use an Append SQL statement to add
the new data to the table. Either way, I'll still get those warning
dialogs. I'll also look at the "CurrentDB.Execute strSQL,
dbFailOnError" command as that will probably give me the same result
then without the warning dialogs.

Thanks,
Merg

--
Today's problems don't worry me,
I haven't solved yesterday's yet.


A few things you should think on though -

1. DoCmd.SetWarnings is something I would avoid if at all possible as
you
can find yourself without warnings if something glitches before you
turn them
back on. AND, it affects ALL of your databases, not just the current
one.

2. If you MUST use DoCmd.SetWarnings be sure to put an error handler
in that
procedure and make the very first line of the error handler
DoCmd.SetWarnings True
so you turn them back on.

3. DoCmd.SetWarnings is NOT necessary in 99% of the time if you run
your
query by using the alternate:
CurrentDb.Execute strSQL, dbFailOnError

I would suggest using this instead of DoCmd.SetWarnings.

Also, there is a fourth consideration here.

Why are you using code to make a table? Does the definition change
each
time? Or, if it is the same, you should just delete all records from
it and
then append new records to it. This will save you on massive database
bloat
that can occur when you keep deleting and remaking tables.
--
Bob Larson
Free MS Access Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


:

Thanks. Easy enough.

- Merg

--
Today's problems don't worry me,
I haven't solved yesterday's yet.


Try..

DoCmd.SetWarnings False 'Turns them off
DoCmd.RunSQL etc...
DoCmd.SetWarnings True 'Turns them back on

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index.htm

I am using the DoCmd.RunSQL command to execute a MakeTable query. I
get
prompted with the standard "your current table will be deleted" and
"xx
rows will be added to your table" prompts. Is there anyway to have
it
automatically accept those options and move on?

Thanks,
Merg

 

[David W. Fenton]

It's been a while since I've coded in Access...
How do I take a SQL statement and use that to create a new
QueryDef saved in Access? Basically, what's the equivalent
MakeQuery method?

In the VBE help, search for CreateQueryDef. There's a code example
there.