Permanent Reference using VBE

References in Access and .mdb files have always been painful for me -
just when I thought I was OK in an .mdb, I have a .adp I'm working on
and I need to use Set db as CurrentDb - Constant "object variable or
with block variable not set" error almost killed me and VB Help says

"If you want to use the CurrentDb method in an Access project (.adp)
you must set a permanent reference to the DAO 3.6 Object library in
the Microsoft Visual Basic Editor."

How do I set a "Permanent" reference? As opposed to another kind?

TIA

re: Permanent Reference using VBE

On Thu, 15 Jan 2004 04:21:46 GMT, CFW <chris@thewilkfamily.com> wrote:

That's probably not MSFT's finest work. There is no other kind (if you
don't consider late binding).

First off you have to realize that ADPs are about using ADO against a
SQL Server back-end. Doing anything outside of this mold is an
advanced topic. The advice to drag DAO into this is somewhat off base
for 95% of the ADP applications.

Knowing you're now in an ADO environment immediately breaks some of
the DAO code you may be more comfortable with. No more CurrentDB, no
more (DAO.)Recordset. Welcome Connection object, ADODB.Recordset
object, etc.

-Tom.

[color=blue]
>References in Access and .mdb files have always been painful for me -
>just when I thought I was OK in an .mdb, I have a .adp I'm working on
>and I need to use Set db as CurrentDb - Constant "object variable or
>with block variable not set" error almost killed me and VB Help says
>
>"If you want to use the CurrentDb method in an Access project (.adp)
>you must set a permanent reference to the DAO 3.6 Object library in
>the Microsoft Visual Basic Editor."
>
>How do I set a "Permanent" reference? As opposed to another kind?
>
>TIA[/color]

re: Permanent Reference using VBE

Thanks for the response. That's exactly what I resorted to "Set New
ADODB.Recordset as Recordset" . . . etc. Can I ask a follow-up . . .
this is new to me - using the "Builder . . . " in a .mdb database
within the criteria cell to create a query I could do in my sleep.
With a .adp project, there is no "Builder . . . " and I can't seem to
get the syntax to set the criteria for a column in a "view" to refer
to a control on an open form. = Forms!frmSomeForm!cboSomeComboBox
used to work great! What am I missing? TIA!

On Wed, 14 Jan 2004 22:37:01 -0700, Tom van Stiphout
<tom7744@no.spam.cox.net> wrote:
[color=blue]
>On Thu, 15 Jan 2004 04:21:46 GMT, CFW <chris@thewilkfamily.com> wrote:
>
>That's probably not MSFT's finest work. There is no other kind (if you
>don't consider late binding).
>
>First off you have to realize that ADPs are about using ADO against a
>SQL Server back-end. Doing anything outside of this mold is an
>advanced topic. The advice to drag DAO into this is somewhat off base
>for 95% of the ADP applications.
>
>Knowing you're now in an ADO environment immediately breaks some of
>the DAO code you may be more comfortable with. No more CurrentDB, no
>more (DAO.)Recordset. Welcome Connection object, ADODB.Recordset
>object, etc.
>
>-Tom.
>
>[color=green]
>>References in Access and .mdb files have always been painful for me -
>>just when I thought I was OK in an .mdb, I have a .adp I'm working on
>>and I need to use Set db as CurrentDb - Constant "object variable or
>>with block variable not set" error almost killed me and VB Help says
>>
>>"If you want to use the CurrentDb method in an Access project (.adp)
>>you must set a permanent reference to the DAO 3.6 Object library in
>>the Microsoft Visual Basic Editor."
>>
>>How do I set a "Permanent" reference? As opposed to another kind?
>>
>>TIA[/color][/color]

re: Permanent Reference using VBE

On Thu, 15 Jan 2004 12:52:16 GMT, CFW <chris@thewilkfamily.com> wrote:

Still works the same way. In the NorthwindCS sample app, I ran the
Employee, and then added a textbox to the Orders form with a
ControlSource of:
=[Forms]![Employees]![FirstName]
(Access provided the bracketing)
And it correctly pulled up the first name of the employee.

-Tom.


[color=blue]
>Thanks for the response. That's exactly what I resorted to "Set New
>ADODB.Recordset as Recordset" . . . etc. Can I ask a follow-up . . .
>this is new to me - using the "Builder . . . " in a .mdb database
>within the criteria cell to create a query I could do in my sleep.
>With a .adp project, there is no "Builder . . . " and I can't seem to
>get the syntax to set the criteria for a column in a "view" to refer
>to a control on an open form. = Forms!frmSomeForm!cboSomeComboBox
>used to work great! What am I missing? TIA!
>
>On Wed, 14 Jan 2004 22:37:01 -0700, Tom van Stiphout
><tom7744@no.spam.cox.net> wrote:
>[color=green]
>>On Thu, 15 Jan 2004 04:21:46 GMT, CFW <chris@thewilkfamily.com> wrote:
>>
>>That's probably not MSFT's finest work. There is no other kind (if you
>>don't consider late binding).
>>
>>First off you have to realize that ADPs are about using ADO against a
>>SQL Server back-end. Doing anything outside of this mold is an
>>advanced topic. The advice to drag DAO into this is somewhat off base
>>for 95% of the ADP applications.
>>
>>Knowing you're now in an ADO environment immediately breaks some of
>>the DAO code you may be more comfortable with. No more CurrentDB, no
>>more (DAO.)Recordset. Welcome Connection object, ADODB.Recordset
>>object, etc.
>>
>>-Tom.
>>
>>[color=darkred]
>>>References in Access and .mdb files have always been painful for me -
>>>just when I thought I was OK in an .mdb, I have a .adp I'm working on
>>>and I need to use Set db as CurrentDb - Constant "object variable or
>>>with block variable not set" error almost killed me and VB Help says
>>>
>>>"If you want to use the CurrentDb method in an Access project (.adp)
>>>you must set a permanent reference to the DAO 3.6 Object library in
>>>the Microsoft Visual Basic Editor."
>>>
>>>How do I set a "Permanent" reference? As opposed to another kind?
>>>
>>>TIA[/color][/color][/color]

re: Permanent Reference using VBE

I'm sorry - I was not clear . . . The following perfectly fine SQL
syntax in a query in a .mdb results in a "Error in WHERE clause near
'!'. Unable to parse query text" error message when used in an .adp
SQL Query pane. I can't get the syntax right for referring to a
control on a running form for the criteria of a particular column - in
this case, the FuneralHome column.

SELECT [_tblFuneralHome].FuneralHome, [_tblFuneralHome].AddrLine1,
[_tblFuneralHome].AddrLine2, [_tblFuneralHome].City,
[_tblFuneralHome].State, [_tblFuneralHome].Zip
FROM _tblFuneralHome
WHERE
((([_tblFuneralHome].FuneralHome)=[Forms]![frmCremation]![cboFuneralHome]));


On Thu, 15 Jan 2004 07:00:34 -0700, Tom van Stiphout
<tom7744@no.spam.cox.net> wrote:
[color=blue]
>On Thu, 15 Jan 2004 12:52:16 GMT, CFW <chris@thewilkfamily.com> wrote:
>
>Still works the same way. In the NorthwindCS sample app, I ran the
>Employee, and then added a textbox to the Orders form with a
>ControlSource of:
>=[Forms]![Employees]![FirstName]
>(Access provided the bracketing)
>And it correctly pulled up the first name of the employee.
>
>-Tom.
>
>
>[color=green]
>>Thanks for the response. That's exactly what I resorted to "Set New
>>ADODB.Recordset as Recordset" . . . etc. Can I ask a follow-up . . .
>>this is new to me - using the "Builder . . . " in a .mdb database
>>within the criteria cell to create a query I could do in my sleep.
>>With a .adp project, there is no "Builder . . . " and I can't seem to
>>get the syntax to set the criteria for a column in a "view" to refer
>>to a control on an open form. = Forms!frmSomeForm!cboSomeComboBox
>>used to work great! What am I missing? TIA!
>>
>>On Wed, 14 Jan 2004 22:37:01 -0700, Tom van Stiphout
>><tom7744@no.spam.cox.net> wrote:
>>[color=darkred]
>>>On Thu, 15 Jan 2004 04:21:46 GMT, CFW <chris@thewilkfamily.com> wrote:
>>>
>>>That's probably not MSFT's finest work. There is no other kind (if you
>>>don't consider late binding).
>>>
>>>First off you have to realize that ADPs are about using ADO against a
>>>SQL Server back-end. Doing anything outside of this mold is an
>>>advanced topic. The advice to drag DAO into this is somewhat off base
>>>for 95% of the ADP applications.
>>>
>>>Knowing you're now in an ADO environment immediately breaks some of
>>>the DAO code you may be more comfortable with. No more CurrentDB, no
>>>more (DAO.)Recordset. Welcome Connection object, ADODB.Recordset
>>>object, etc.
>>>
>>>-Tom.
>>>
>>>
>>>>References in Access and .mdb files have always been painful for me -
>>>>just when I thought I was OK in an .mdb, I have a .adp I'm working on
>>>>and I need to use Set db as CurrentDb - Constant "object variable or
>>>>with block variable not set" error almost killed me and VB Help says
>>>>
>>>>"If you want to use the CurrentDb method in an Access project (.adp)
>>>>you must set a permanent reference to the DAO 3.6 Object library in
>>>>the Microsoft Visual Basic Editor."
>>>>
>>>>How do I set a "Permanent" reference? As opposed to another kind?
>>>>
>>>>TIA[/color][/color][/color]

re: Permanent Reference using VBE

On Fri, 16 Jan 2004 02:03:15 GMT, Chris Wilk <chris@thewilkfamily.com>
wrote:

No can do. And for a good reason. What you call the SQL Query pane is
used to create Views. Views are SQL Server objects, stored in the
server, and executed by and on the server. These objects don't know
which client invoked them and what the values in the controls of their
forms might be.

Better to use a stored procedure that takes an argument, and use the
InputParameters property of the form to supply the value. You may set
it to an expression like [Forms]![frmCremation]![cboFuneralHome].

Get away from the thought that ADP is the same as MDB. I can recommend
Getz et al's Access Developer Handbook. I believe Part 2 has a big
section on ADP. Required reading.

-Tom.


[color=blue]
>I'm sorry - I was not clear . . . The following perfectly fine SQL
>syntax in a query in a .mdb results in a "Error in WHERE clause near
>'!'. Unable to parse query text" error message when used in an .adp
>SQL Query pane. I can't get the syntax right for referring to a
>control on a running form for the criteria of a particular column - in
>this case, the FuneralHome column.
>
>SELECT [_tblFuneralHome].FuneralHome, [_tblFuneralHome].AddrLine1,
>[_tblFuneralHome].AddrLine2, [_tblFuneralHome].City,
>[_tblFuneralHome].State, [_tblFuneralHome].Zip
>FROM _tblFuneralHome
>WHERE
>((([_tblFuneralHome].FuneralHome)=[Forms]![frmCremation]![cboFuneralHome]));
>
>
>On Thu, 15 Jan 2004 07:00:34 -0700, Tom van Stiphout
><tom7744@no.spam.cox.net> wrote:
>[color=green]
>>On Thu, 15 Jan 2004 12:52:16 GMT, CFW <chris@thewilkfamily.com> wrote:
>>
>>Still works the same way. In the NorthwindCS sample app, I ran the
>>Employee, and then added a textbox to the Orders form with a
>>ControlSource of:
>>=[Forms]![Employees]![FirstName]
>>(Access provided the bracketing)
>>And it correctly pulled up the first name of the employee.
>>
>>-Tom.
>>
>>
>>[color=darkred]
>>>Thanks for the response. That's exactly what I resorted to "Set New
>>>ADODB.Recordset as Recordset" . . . etc. Can I ask a follow-up . . .
>>>this is new to me - using the "Builder . . . " in a .mdb database
>>>within the criteria cell to create a query I could do in my sleep.
>>>With a .adp project, there is no "Builder . . . " and I can't seem to
>>>get the syntax to set the criteria for a column in a "view" to refer
>>>to a control on an open form. = Forms!frmSomeForm!cboSomeComboBox
>>>used to work great! What am I missing? TIA!
>>>
>>>On Wed, 14 Jan 2004 22:37:01 -0700, Tom van Stiphout
>>><tom7744@no.spam.cox.net> wrote:
>>>
>>>>On Thu, 15 Jan 2004 04:21:46 GMT, CFW <chris@thewilkfamily.com> wrote:
>>>>
>>>>That's probably not MSFT's finest work. There is no other kind (if you
>>>>don't consider late binding).
>>>>
>>>>First off you have to realize that ADPs are about using ADO against a
>>>>SQL Server back-end. Doing anything outside of this mold is an
>>>>advanced topic. The advice to drag DAO into this is somewhat off base
>>>>for 95% of the ADP applications.
>>>>
>>>>Knowing you're now in an ADO environment immediately breaks some of
>>>>the DAO code you may be more comfortable with. No more CurrentDB, no
>>>>more (DAO.)Recordset. Welcome Connection object, ADODB.Recordset
>>>>object, etc.
>>>>
>>>>-Tom.
>>>>
>>>>
>>>>>References in Access and .mdb files have always been painful for me -
>>>>>just when I thought I was OK in an .mdb, I have a .adp I'm working on
>>>>>and I need to use Set db as CurrentDb - Constant "object variable or
>>>>>with block variable not set" error almost killed me and VB Help says
>>>>>
>>>>>"If you want to use the CurrentDb method in an Access project (.adp)
>>>>>you must set a permanent reference to the DAO 3.6 Object library in
>>>>>the Microsoft Visual Basic Editor."
>>>>>
>>>>>How do I set a "Permanent" reference? As opposed to another kind?
>>>>>
>>>>>TIA[/color][/color][/color]

re: Permanent Reference using VBE

Thanks Tom - I'm getting the book today!

On Thu, 15 Jan 2004 21:39:44 -0700, Tom van Stiphout
<tom7744@no.spam.cox.net> wrote:
[color=blue]
>On Fri, 16 Jan 2004 02:03:15 GMT, Chris Wilk <chris@thewilkfamily.com>
>wrote:
>
>No can do. And for a good reason. What you call the SQL Query pane is
>used to create Views. Views are SQL Server objects, stored in the
>server, and executed by and on the server. These objects don't know
>which client invoked them and what the values in the controls of their
>forms might be.
>
>Better to use a stored procedure that takes an argument, and use the
>InputParameters property of the form to supply the value. You may set
>it to an expression like [Forms]![frmCremation]![cboFuneralHome].
>
>Get away from the thought that ADP is the same as MDB. I can recommend
>Getz et al's Access Developer Handbook. I believe Part 2 has a big
>section on ADP. Required reading.
>
>-Tom.
>
>
>[color=green]
>>I'm sorry - I was not clear . . . The following perfectly fine SQL
>>syntax in a query in a .mdb results in a "Error in WHERE clause near
>>'!'. Unable to parse query text" error message when used in an .adp
>>SQL Query pane. I can't get the syntax right for referring to a
>>control on a running form for the criteria of a particular column - in
>>this case, the FuneralHome column.
>>
>>SELECT [_tblFuneralHome].FuneralHome, [_tblFuneralHome].AddrLine1,
>>[_tblFuneralHome].AddrLine2, [_tblFuneralHome].City,
>>[_tblFuneralHome].State, [_tblFuneralHome].Zip
>>FROM _tblFuneralHome
>>WHERE
>>((([_tblFuneralHome].FuneralHome)=[Forms]![frmCremation]![cboFuneralHome]));
>>
>>
>>On Thu, 15 Jan 2004 07:00:34 -0700, Tom van Stiphout
>><tom7744@no.spam.cox.net> wrote:
>>[color=darkred]
>>>On Thu, 15 Jan 2004 12:52:16 GMT, CFW <chris@thewilkfamily.com> wrote:
>>>
>>>Still works the same way. In the NorthwindCS sample app, I ran the
>>>Employee, and then added a textbox to the Orders form with a
>>>ControlSource of:
>>>=[Forms]![Employees]![FirstName]
>>>(Access provided the bracketing)
>>>And it correctly pulled up the first name of the employee.
>>>
>>>-Tom.
>>>
>>>
>>>
>>>>Thanks for the response. That's exactly what I resorted to "Set New
>>>>ADODB.Recordset as Recordset" . . . etc. Can I ask a follow-up . . .
>>>>this is new to me - using the "Builder . . . " in a .mdb database
>>>>within the criteria cell to create a query I could do in my sleep.
>>>>With a .adp project, there is no "Builder . . . " and I can't seem to
>>>>get the syntax to set the criteria for a column in a "view" to refer
>>>>to a control on an open form. = Forms!frmSomeForm!cboSomeComboBox
>>>>used to work great! What am I missing? TIA!
>>>>
>>>>On Wed, 14 Jan 2004 22:37:01 -0700, Tom van Stiphout
>>>><tom7744@no.spam.cox.net> wrote:
>>>>
>>>>>On Thu, 15 Jan 2004 04:21:46 GMT, CFW <chris@thewilkfamily.com> wrote:
>>>>>
>>>>>That's probably not MSFT's finest work. There is no other kind (if you
>>>>>don't consider late binding).
>>>>>
>>>>>First off you have to realize that ADPs are about using ADO against a
>>>>>SQL Server back-end. Doing anything outside of this mold is an
>>>>>advanced topic. The advice to drag DAO into this is somewhat off base
>>>>>for 95% of the ADP applications.
>>>>>
>>>>>Knowing you're now in an ADO environment immediately breaks some of
>>>>>the DAO code you may be more comfortable with. No more CurrentDB, no
>>>>>more (DAO.)Recordset. Welcome Connection object, ADODB.Recordset
>>>>>object, etc.
>>>>>
>>>>>-Tom.
>>>>>
>>>>>
>>>>>>References in Access and .mdb files have always been painful for me -
>>>>>>just when I thought I was OK in an .mdb, I have a .adp I'm working on
>>>>>>and I need to use Set db as CurrentDb - Constant "object variable or
>>>>>>with block variable not set" error almost killed me and VB Help says
>>>>>>
>>>>>>"If you want to use the CurrentDb method in an Access project (.adp)
>>>>>>you must set a permanent reference to the DAO 3.6 Object library in
>>>>>>the Microsoft Visual Basic Editor."
>>>>>>
>>>>>>How do I set a "Permanent" reference? As opposed to another kind?
>>>>>>
>>>>>>TIA[/color][/color][/color]