New to ADP and Confused on Parameters/SP

[kobi]

I have been using Access for about 15 years now, have taught myself enought
to fumble thru the VBA programming issues I wanted to use and now I feel like
I am back to square one. I had been developing a front end for a SQL back
end using linked tables that were giving all kinds of problems looking links
and disallowing appends, etc. Then I came across the Access ADP which seems
to be specifically developed for this type of thing. The existing SQL server
is a 3rd party developed product full of stored procedures/views/triggers,
etc.

This brings me to my first question, what is a trigger and what is the
easiest way to understand the difference between a stored procedure and a
view?

I have been doing research on SQL and trying to learn but nothing is direct
enough for my questions, fast enough for the timeframe I need and much of the
information seems to me contradictory and confusing. So I must apologize for
my ignorance, but I don't know where else to turn.

I have so many forms with combo boxes, text boxes, check boxes that pass
parameters thru vba to filter forms with DAO recordsets. Now, I've read that
you can't use DAO with an access adp project. Is this true? I'm at a total
loss of what to use to run my existing forms filtered thru DAO.recordset vba
code and how to pass the parameters to them to make them run. I tried using
the existing code, but was getting OLE object errors and different errors
stating that the declarations on the database and recordsets were
unrecognized.

Can someone help me clear my confusion? Any help would be appreciated.

 

[Vadim Rapp]

k> problems looking links and disallowing appends, etc. Then I came across
k> the Access ADP which seems to be specifically developed for this type of
k> thing.

Unfortunately, it looks like Microsoft is not betting on the future of adp.
For example, you can't design adp with sql seerver 2005.

k> This brings me to my first question, what is a trigger and what is the
k> easiest way to understand the difference between a stored procedure and
k> a view?

trigger is a code automatically launched by the server whenever a record is
deleted, inserted, or updated in the specified table. Each trigger is
attached to one table. The trigger has access to the
inserted/deleted/updated record.

View is one select statement and always returns resultset. It's a "virtual
table", so to speak. Stored procedure can have many statements, IF's, loops,
etc. The best practice is using views when you need to return results, and
s.p. when you need to do some action that does not return results - update
other tables, etc. There's yet another object, user-defined function, which
can return programmatically-generated resultset.

Note that for form or report recordsource, you can easily specify direct
select statement, you don't need to wrap it in view or stored procedure -
unless it's so big and complex that Access fails, then you wrap it in a
view.

k> I have so many forms with combo boxes, text boxes, check boxes that pass
k> parameters thru vba to filter forms with DAO recordsets. Now, I've read
k> that you can't use DAO with an access adp project. Is this true?

programmatically, you can use any object. But the effective way is to
specify combobox rowsource (for example) and let Access to the work for you.
Ideally, your application shouldn't be using a single recordset.

But, there's a trick. For forms recordsource, you can't reference Access
objects. For example, you can't specify form2's recordsource as

select * from table1 where id=forms!form1!field1

The solution is in using parameters:

select * from table where id=?

and then specify adp-specific form property InputParameters as

? = forms!form1!id .

Vadim Rapp

 

[PBsoft]

Unfortunately, it looks like Microsoft is not betting on the future of

adp. For example, you can't design adp with sql seerver 2005.

ADPs are supported also in Access 2007, where you will also be able to manipulate
SQL 2005 objects from.

Note that for form or report recordsource, you can easily specify
direct select statement, you don't need to wrap it in view or stored
procedure - unless it's so big and complex that Access fails, then you
wrap it in a view.

It should be so if it wouldn't be more flexyble to associate a report or
form to a stored procedure to allow easier updates.

But, there's a trick. For forms recordsource, you can't reference
Access objects. For example, you can't specify form2's recordsource as

select * from table1 where id=forms!form1!field1

The solution is in using parameters:

select * from table where id=?

and then specify adp-specific form property InputParameters as

? = forms!form1!id .

Good, so you'll let Access to automatically create temporary parameterized
stored procedures

 

[aaron.kempf]

Vadim

you are a CORNHOLER

a LIAR

and a FAGGOT

start speaking the truth. ADP has _JUST_ as bright of a future as
MDB.. Except for the FACT that MDB is for babies.

ADP you don't have bloat; you don't have stability problems.

EAT SHIT VADIM and start speaking the truth!

-Aaron
ADP Nationalist

 

[aaron.kempf]

There are a half-dozen different ways to bind parameters.

Sprocs are INFINITELY more powerful than baby MDB queries.

I just MILITANTLY disagree with this mis-information about ADP, Vadim.

I challenge you to a fistfight-- for the honor of ADP
..

 

[aaron.kempf]

and for the record; yeah-- DAO has been DED for 7 years now.

moveOn.org



-Aaron

 

[aaron.kempf]

Vadim

if you lost your training wheels and learned SQL Server?

you would use sprocs

there is magic auto-binding of parameters in ADP.. i dont have to do a
goddamn thing to get text fields to bind to parameters

shit just works-- out of the box

you've got a sproc looking for a parameter @PLU.. and a text field
named PLU-- it SHOULD auto-negotiate

lose the training wheels VADIM.

-Aaron

 

[aaron.kempf]

I've been building ADP apps for SIX YEARS. and i've never used the
input parameters property ONCE.

-Aaron

 

[Vadim Rapp]

P> Good, so you'll let Access to automatically create temporary
P> parameterized stored procedures

sure - which I don't then need to support, specifically, solve the usual
puzzle "who does it belong to, and where is it used".

Vadim Rapp