|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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.
|
|
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
|
|
|