By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,560 Members | 2,989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,560 IT Pros & Developers. It's quick & easy.

SQL duplication & project maintainability

P: n/a
Hello -

I apologize if this has been covered in the group, I tried querying
on various combinations of "recordset", "2147217904", "maintainability"
but couldn't find anything.

I have a form, MainForm, containing a field, SearchField. I have a
query (MyQuery) with the field as a parameter, e.g.:

SELECT a, b FROM t WHERE b LIKE [Forms]![MainForm]![SearchField]

This query works fine (as long as MainForm is open). I also have a
form based on this query which also works (again as long as MainForm is
open). Finally, I have some code, in MyProc(), where I would like to
run this query to a recordset, e.g.:

Dim Conn As ADODB.Connection
Dim RS As ADODB.Recordset
Dim SQL As String
Set Conn = CurrentProject.Connection
SQL = "SELECT * FROM MyQuery" ' <-- edit here; see below
Set RS = Conn.Execute (SQL)

Sadly, the last line fails with the error:

Run-time error '-2147217904 (80040e10)':
No value given for one or more required parameters.

This happens regardless of whether MainForm is open or not. I found
this explained in news:a8************@ID-139850.news.dfncis.de :
(Turtle MacDermott?) "if you open a recordset based on a parameterized
query, no effort is made to resolve that parameter".

Now, that's easy enough to work around, as I can just copy the SQL from
MyQuery into the line marked "edit here". But that's duplicating code,
and then if I change MyQuery I have to remember to change MyProc() too.

Related questions:

1. Is there a way to extract the SQL from a Query, perhaps as a
property ? Then for "edit here" I could have something like:

SQL = MyQuery.GetSQL

2. Is there a way to put comments in the SQL for a query ? Then I
could have a comment at the beginning of MyQuery:

-- don't forget to update this query in MyProc()
I hope the intent here is clear: To improve the maintainability of the
project, by having (SQL) code that does one thing in one place only.
I will be grateful for any ideas. Thanks, - Albert

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
I don't do ADO unless coerced, but there is an SQL property for a
DAO.QueryDef from which you can obtain the SQL. And, there's no prohibition
against using ADO and DAO in the same MDB.

In my experience, it is most often "safer" to use a Query for only one
purpose and in one place, so I would likely just put the SQL string right
there in the code.

Larry Linson
Microsoft Access MVP
"Albert Lingelbach" <al******@yahoo.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...
Hello -

I apologize if this has been covered in the group, I tried querying
on various combinations of "recordset", "2147217904", "maintainability"
but couldn't find anything.

I have a form, MainForm, containing a field, SearchField. I have a
query (MyQuery) with the field as a parameter, e.g.:

SELECT a, b FROM t WHERE b LIKE [Forms]![MainForm]![SearchField]

This query works fine (as long as MainForm is open). I also have a
form based on this query which also works (again as long as MainForm is
open). Finally, I have some code, in MyProc(), where I would like to
run this query to a recordset, e.g.:

Dim Conn As ADODB.Connection
Dim RS As ADODB.Recordset
Dim SQL As String
Set Conn = CurrentProject.Connection
SQL = "SELECT * FROM MyQuery" ' <-- edit here; see below
Set RS = Conn.Execute (SQL)

Sadly, the last line fails with the error:

Run-time error '-2147217904 (80040e10)':
No value given for one or more required parameters.

This happens regardless of whether MainForm is open or not. I found
this explained in news:a8************@ID-139850.news.dfncis.de :
(Turtle MacDermott?) "if you open a recordset based on a parameterized
query, no effort is made to resolve that parameter".

Now, that's easy enough to work around, as I can just copy the SQL from
MyQuery into the line marked "edit here". But that's duplicating code,
and then if I change MyQuery I have to remember to change MyProc() too.

Related questions:

1. Is there a way to extract the SQL from a Query, perhaps as a
property ? Then for "edit here" I could have something like:

SQL = MyQuery.GetSQL

2. Is there a way to put comments in the SQL for a query ? Then I
could have a comment at the beginning of MyQuery:

-- don't forget to update this query in MyProc()
I hope the intent here is clear: To improve the maintainability of the
project, by having (SQL) code that does one thing in one place only.
I will be grateful for any ideas. Thanks, - Albert

Nov 13 '05 #2

P: n/a
I agreee with Larry, and would rewrite the code as follows:

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set conn = CurrentProject.Connection
Set rs = New ADODB.Recordset

rs.Source = "SELECT a, b FROM t WHERE b LIKE " &
Forms("MainForm").SearchField
rs.Open , cnxn, adOpenForwardOnly, adLockReadOnly, adCmdText

[Above opens readonly recordset, as does your code]

If you want to use the stored query, you will need to create a ADODB>Command
object and parameters.

IMHO, you're not gaining anything significant by using a stored query unless
it is much more complex. Further, you run the risk that you will change the
stored query, [overlooking the fact that you are also ] changing the VBA
code. I know that it can be done, and should be avoided :(
Darryl Kerkeslager

"Albert Lingelbach" <al******@yahoo.com> wrote:
I have a form, MainForm, containing a field, SearchField. I have a
query (MyQuery) with the field as a parameter, e.g.:

SELECT a, b FROM t WHERE b LIKE [Forms]![MainForm]![SearchField]

This query works fine (as long as MainForm is open). I also have a
form based on this query which also works (again as long as MainForm is
open). Finally, I have some code, in MyProc(), where I would like to
run this query to a recordset, e.g.:

Dim Conn As ADODB.Connection
Dim RS As ADODB.Recordset
Dim SQL As String
Set Conn = CurrentProject.Connection
SQL = "SELECT * FROM MyQuery" ' <-- edit here; see below
Set RS = Conn.Execute (SQL)

Sadly, the last line fails with the error:

Run-time error '-2147217904 (80040e10)':
No value given for one or more required parameters.

This happens regardless of whether MainForm is open or not. I found
this explained in news:a8************@ID-139850.news.dfncis.de :
(Turtle MacDermott?) "if you open a recordset based on a parameterized
query, no effort is made to resolve that parameter".

Now, that's easy enough to work around, as I can just copy the SQL from
MyQuery into the line marked "edit here". But that's duplicating code,
and then if I change MyQuery I have to remember to change MyProc() too.

Related questions:

1. Is there a way to extract the SQL from a Query, perhaps as a
property ? Then for "edit here" I could have something like:

SQL = MyQuery.GetSQL

2. Is there a way to put comments in the SQL for a query ? Then I
could have a comment at the beginning of MyQuery:

-- don't forget to update this query in MyProc()

Nov 13 '05 #3

P: n/a
On 22 Dec 2004 14:27:52 -0800, Albert Lingelbach wrote:
Set Conn = CurrentProject.Connection
SQL = "SELECT * FROM MyQuery" ' <-- edit here; see below
Set RS = Conn.Execute (SQL)

Sadly, the last line fails with the error:

Run-time error '-2147217904 (80040e10)':
No value given for one or more required parameters.
The traditional way to do this is with a command object:

Dim Conn as ADODB.Connection
Dim RS as ADODB.Recordset
Dim Cmd as ADODB.Command
Dim Parm as ADODB.Parameter
Dim SQL As String
Set Conn = CurrentProject.Connection
SQL = "SELECT a,b FROM t WHERE b LIKE ?"
Set Cmd = Conn.CreateCommand(SQL)
Set Parm = Cmd.CreateParameter("@Search",adVarWChar,adParamIn put,2048, _
[Forms]![MainForm]![SearchField] )
Cmd.Parameters.Add(Parm)
Set RS = Cmd.Execute()

This happens regardless of whether MainForm is open or not. I found
this explained in news:a8************@ID-139850.news.dfncis.de :
(Turtle MacDermott?) "if you open a recordset based on a parameterized
query, no effort is made to resolve that parameter".

Now, that's easy enough to work around, as I can just copy the SQL from
MyQuery into the line marked "edit here". But that's duplicating code,
and then if I change MyQuery I have to remember to change MyProc() too.

Related questions:

1. Is there a way to extract the SQL from a Query, perhaps as a
property ? Then for "edit here" I could have something like:

SQL = MyQuery.GetSQL


This can be done with DAO (assuming we're talking MDB here, not ADP)

Dim QDF as DAO.Querydef
Set QDF = Currentdb.Querydefs("MyQuery")
SQL = QDF.SQL
Nov 13 '05 #4

P: n/a
Hello -

Thank you all very much for your replies & ideas. Happy holidays !
- Albert

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.