codedb.execute

[Guest]

Ok... UNCLE.... I am not finding my answer in MSAccess help (go figure) and i
have searched google and here w/o any luck. So i'll just post my question...

I have an Access module/sub where i have basically the following...


CodeDb.Execute "MyQuery"

MyQuery is a create table query and I am getting the following error...

Too few parameters. Expected 1.

My guess is that the query is wanting an overwrite option but the only
option value that i have been able to find is dbSeeChanges.


QUESTION... what are the option values for CodeDb.Execute?

Thank you for your time

 

[Allen Browne]

MyQuery contains a parameter that must be supplied *before* you execute the
query.

It could be something like:
[Forms].[Form1].[Text0]

If so, you could execute the query like this:
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("MyQuery")
qdf.Parameters("[Forms].[Form1].[Text0]") = [Forms].[Form1].[Text0]
qdf.Execute, dbFailOnError
Set qdf = Nothing
Set db = Nothing

 

[Guest]

Hi dag,

Just a quick one before I head home, but that message makes me very
suspicious that there's a problem with your query "MyQuery" and not
CodeDb.Execute. Have another look at your query first and see if there's any
problems there - could save you barking up the wrong lamppost...

Cheers,

Stuart

 

[Guest]

Allen,
Your right... I do have a parm in there (I guess i didn't tell ya that
because i don't think that is the cleanest way to write a query). And for
whatever reason passing the parm as you have described does work. Thank
you...

However, this has raised a new error.... The query is a create table query
so now I'm getting...

Table 'tmpMyTable' already exists.

and the query fails. Any suggestions... I suppose i could drop the table
before i run the query.

MyQuery contains a parameter that must be supplied *before* you execute the
query.

It could be something like:
[Forms].[Form1].[Text0]

If so, you could execute the query like this:
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("MyQuery")
qdf.Parameters("[Forms].[Form1].[Text0]") = [Forms].[Form1].[Text0]
qdf.Execute, dbFailOnError
Set qdf = Nothing
Set db = Nothing

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ok... UNCLE.... I am not finding my answer in MSAccess help (go figure)
and i
have searched google and here w/o any luck. So i'll just post my
question...

I have an Access module/sub where i have basically the following...


CodeDb.Execute "MyQuery"

MyQuery is a create table query and I am getting the following error...

Too few parameters. Expected 1.

My guess is that the query is wanting an overwrite option but the only
option value that i have been able to find is dbSeeChanges.


QUESTION... what are the option values for CodeDb.Execute?

Thank you for your time

 

[Guest]

First, let me say I have a personal prejudice against make table queries for
a number of reasons I wont go in to. I will say they have value in some
cases, but not usually.

If the table already exists, it will have to be deleted before you can make
it. If it already exists, are there any relationships establised or any
field sizes defined? The reason I ask is that it is more efficient to use an
append query. Make table queries use the default field sizes for text fields
and usually wastes space.

So, I would suggest you change your query to an append query, and make the
following changes to the code:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb()
db.Execute("DELETE * FROM tmpMyTable;"), dbFailOnError
Set qdf = db.QueryDefs("MyQuery")
qdf.Parameters("[Forms].[Form1].[Text0]") = [Forms].[Form1].[Text0]
qdf.Execute, dbFailOnError
Set qdf = Nothing
Set db = Nothing

It that doesn't suit you, use the DeleteObject method to get rid of the old
table before you make a new on.

Allen,
Your right... I do have a parm in there (I guess i didn't tell ya that
because i don't think that is the cleanest way to write a query). And for
whatever reason passing the parm as you have described does work. Thank
you...

However, this has raised a new error.... The query is a create table query
so now I'm getting...

Table 'tmpMyTable' already exists.

and the query fails. Any suggestions... I suppose i could drop the table
before i run the query.

MyQuery contains a parameter that must be supplied *before* you execute the
query.

It could be something like:
[Forms].[Form1].[Text0]

If so, you could execute the query like this:
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("MyQuery")
qdf.Parameters("[Forms].[Form1].[Text0]") = [Forms].[Form1].[Text0]
qdf.Execute, dbFailOnError
Set qdf = Nothing
Set db = Nothing

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ok... UNCLE.... I am not finding my answer in MSAccess help (go figure)
and i
have searched google and here w/o any luck. So i'll just post my
question...

I have an Access module/sub where i have basically the following...


CodeDb.Execute "MyQuery"

MyQuery is a create table query and I am getting the following error...

Too few parameters. Expected 1.

My guess is that the query is wanting an overwrite option but the only
option value that i have been able to find is dbSeeChanges.


QUESTION... what are the option values for CodeDb.Execute?

Thank you for your time

 

[Douglas J Steele]

Realistically, you've answered your own question.

If you're running a Make Table query, the table cannot already exist.

Your options are to delete the table first, or change your query to an
Append query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Allen,
Your right... I do have a parm in there (I guess i didn't tell ya that
because i don't think that is the cleanest way to write a query). And for
whatever reason passing the parm as you have described does work. Thank
you...

However, this has raised a new error.... The query is a create table query
so now I'm getting...

Table 'tmpMyTable' already exists.

and the query fails. Any suggestions... I suppose i could drop the table
before i run the query.

MyQuery contains a parameter that must be supplied *before* you execute the
query.

It could be something like:
[Forms].[Form1].[Text0]

If so, you could execute the query like this:
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("MyQuery")
qdf.Parameters("[Forms].[Form1].[Text0]") = [Forms].[Form1].[Text0]
qdf.Execute, dbFailOnError
Set qdf = Nothing
Set db = Nothing

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ok... UNCLE.... I am not finding my answer in MSAccess help (go figure)
and i
have searched google and here w/o any luck. So i'll just post my
question...

I have an Access module/sub where i have basically the following...


CodeDb.Execute "MyQuery"

MyQuery is a create table query and I am getting the following error...

Too few parameters. Expected 1.

My guess is that the query is wanting an overwrite option but the only
option value that i have been able to find is dbSeeChanges.


QUESTION... what are the option values for CodeDb.Execute?

Thank you for your time

 

[Guest]

Anytime I post a question I try to keep it simple and to the point. Maybe I
should have had a little more info to begin with.

Problem:
Send a very small strictly formatted CSV file via email.

Simple solution:
Create QueryX to get the necessary data
DoCmd.SendObject …QueryX…

Problem:
..SendObject does not support CSV format

Solution:
DoCmd.TransferText …QueryX…FileX
Create Email ObjectX
ObjectX.Attach FileX

Problem:
QueryX and data for query exist in AddinDB.mda and TransferText command is
looking in PrimaryDB.mde for QueryX

Solution:
Change QueryX to a MakeTable query tmpTableX
CodeDb.QueryDefs(QueryX)
Execute query
DoCmd.TransferText …tmpTableX…FileX
Create Email ObjectX
ObjectX.Attach FileX

This is where I’m at and the problem is that tmpTableX might already exist.
I don’t care about the tmpTableX for anything else. I suppose my solution to
this is to drop the table after the file is attached and sent.

Thanks everyone for your input and I may have the solution but maybe someone
may have a cleaner idea then the path that I am in now.