Re: type argument in OpenRecordset method
- From: mirandacascade@xxxxxxxxx
- Date: Thu, 25 Sep 2008 08:11:57 -0700 (PDT)
On Sep 25, 5:51 am, lyle fairfield <lyle.fairfi...@xxxxxxxxx> wrote:
The fully qualified table name precisely identifies the table:
Server.Database.Schema.Name
If one doesn't know the fully qualified table name then how could one
be sure he/she is getting the fully qualified table name of the
correct table?
The answer to the "If one doesn't know..." question is in #6
below...items 1 - 5 provide background info that may provide context
for #6
1) there are two processes that have names that include
"FullyQualifiedTableName":
- a SQL Server stored proc
- an MS Access function
2) MS Access function exists to:
- format call to stored proc
- handle result from stored proc
3) The Access app is used to retrieve information for current data or
for historical data
4) The databases/tables on the SQL Server side are split up based on
whether the data is current data or historical, for example:
- current loan data stored in <server>.current.dbo.loandata
- last year's loan data for December stored in
<server>.hist2007.dbo.loandata200712
- last year's loan data for November stored in
<server>.hist2007.dbo.loandata200711
- ... and so on
5) There are parameters passed to the stored proc:
- indicator whether it is current month or history
- a 'topic' describing the type of data (e.g. 'loan' or
'borrower' or 'collateral')
the combination of which provide enough information to the stored
proc to derive the fully qualified table name
6) So, yes, since the Access application provides the parameters to
the stored proc and since the stored proc uses those parameters to
derive the fully qualified table name, the Access app does, in fact,
have all the information needed to unambiguously identify the fully
qualified table name. The decision to put the logic on the SQL side
was based on the idea that the Access app shouldn't know much about
the specifics of how the SQL tables/columns are organized; to the
extent that the Access front end app needs to get specific
information, it is handled through an abstraction layer such as the
'topic'...the Access app says, "I need to know something specific
about 'borrower', so I'll pass a topic and let the SQL stored proc
have the smarts to know that the borrower information is in table
<whatever>". The idea/guess is that there are occasional changes to
things on the SQL side, and by providing this abstraction layer, it is
theoretically possible to insulate the front end app from some of the
changes on the SQL side. If there were no abstraction layer, a change
on the SQL side might necessitate a change to the front end app (along
with the effort of deploying that app to multiple locations). With an
abstraction layer there might be some changes on the SQL side that can
be handled entirely by changing the stored proc...one change to one
location.
.
- References:
- type argument in OpenRecordset method
- From: mirandacascade
- Re: type argument in OpenRecordset method
- From: lyle fairfield
- type argument in OpenRecordset method
- Prev by Date: Re: Relationships, Lookups and Access 2007 tables
- Next by Date: Re: Relationships, Lookups and Access 2007 tables
- Previous by thread: Re: type argument in OpenRecordset method
- Next by thread: Combo boxes on a report
- Index(es):
Relevant Pages
|
Loading
