A little help with access forms.

[Terry]

I am in the process of converting an Access 2002 db to use SQL Server 2000
as the backend. I have found a bug that is giving me fits. I know it is
because of my own ignorance as to how Access forms work.

I have a form that pulls from a local query that joins two tables. I have a
second form that opens when a button is pushed on the first form. The second
form updates one of the tables called from the first form. After the update
and I close the second form, the first form is unchanged. Even if I refresh
the record or scroll to another one, the record appears to be unchanged. If
I close the mdb and open it back up again, the data is updated.

How do forms know when an update has happened and why does this record not
update when I scroll though the records? Is this because it is using a query
to create the form or is it some type of transaction issue?

To give a little background, I have mostly done web programming with
VBScript on ASP and pretty much only have done procedure type programming.
Access seems really foreign to me because I don't understand how jet or
access forms work yet. I am used to "send a query, get a recordset" type
stuff.

Thanks in advance -- Terry

 

[Nikos Yannacopoulos]

Terry,

Use the On Close event of the second form to run a line of code like:

Forms!FirstFormName.Requery

Note: if the second form may also be opened and closed independently of the
first form, then this would produce an error if the first form is not open
when the event code fires. If this is the case, then use something like:

On Error Resume Next
Forms!FirstFormName.Requery
On Error GoTo 0

HTH,
Nikos

 

[Terry]

Nikos,
That is exactly the information I needed. Thank you! I am slowly
learning the objects and methods used in Access.

Again, Thanks!
-- Terry

 

[Terry]

Ok, one more dumb question. After the requery is performed, the form's
record number jumps back to be beginning of the record set.

Is there a method or property to programmatically jump to a specific record
number?

Thanks again,
Terry

 

[Terry]

Found it!
DoCmd.GoToRecord acDataForm, "FormName", acGoTo, int-- Terry"Terry"

 

[Albert D. Kallal]

Actually, you could try a few things:

If the data in the first from can be modified BEFORE the 2nd form is
launched, you should consider forcing a disk write *right* before you launch
the 2nd form.

However, lets leave the above issue alone.

It is possible that you don't need to re-query the first form. You could
execute a command to force all data to be re-freshed, and this does NOT
re-load all data to the form, but only stuff that have been changed.
Further, it don't lose the current position.

So, on the 2nd forms close even, you could go:

Forms!FirstFormName.Refresh

The above would thus eliminate the need to re-set the current record
position.

 

[Terry]

Well, that is the thing. The refresh method does not display the data that
is actually in the database. Only if I exit and reopen mdb file, or use the
Reload method on the close event of the 2nd form, does it show the correct
information. The problem I am running into now is that it's simply too slow
moving back to the correct record after a reload. I am looking at maybe
attempting a passthough query for the main form to help speed it up. It
appears that you can't use views in an mdb file

-- Terry

 

[Albert D. Kallal]

Well, that is the thing. The refresh method does not display the data that
is actually in the database.

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

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).

Only if I exit and reopen mdb file, or use the
Reload method

Ah, I think you mean .requery method here..right?

information. The problem I am running into now is that it's simply too
slow
moving back to the correct record after a reload.

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.

It
appears that you can't use views in an mdb file

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.
There is NO reason to try and make a join on the client side, that stuff
should be done serve side. 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.

 

[Terry]

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.

 

[Albert D. Kallal]

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


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?

Yes, that field needs to be exposed to ms-access front end.

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.

Hum, 4000 records is small. I run applications with 55+ highly rated tables,
5 users, and small tables in the 50,000 record range (and, we are talking no
sql server here..but just a JET file share). Response times for this
application are instant on a typical office network, and as mentioned no sql
server is being used.

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.

Yes, my point was don't use queries built on the ms-access side if they join
multiple tables. Just make a view on the sql side, and then link to that,
and then used the linked "table" (well, actually view) in these cases.

The whole concept of "queries", in the Access
sense, is completely foreign to me.

If you are talking about a select query in ms-access, a select query in
ms-access is EXACTLY the same concept as a view in sql server. The reason
why we don't call these queries views, as ms-access queries can also be
update and append queries...which of course are not views. But, for all
conceptual concepts, a select query in ms-access is the SAME concept as a
view on sql server. Of course, throwing in this concept of linked tables can
also add confusing here...but queries in ms-access are simply views to
tables.

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.

You should be able to get performance up to spec. A adp will not necessary
perform better, but often restrict choices for the developer to not do the
wrong thing...and that is why they run better!

 

[yyl2004]

Well, that is the thing. The refresh method does not display the data that
is actually in the database. Only if I exit and reopen mdb file, or use the
Reload method on the close event of the 2nd form, does it show the correct
information. The problem I am running into now is that it's simply too slow
moving back to the correct record after a reload. I am looking at maybe
attempting a passthough query for the main form to help speed it up. It
appears that you can't use views in an mdb file

-- Terry

 

[Terry]

Yes, that field needs to be exposed to ms-access front end.

Should it simply be added to the select statement in the query? How do you
"expose" it?

Yes, my point was don't use queries built on the ms-access side if they join
multiple tables. Just make a view on the sql side, and then link to that,
and then used the linked "table" (well, actually view) in these cases.


If you are talking about a select query in ms-access, a select query in
ms-access is EXACTLY the same concept as a view in sql server. The reason
why we don't call these queries views, as ms-access queries can also be
update and append queries...which of course are not views. But, for all
conceptual concepts, a select query in ms-access is the SAME concept as a
view on sql server. Of course, throwing in this concept of linked tables can
also add confusing here...but queries in ms-access are simply views to
tables.

I suppose the technology seems a bit strange to me because I am used to
dealing with things in a true client-server way. Cursor movement seems to be
hidden, a "query" really isn't a query and I just can't seem to get too far
past the IDE and really look at the code. I am ignorant about Access and I
feel like a fish out of water

Again, Thanks
-- Terry

 

[Albert D. Kallal]

Should it simply be added to the select statement in the query? How do you
"expose" it?

Yes, simply include the time stamp field in the query. This is not so
important for a report, or just a query that grabs some data. However, when
you are dealing with a form bound to this query, then the timestamp issue
becomes critical. So,...yes...just include the TS field, and especially for
forms that allow you to edit data. You certainly don't need a control on the
form with the TS, but you do want the time stamp to be part of the table,
and it needs to be exposed to ms-access.

I suppose the technology seems a bit strange to me because I am used to
dealing with things in a true client-server way. Cursor movement seems to
be
hidden

You can certainly think of any reocrdset in code as a cursor. I mean, it
really depends on what type of data structures and the client tools you are
using to work the data.

, a "query" really isn't a query and I just can't seem to get too far
past the IDE and really look at the code.

I only mentioned that queries in ms-access are conceptuality the same thing
as views in sql server. All this means is that instead of using a table
direct, you got some sql statements or code that looks to the "view", and
not the table.

And, since ms-access has so many data features, and throwing in sql server,
then you centrally has a "mix" of tools here, and which end to put sql on,
and the number of features of "how" to do things really expands.

I would say that having sql server skills here is rather nice thing to have
on your part, but don't forget ms-access does have quite large curve, and in
some ways more then sql server.