How to use Function to pass query parameter?

[deko]

I need a way to create a table with a programmatically defined name. I have a
Make Table query that will create the table with the name that I put in the
query, but I don't know how to (or if I can) use a function to pass the table
name into the query.

Here is the basic form of the Mke Tble query:

SELECT TxDate, Amount, Tx_ID, TxAcctName, INTO [table name here] FROM
tblTxJournal;

This does not seem to work:

SELECT TxDate, Amount, Tx_ID, TxAcctName, INTO (QryPrmName) FROM
tblTxJournal;

Public Function QryPrmName() As String
Dim strTxAcct As String
Dim strQtr As String
Dim strYr As String
strTxAcct = Forms!frm2!cbx2TxAcct.Column(1)
strQtr = Forms!frm2!cbx2Quarter.Column(1)
strYr = Forms!frm2!cbx2Year
QryPrmName = "Transactions_For_" & strTxAcct & "_" & strQtr & "_" & strYr
End Function

Is it possible to do this?

Thanks in advance.

[Steve Jorgensen]

To insert rows into an existing table, you must use INSERT INTO, not SELECT
.... INTO. If you want to specify the target fields for your insert, you list
them in parentheses right after INSERT INTO <table-name> in the same order
they will appear in the output from the SELECT.

Example:
INSERT INTO tblFoo (FooName, FooDescription) SELECT BarName, BarDescription
FROM tblBar

If you want to create the table with the correct field names directy rather
than creating it first, but still have control over the field names, you just
need to alias the fields in your select list. Of course, you'll have to alter
the table design afterward if you need an Autonumber, indexes, aetc.

Example:
SELECT BarName As FooName, BarDescription As FooDescription
INTO tblFoo FROM tblBar

On Sat, 31 Jan 2004 21:57:24 GMT, "deko" <dj****@hotmail.com> wrote:

I need a way to create a table with a programmatically defined name. I have a
Make Table query that will create the table with the name that I put in the
query, but I don't know how to (or if I can) use a function to pass the table
name into the query.

Here is the basic form of the Mke Tble query:

SELECT TxDate, Amount, Tx_ID, TxAcctName, INTO [table name here] FROM
tblTxJournal;

This does not seem to work:

SELECT TxDate, Amount, Tx_ID, TxAcctName, INTO (QryPrmName) FROM
tblTxJournal;

Public Function QryPrmName() As String
Dim strTxAcct As String
Dim strQtr As String
Dim strYr As String
strTxAcct = Forms!frm2!cbx2TxAcct.Column(1)
strQtr = Forms!frm2!cbx2Quarter.Column(1)
strYr = Forms!frm2!cbx2Year
QryPrmName = "Transactions_For_" & strTxAcct & "_" & strQtr & "_" & strYr
End Function

Is it possible to do this?

Thanks in advance.

[deko]

Hi and thanks for the reply.

well, perhaps I could just rename the table after creating it:

For Each tdf In CurrentDb.TableDefs
If tdf.Name = strOldName Then
'CurrentDb.Execute "DROP TABLE " & strNewName
tdf.Name = strNewName
End If
Next
CurrentDb.TableDefs.Refresh

the problem is dealing with table if it already exists... I'm also having
trouble with:

DoCmd.OutputTo acOutputTable, strNewName

I can get it to work but getting ugly errors...

The other option, as you suggested, is to create the table first with the right
name and insert into it. I was thinking I could use DDL for this:

DoCmd.RunSql ("CREATE TABLE strNewName (TxDate Date, Amount Currency, TxAcctName
Text, Tx_ID AutoIncrement Constraint PrimaryKey PRIMARY KEY);

or something like that...

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:ls********************************@4ax.com...

To insert rows into an existing table, you must use INSERT INTO, not SELECT
... INTO. If you want to specify the target fields for your insert, you list
them in parentheses right after INSERT INTO <table-name> in the same order
they will appear in the output from the SELECT.

Example:
INSERT INTO tblFoo (FooName, FooDescription) SELECT BarName, BarDescription
FROM tblBar

If you want to create the table with the correct field names directy rather
than creating it first, but still have control over the field names, you just
need to alias the fields in your select list. Of course, you'll have to alter
the table design afterward if you need an Autonumber, indexes, aetc.

Example:
SELECT BarName As FooName, BarDescription As FooDescription
INTO tblFoo FROM tblBar

On Sat, 31 Jan 2004 21:57:24 GMT, "deko" <dj****@hotmail.com> wrote:

I need a way to create a table with a programmatically defined name. I have aMake Table query that will create the table with the name that I put in the
query, but I don't know how to (or if I can) use a function to pass the table
name into the query.

Here is the basic form of the Mke Tble query:

SELECT TxDate, Amount, Tx_ID, TxAcctName, INTO [table name here] FROM
tblTxJournal;

This does not seem to work:

SELECT TxDate, Amount, Tx_ID, TxAcctName, INTO (QryPrmName) FROM
tblTxJournal;

Public Function QryPrmName() As String
Dim strTxAcct As String
Dim strQtr As String
Dim strYr As String
strTxAcct = Forms!frm2!cbx2TxAcct.Column(1)
strQtr = Forms!frm2!cbx2Quarter.Column(1)
strYr = Forms!frm2!cbx2Year
QryPrmName = "Transactions_For_" & strTxAcct & "_" & strQtr & "_" & strYr
End Function

Is it possible to do this?

Thanks in advance.

[Mike Storr]

On Sat, 31 Jan 2004 21:57:24 GMT, deko wrote:

I need a way to create a table with a programmatically defined name. I have a
Make Table query that will create the table with the name that I put in the
query, but I don't know how to (or if I can) use a function to pass the table
name into the query.

Here is the basic form of the Mke Tble query:

SELECT TxDate, Amount, Tx_ID, TxAcctName, INTO [table name here] FROM
tblTxJournal;

This does not seem to work:

SELECT TxDate, Amount, Tx_ID, TxAcctName, INTO (QryPrmName) FROM
tblTxJournal;

Public Function QryPrmName() As String
Dim strTxAcct As String
Dim strQtr As String
Dim strYr As String
strTxAcct = Forms!frm2!cbx2TxAcct.Column(1)
strQtr = Forms!frm2!cbx2Quarter.Column(1)
strYr = Forms!frm2!cbx2Year
QryPrmName = "Transactions_For_" & strTxAcct & "_" & strQtr & "_" & strYr
End Function

Is it possible to do this?

Thanks in advance.

If a QueryDef object is not required, then doing the following would
simplify things (modify the error handle to suit your needs)...

Public Function QryPrmName() As String
On Error Goto Error_Handler
Dim strTxAcct As String
Dim strQtr As String
Dim strYr As String
Dim strSQL As String
Dim db As Database
strTxAcct = Forms!frm2!cbx2TxAcct.Column(1)
strQtr = Forms!frm2!cbx2Quarter.Column(1)
strYr = Forms!frm2!cbx2Year
strSQL = "SELECT TxDate, Amount, Tx_ID, TxAcctName, INTO "
strSQL = strSQL & "Transactions_For_" & strTxAcct & "_" & strQtr & "_"
strSQL = strSQL & "strYr" FROM tblTxJournal;
db.Execute sqlStr, dbFailOnError

Exit_Here:
Exit Function

Error_Handler:
MsgBox Err.Number & " - " & Err.Description
resume Exit_Here

End Function
--
Mike Storr
veraccess.com

[Steve Jorgensen]

OK, is it just that you need to know how to get the table name into the SQL
Statement? Just use the .Execute method of the database object, and insert
the name into the SQL text.

Dim strSQL As String
Dim dbs As DAO.Database

strSQL = "SELECT ... INTO [" & <mytablename> & "] ..."

Set dbs = CurrentDB
dbs.Execute strSQL, dbFailOnError
Set dbs = Nothing

On Sat, 31 Jan 2004 21:57:24 GMT, "deko" <dj****@hotmail.com> wrote:

I need a way to create a table with a programmatically defined name. I have a
Make Table query that will create the table with the name that I put in the
query, but I don't know how to (or if I can) use a function to pass the table
name into the query.

Here is the basic form of the Mke Tble query:

SELECT TxDate, Amount, Tx_ID, TxAcctName, INTO [table name here] FROM
tblTxJournal;

This does not seem to work:

SELECT TxDate, Amount, Tx_ID, TxAcctName, INTO (QryPrmName) FROM
tblTxJournal;

Public Function QryPrmName() As String
Dim strTxAcct As String
Dim strQtr As String
Dim strYr As String
strTxAcct = Forms!frm2!cbx2TxAcct.Column(1)
strQtr = Forms!frm2!cbx2Quarter.Column(1)
strYr = Forms!frm2!cbx2Year
QryPrmName = "Transactions_For_" & strTxAcct & "_" & strQtr & "_" & strYr
End Function

Is it possible to do this?

Thanks in advance.

[deko]

> Just use the .Execute method of the database object, and insert

the name into the SQL text.

I think db.Execute is the way to go, but...
I'm getting this error:

"Error Number 3061: Too Few Parameters..."

Is this one of those cases where I have to use a Function to get the query
parameter?
I have stuff like this in the Where clause:

WHERE (((tblTxJournal.TxType_ID = Forms!frm2!cbx2TxType Or Forms!frm2!cbx2TxType
= 0) And (tblTxJournal.TxAcct_ID = Forms!frm2!cbx2TxAcct Or
Forms!frm2!cbx2TxAcct = 0)) Or (Forms!frm2!cbx2Year = "<All>")) ...

It seems to work fine in a stored query, but barfs with db.Execute

[deko]

DoCmd.RunSql seems to be working - apparently db.Execute cannot resolve the
references

Thanks for the help!

[Steve Jorgensen]

That's right. I didn't realize your query was trying to use Access object
references as parameters. To do this using DAO, you have to populate the
references from code which means you have to use a QueryDef object.

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
dim strSQL As String

strSQL = "... WHERE Abc=prmAbc ..."

Set dbs = CurrentDB
Set qdf = dbs.CreateQuerydef("", strSQL)
qdf.Parameters("prmAbc") = Forms!<form-name>!<control-name>
qdf.Execute dbFailOnError
Set qdf = Nothing
Set dbs = Nothing

I recommend doing this, not RunSQL because direct DAO is more efficient, and
has better error handling.

On Sun, 01 Feb 2004 02:12:54 GMT, "deko" <dj****@hotmail.com> wrote:

DoCmd.RunSql seems to be working - apparently db.Execute cannot resolve the
references

Thanks for the help!

[deko]

> I recommend doing this, not RunSQL because direct DAO is more efficient, and

has better error handling.

A QueryDef object may be the best solution for a situation like this, but RunSql
seems to be working, too...

I have to join 4 tables and get parameters from 4 different combo boxes to get
the recordset I need. The objective is to narrow a datasheet of transactions by
type, account, date, etc. and export the filtered records to a file. At this
point I'm setting up a CreateTable Sql string with VBA, running it with RunSql,
then dumping the table contents into a file with OutputTo, then dropping the
table with db.Execute.

The reason the table name is important is because OutputTo, when used with
minimal arguments, uses the table name as the default file and header name. So
I take stuff like AcctNumber, Type, Date, CustID, etc., format it into a string
and use that as the table name. This makes the file self-describing. The nice
thing is that OutputTo gives you a dialog that lets you choose the type of file
you want to export to - Excel, Html, Rtf, XML, etc. - when you leave off the
arguments.

[Steve Jorgensen]

On Sun, 01 Feb 2004 06:16:14 GMT, "deko" <dj****@hotmail.com> wrote:

I recommend doing this, not RunSQL because direct DAO is more efficient, and
has better error handling.
A QueryDef object may be the best solution for a situation like this, but RunSql
seems to be working, too...

Yes, RunSQL works, but expetiance shows that it has some problems, and I
suggest you start learning to use QueryDef instead. For one thing, RunSQL's
behavior is dependent upon the Warnings setting. If Warnings are on, the user
will be asked to confirm or cancel if there is a problem such as a primary key
violation. If Warnings are off, any such problems will be ignored, all rows
with no problems will be inserted, and the user will enver know this happened.
if ever want your code to be able to detect and report this kind of error
itself, you'll simply have to use .Execute.
I have to join 4 tables and get parameters from 4 different combo boxes to get
the recordset I need. The objective is to narrow a datasheet of transactions by
type, account, date, etc. and export the filtered records to a file. At this
point I'm setting up a CreateTable Sql string with VBA, running it with RunSql,
then dumping the table contents into a file with OutputTo, then dropping the
table with db.Execute.

The reason the table name is important is because OutputTo, when used with
minimal arguments, uses the table name as the default file and header name. So
I take stuff like AcctNumber, Type, Date, CustID, etc., format it into a string
and use that as the table name. This makes the file self-describing. The nice
thing is that OutputTo gives you a dialog that lets you choose the type of file
you want to export to - Excel, Html, Rtf, XML, etc. - when you leave off the
arguments.

Well, it's the name of the table or the query if you export a query. You
could create a named querydef, set the SQL to SELECT * FROM <table-name>, then
export the querydef. That's much more efficient than copying all the data to
a new table, and is actually a bit simpler to code.

[deko]

> Well, it's the name of the table or the query if you export a query. You

could create a named querydef, set the SQL to SELECT * FROM <table-name>, then
export the querydef. That's much more efficient than copying all the data to
a new table, and is actually a bit simpler to code.

Okay, how about this:

Private Sub cmdExportTx_Click()
Dim strTitle As String
strTitle = FunctionTitle 'puts a string together
Call TxNew(strTitle)
DoCmd.OutputTo acOutputQuery, strTitle, , , True
DoCmd.DeleteObject acQuery, strTitle
End Sub

Create query here:

Private Sub TxNew(strTitle As String)
Dim strSql As String
Dim qdf As QueryDef
Dim db As DAO.Database
strSql = "SELECT ... QryPrm("Form", "Control") ... "
Set db = CurrentDb
Set qdf = db.CreateQueryDef(strTitle, strSql)
Set qdf = Nothing
Set db = Nothing
End Sub

Handle query parameters here:

Public Function QryPrm(ByVal strFrm _
As String, ByVal strCtl As String) As Variant
QryPrm = Forms(strFrm).Controls(strCtl)
End Function

No more table... but still need to use DoCmd to delete the query. Still, I
agree this is better...

[Steve Jorgensen]

On Sun, 01 Feb 2004 22:53:54 GMT, "deko" <dj****@hotmail.com> wrote:

Well, it's the name of the table or the query if you export a query. You
could create a named querydef, set the SQL to SELECT * FROM <table-name>, then
export the querydef. That's much more efficient than copying all the data to
a new table, and is actually a bit simpler to code.

Okay, how about this:

Private Sub cmdExportTx_Click()
Dim strTitle As String
strTitle = FunctionTitle 'puts a string together
Call TxNew(strTitle)
DoCmd.OutputTo acOutputQuery, strTitle, , , True
DoCmd.DeleteObject acQuery, strTitle
End Sub

Create query here:

Private Sub TxNew(strTitle As String)
Dim strSql As String
Dim qdf As QueryDef
Dim db As DAO.Database
strSql = "SELECT ... QryPrm("Form", "Control") ... "
Set db = CurrentDb
Set qdf = db.CreateQueryDef(strTitle, strSql)
Set qdf = Nothing
Set db = Nothing
End Sub

Handle query parameters here:

Public Function QryPrm(ByVal strFrm _
As String, ByVal strCtl As String) As Variant
QryPrm = Forms(strFrm).Controls(strCtl)
End Function

No more table... but still need to use DoCmd to delete the query. Still, I
agree this is better...

Yup - this looks pretty good. Of course (even though I suggested this) it's
still a kludge. The best possible way would be to use the Windows API to open
the file selector, supply the default name yourself to the API call, then use
the file name the user selected in the OutputTo. What you have now is decent,
though.

[Pieter Linden]

why not just leave the temporary query there and just assign a new SQL
statement to it?

[deko]

> why not just leave the temporary query there and just assign a new SQL

statement to it?

How do I do that?

[deko]

> Yup - this looks pretty good. Of course (even though I suggested this) it's

still a kludge. The best possible way would be to use the Windows API to open
the file selector, supply the default name yourself to the API call, then use
the file name the user selected in the OutputTo. What you have now is decent,
though.

appears to work great. only problem is now I want to recode other queries to
work like this... entering infinite loop of rewrite :-P

Thanks for the help.

[Steve Jorgensen]

On Mon, 02 Feb 2004 04:52:54 GMT, "deko" <dj****@hotmail.com> wrote:

Yup - this looks pretty good. Of course (even though I suggested this) it's
still a kludge. The best possible way would be to use the Windows API to open
the file selector, supply the default name yourself to the API call, then use
the file name the user selected in the OutputTo. What you have now is decent,
though.

appears to work great. only problem is now I want to recode other queries to
work like this... entering infinite loop of rewrite :-P

Thanks for the help.

I'm not sure what you were saying with that last paragraph. It sounds like
you might have a case here where you should refactor some things so that
if/when you change your approach, you only have to change code one place.
Then you can use the named query approach for now, since you know it's
working, and improve the code later if the need arises by changing just one or
2 centralized functions.

The earlier you can identify and remove duplication, the more time it will
save you down the road.