Seems like creating a #temp table is kind of useless because it cannot be bound
to a subform.
OTOH, seems like a waste of resources to populate work tables in the "real"
database - both because of server mips and the round-trip-for-nothing.
I'm thinking about a timesheet where data is stored in a three-table heirarchy:
TimeSheet==>Tim eSheetLine==>Ho ursWorked. To present that to the user, the data
has to be pivoted into a temp or work table that shows a line for each project
and a column for each weekday. The user works on the pivoted presentation and
then, when it's time to save, the data is un-pivoted back into
Timesheet/TimeSheetLine/HoursWorked.
How about setting up a "tblTimeSheetPr esentation" on the server but never
actually writing to it? Instead, the server table would just serve as a
marker/hook/tabledef and when the app "used" it, it would open up the table, cut
the connection, and then work with it locally - where I *guess* ADO
transparently maintains an XML dataset or arrays in memory or something... And
once the local data has been un-pivoted and saved, the disconnected recordset
would have all it's rows deleted..
Is this feasible? Seems like it would satisfy the ADP table-wise, but prevent
the wasted round-trips to the server...
--
PeteCresswell 3 3003
On Mon, 05 Apr 2004 09:42:04 GMT, Chuck Grimsby
<c.*******@worl dnet.att.net.in valid> wrote: #temp tables are always kind of a hack in SQL, Pete. They are "expensive" in terms of resources and somewhat slow. There are also times when there's no other choice but to use them, however #temp tables really should be considered a last resort. Rather like using Make-Table queries in Access and then deleting the table when it's use is over or it's time to create a new table.
It's far more efficient in both Access and SQL to use a permanent "temp" table and limit the records the user deals with to just those that the user "owns" via the WHERE clause, or just use queries (views and Stored Procedures in SQL) to get to the data.
Well, that's not the whole story with SQL Server. There's another expens you
may want to avoid, and that's space in the transaction log that must be backed
up if you are doing incremental backups. Levels of temporary data activity
can swamp permanent activity in many applications, meaning that the space it
ends up taking in the backup dwarfs the space required for just backing up
changes to permanent data.
One alternative I've used with MDB front-ends is to actually use batches of
temporary data in permanent tables with batch IDs, but put those tables in a
separate database using the simple recovery model, so that the transaction log
will not grow. A view in the primary database references the table in the
other database, so the application doesn't have to keep track of the
implementation. Unfortunately, due to the way ADPs break encapsulation, I've
never been able to make this trick work in an ADP.
And, of course, it's never a good idea to access a table directly in any database. Always go through a query, view or stored procedure.
Again, with ADPs, you can indicate that you are using the view or stored
procedure, but ADO and Access will try to go around your back. You still get
the conceptual abstraction, but not true encapsulation. Using a view that has
the WITH METADATA option, you can actually get a view to work as
encapsulation, but with some significant limitations. I was not able to use
that to solve the problem above, for instance. Also, a WITH METADAT view will
report no or incorrect key information if multiple tables are involved.
That's OK with an MDB where you can assign a virtual primary key to a link,
but you can't do that in an ADP.
"Chuck Grimsby" <c.*******@worl dnet.att.net.in valid> wrote in message
news:p3******** *************** *********@4ax.c om... #temp tables are always kind of a hack in SQL, Pete. They are "expensive" in terms of resources and somewhat slow. There are also times when there's no other choice but to use them, however #temp tables really should be considered a last resort. Rather like using Make-Table queries in Access and then deleting the table when it's use is over or it's time to create a new table.
Temporary tables have two advantages though;
a) since tempdb is minimally logged less information is written to disk so
writes are somewhat faster,
b) you can locate tempdb on a separate physical drive, which can have a
marked improvement on performance
It's far more efficient in both Access and SQL to use a permanent "temp" table and limit the records the user deals with to just those that the user "owns" via the WHERE clause, or just use queries (views and Stored Procedures in SQL) to get to the data. And, of course, it's never a good idea to access a table directly in any database. Always go through a query, view or stored procedure.
I don't agree with this Chuck - I think global work tables are going to
incur more overhead. If you consider that they are fully logged, plus the
cost of having to constantly query for each users records, no, I think a
true tamp table will win out, both in disk I/O and processor cycles. To make
the temp table updateable though you have to roll your own as it were. In
this case I would prefer to just fetch the records once and process them on
the client.
For "pivoted" information, disconnected recordsets are indeed best, with behind the form updates to whatever table(s) are worked with.
I've started using local jet databases with adp's for these situations and
I've had great results. It's a lot more work, granted, especially managing
batch updates and handling multi-user conflicts, but the benefit of having
true relational capability client-side more than makes up for it. With
Access XP, (and I think 2000 as well), you can bind an updateable form to a
dao recordset created from a local jet database, so you really have the best
of both worlds.
We have an application where users can view a couple of hundred columns of
results, in pages of 10 columns at a time. The old system using server temp
tables was horrible; changing pages took several seconds - once we changed
to a local jet database it ran like lightning..
"John Winterbottom" <as******@hotma il.com> wrote in
news:c4******** *****@ID-185006.news.uni-berlin.de: I've started using local jet databases with adp's for these situations and I've had great results. It's a lot more work, granted, especially managing batch updates and handling multi-user conflicts, but the benefit of having true relational capability client-side more than makes up for it. With Access XP, (and I think 2000 as well), you can bind an updateable form to a dao recordset created from a local jet database, so you really have the best of both worlds.
But, heavens!!!! That means your ADP is not longer JETLESS!!!!!
HORROR OF HORRORS!!!!!!!! !!!!!
THE WORLD WILL COME TO AN END!!!!!!!
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: dp |
last post by:
After looking and looking, it appears that Access ADPs graphic query
designer won't display:
update customer
set = .
from customer,
where customer. = .;
It comes up with the "Query Definitions Differ" dialog box. Anybody know
anything about this? I can live this with I guess, however it was sure
|
by: Jerry Boone |
last post by:
A long time ago Larry Linson (MVP contributor to this group) made a point to
me that mdb's could do anything ADP's (Access Projects) could by using
linked tables to leverage Sql server databases. It's been years since then,
and I still sometimes think about that statement when evaluating the
usefulness of the ADP's I build. Larry and I...
|
by: (Pete Cresswell) |
last post by:
Seems like creating a #temp table is kind of useless because it cannot be bound
to a subform.
OTOH, seems like a waste of resources to populate work tables in the "real"
database - both because of server mips and the round-trip-for-nothing.
I'm thinking about a timesheet where data is stored in a three-table heirarchy:...
|
by: Neil Ginsberg |
last post by:
A while back I posted a message re. using an ADP file with a SQL Server back
end as opposed to MDB file with linked tables, thinking that the ADP file
would be less problematic. The input I got was that the ADP would be just
as, if not more, problematic; that ADO is far more limited than DAO,
requiring a lot of workarounds; and that it would...
|
by: Neil |
last post by:
I have a situation with an ODBC linked view in an Access 2000 MDB with a SQL
7 back end. The view is scrolling very slowly. However, if I open the view
in an ADP file, it scrolls quickly.
I needed to use an ODBC link for the view because it needs to be editable.
Otherwise, I would have used a pass-through query.
In previous discussions...
| |
by: gabry.morelli |
last post by:
Is it possible to create an .ADP application (in Access 2003) without
having to connect SQL Server but using directly the tables and queries
inside the .ADP file?
Thanks
|
by: Mbam |
last post by:
Hi all-
I have an ADP that has worked fine for years. It is the front end for a web based shopping cart.
I am now trying to clean up the database a bit and am separating all the static catalog & lookup tables from the order tables.
I might be over complicating this altogether but I now have a third database on the server that only has...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
| |
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |