Re: A little help with access forms.

From: Terry (ttrapp.spam.me.not_at_org.insurors)
Date: 09/24/04 

Date: Fri, 24 Sep 2004 11:57:24 -0500

> The refresh method will not show new records that you add, but it will
show
> changes to existing records (at least it should).

For some reason, it does not. Based on what you said about new records, I
checked to make sure that the data was not being duplicated in the table
where it is not refreshing correctly. There were no duplicates.

I don't know why it's not working either.

> You of course do expose and have timestamp fields on any of those linked
> tables..right? (both m-access, and sql-server use time stamp fields to
> figure out when to update things, and also when things where updated. You
> want to always expose these timestamp fields to ms-access).

This is an upsized database and it does have the upsize_ts field, however, I
do not think that field was added to the upsized "query". Should it be?

>
> Ah, I think you mean .requery method here..right?
>
My bad. Yes, requery method.

> Likely, you have a form with lots of records loaded..and you should not. I
> would fix your design to not have so many records loaded, or as mentioned
> get the "refresh" method working here.

I lay no claim to this "design" (GRIN). I did reindex the database and it
REALLY improved things. It only has about 4000 records so I didn't think
that to be such a big deal. It just seems strange that the test app I
designed using an .ADP project is lightning fast while this upsized .mdb
file pokes along moving from record to record on the same database. I know
it's my own ignorance how how the current application is designed. Starting
from scratch is going to be my best bet for improving performance, I think.

> I use views all the time to sql server. In fact, for reports, and
especially
> any query that tries to join multiple tables..that should be placed on the
> server, make into a view, and then you link to that view from ms-access.

I didn't say Access, I said .mdb file. In an .adp file the views show up as
querys. I now see that views show up as tables in .mdb files.

> There is NO reason to try and make a join on the client side, that stuff
> should be done serve side.

Obviously.

> Remember, since your database engine is now sql
> server, then likely don't have any problems of performance with such a
> powerful database engine at your service. Since you are using sql server,
> then using c++, VB, or ms-access on the client side will make NO
difference
> in terms of performance here.

Well that's the thing. I have never done this stuff on the client side. I
have experience with various SQL servers with applications I have written
either for the web (ASP/VBScript,PHP,Perl) or applications that use an SQL
as the back end (PERL/C/C++). The whole concept of "queries", in the Access
sense, is completely foreign to me. I don't even understand how SQL cursor
movement, updates, etc are really done in Access. I'm ignorant as to what is
normally done in Access, so please don't flame me too bad :)

Thanks for everyone's help! I think I may have this app useable enough to
get my users over to SQL server. Then I can rebuild the app from scratch as
an Access Database Project. :)

Relevant Pages

  • Re: Using SQl to store aspx pages and memory problems
    ... I've seen classic ASP applications design this way that work ... database isn't some sort of magic pixie dust that solves all problems. ... > the memory increases - the slowness of the request could be dealt with by ... >> takes to get the page from SQL Server. ...
    (microsoft.public.dotnet.framework.aspnet)
  • RE: database options with SBS 2003 R2
    ... It was doable for the customer to design their own access database in a peer ... If you don't want to deal with a version of SQL Server for the data, ... end MDB files. ...
    (microsoft.public.windows.server.sbs)
  • Re: Database design question
    ... Pro SQL Server 2000 Database Design ... Please reply to the newsgroups only unless you are ...
    (microsoft.public.sqlserver.programming)
  • RE: SQL Server 2000 & Access 2007 Project
    ... You have indicated that the Access database is an Access Project file (ADP) ... occurs when we attempt to open the same table's design view in the Access ... so that SQL server cannot get the type of the data in the design view. ...
    (microsoft.public.sqlserver.clients)
  • Re: Back-to-front introduction needed
    ... >>about database design principles. ... then is the database the .mdb file in an isolated sense or is it ... >>> Access is an application--not a DBMS engine. ... > can't expect many of my PCs to have any sort of version of SQL Server, ...
    (microsoft.public.dotnet.framework.adonet)