DoCmd.GotoRecord in Access97 vs. Access 2000

[Laurie Brand]

I have an A97 BE with an A2000 FE. BE is on the server, FE is on the
individual user's machines. When this was all A97, no problems,
everything works fine, but when I upgraded the FE to Access 2000 it
will not execute the DoCmd.GotoRecord method. Unfortunately, one of
our offices moved to Office2000 and our office is remaining on
Office97, but we have to share the same data. I'm trying to find out
why this code won't work on Access2000.

I'm also running into problems with the .ldb being created by a user
on the 2000 version then locks out any users trying to run it from the
97 version.

Any ideas/suggestions would be appreciated.

Laurie

 

[TC]

Bit hard to help, when you don't say what error or message occurs for the
gotorecord.

TC

 

[Billy Yao [MSFT]]

Hi Laurie,

Thank you for posting in the community.

First of all, I would like to confirm my understanding of your issue.

From your description, I know when you upgraded your front-end to Access 2000, the
DoCmd.GotoRecord method could no longer be succefully executed. Additionally, you met
with the .ldb issue when a user on the 2000 front-end to open the 97 back-end database. If
there is anything I misunderstood, please feel free to let me know.

To narrow down the problem, we appreciate you provide the following information so that
we can address your problem quickly and troubleshoot the issue efficiently:

1. What's the error message you received when it failed to execute the DoCmd.GotoRecord
method?

2. If you can manage to perform another method of DoCmd from the front-end 2000 version,
what's the result?

3. Check your References in front-end 2000 version. See if the default reference "Microsoft
Access 9.0 Object Library" is selected. The default four references in Access 2000 are:

"Visual Basic for Application"
"Microsoft Access 9.0 Object Library"
"OLE Automation"
"Microsoft ActiveX Data Objects 2.1 Library"

To isolate some possible causes of this problem, please make sure these references are
used. Additionally, please add the "Microsoft Access 8.0 Object Library" and "Microsoft
DAO 3.6 Object Library" to suppress some reference issues.


4. For the .ldb issue, I recommend you apply a secured database with a .mdw work group
file and assign different login to different users. To start the Workgroup Administrator, open
the language folder (1033 for English) in \Program Files\Microsoft Office\Office, and then
double-click Wrkgadm.exe. For more information, see the Access Help on the topic "Work
with a workgroup information file".


Thank you for your patience Laurie. Please help narrow down your issue and apply my
suggestion to see if you can solve this problem. If there is anything more I can do to assist
you, or there is any progress you've made on this issue, please feel free to post it in the
group. Thanks!


Best regards,

Billy Yao
Microsoft Online Support

 

[MacDermott]

You might also want to check whether the Access 2000 machine is set to
default to opening for exclusive use.
(That's somewhere in Tools - Options)

HTH
- Turtle

 

[Laurie Brand]

Billy,

Thanks for the reply. The error message on the
DoCmd.GotoRecord, acNew is 2105: You can't go to the
specified record.

I had all the references except the ActiveX 2.1. I've
added that.

I'm not entirely clear on your idea with the .ldb. We are
not using Access security, just the default system.mdw.
Security is being handled through the application. So,
are you suggesting we set up two separate .mdw's, one for
the 2000 FE and one for the A97 FE? Ultimately, the
application is running from an .mde, but the current
testing is being done on the .mdb so we can better debug
any errors.

I've put a call into the gentleman in our Connecticut
office to retest on his end. I'll let you know if
anything changes.

Regards,
Laurie

 

[Laurie Brand]

1. What's the error message you received when it failed to execute the
DoCmd.GotoRecord method?

Error 2105, You can't go to the specified record. This is on a acNew
argument. There is no BeforeUpdate or BeforeInsert events running on
this form.

2. If you can manage to perform another method of DoCmd from the
front-end 2000 version, what's the result?

We can navigate to the next and previous records using
DoCmd.GotoRecord without error.

3. Check your References in front-end 2000 version. See if the
default reference "Microsoft Access 9.0 Object Library" is selected.
The default four references in Access 2000 are:

"Visual Basic for Application"
"Microsoft Access 9.0 Object Library"
"OLE Automation"
"Microsoft ActiveX Data Objects 2.1 Library"

I have all of these except the ActiveX 2.1 Library. I've added it.

To isolate some possible causes of this problem, please make sure
these references are used. Additionally, please add the "Microsoft
Access 8.0 Object Library" and "Microsoft DAO 3.6 Object Library" to
suppress some reference issues.

I have the DAO 3.6 library referenced already. I'll add the Access
8.0.


Security is not handled through the .mdw. The application has it's
own security tables built in. Users are recognized by their login
name and security is handled that way. They do not type in a login
name and password when opening the app. Do you mean make a generic
login in two separate .mdw's, one for 2000 and one for 97? If you
could clarify a little I would appreciate it.

Regards,
Laurie

 

[Billy Yao [MSFT]]

Laurie,

Thanks for your update and further information. I really appreciate all your efforts to check
references and provided more details to clarify the issue.

Things are now clearer to me and the problem is narrowed to one of the specific
DoCmd.GotoRecord methods (not all the method in the DoCmd.GotoRecord):

''''''''''''''''''''''''''''''
DoCmd.GotoRecord , , acNewRec
''''''''''''''''''''''''''''''

However, I'm not 100% sure of if the "acNew" argument you mentioned in your previous
message referred to the "acNewRec", as there is no "acNew" argument in the
Docmd.GoToRecord. Instead, the "acNew" is always used as a constant argument with
Docmd.DoMenuItem. Please feel free to let me know if I misunderstood on this field. Thanks
in advance!

On the other hand, you've verified that there is no BeforeUpdate or BeforeInsert events
running on the form, so I believe the following KB article might not make sense in this
scenario. If possible, I appreciate your time to read it and see if the method within it can
suppress the 2105 error message:

128195 ACC: Commands Not Available During BeforeUpdate Event
http://support.microsoft.com/?id=128195

Furthermore, if you use the Access 2000 project as a FE, I wonder whether you are applying
"Order By" on the specific column of the problem form. Based on my experience, this "Order
By" operation will be ignored and bring the 2105 error when you perform the "
DoCmd.GotoRecord , , acNewRec ".

If this addresses your problem, I suggest you using the following method in the OnOpen
event of that form:

''''''''''''''''''''''''''''''''''''
Private Sub Form_Open(Cancel As Integer)
Me.OrderBy = "Field DESC/ASC" ' Modify it as you please
Me.OrderByOn = True
End Sub
''''''''''''''''''''''''''''''''''''

To the scurity of the database, as your application has it's own security tables built in, I lean
to leave it handled with the built-in security mechanism. In the meanwhile, it is recommended
that you apply the latest Jet SP8 on your BE.

Jet 4.0 Service Pack 8:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;829558

Additionally, I'm eager to share you with some best practices that you can use to help keep
your Jet 3.X database in top working condition in a multiuser/network environment:

303519 HOW TO: Keep a Jet 3.x Database in Top Working Condition
http://support.microsoft.com/?id=303519



Please apply my suggestions above and keep up updated with the status of your issue. If
there is anything more I can do to assist you, please feel free to post it in the group. Thanks
for posting in the community.


Best regards,

Billy Yao
Microsoft Online Support

 

[Laurie Brand]

You might also want to check whether the Access 2000 machine is set to
default to opening for exclusive use.
(That's somewhere in Tools - Options)

HTH
- Turtle

Thanks for the suggestion. I'll look into it.

Laurie

 

[Laurie Brand]

Billy,

Thanks again for the reply.

Things are now clearer to me and the problem is narrowed to one of the specific
DoCmd.GotoRecord methods (not all the method in the DoCmd.GotoRecord):

Yes, I'm using DoCmd.GotoRecord,,acNewRec

I read the article, but it doesn't apply as I don't have any
beforeupdate or beforeinsert events running on this form.

Furthermore, if you use the Access 2000 project as a FE, I wonder whether you are applying
"Order By" on the specific column of the problem form.

The recordsource for this form is a query on a single table and yes,
it had three columns included in an orderby clause. I took the sort
order out of the query and added it in the OnOpen event as you
suggested.

To the scurity of the database, as your application has it's own security tables built in, I lean
to leave it handled with the built-in security mechanism. In the meanwhile, it is recommended
that you apply the latest Jet SP8 on your BE.

I'm not sure if you understand on the security issue. The problem is
that when the user in our Connecticut office opens the app, and then I
try to open the application at our Virginia office, I cannot edit, add
or delete any data. I am totally locked out. Once he exits the
application, I can then edit/add/delete. If I go in first and then he
goes in second, I can edit/add/delete fine and he is locked out.

Someone in our office suggested this may be an issue with the .ldb
being created from a Office2000 machine vs. an Office97 machine. One
..ldb locks the other users from the other site out.

Any thoughts on that?

Thanks for the notes on the articles. I'll read through them. I'm
still working on getting my Connecticut connection to test this with
the new orderby. I'll let you know soon.

Regards,
Laurie

 

[Billy Yao [MSFT]]

Hi Laurie,

Thank you for your update and specifications on the second security issue.
I appreciate all your time and efforts throughout the troubleshooting these
problem.

===============================================

As I assumed, there was really "Order By" applied on the columns of the
underlying table. In this scenario, an indeterminate relationship defined
between your tables in the
Underlying query may cause this problem. After putting the "Order By"
columns into the OnOpen event of the form, does the problem persist?

If it does, please help check if there are any indeterminate relationships
between your tables. Based on my experience, it's better to define a
primary key for the queried table if it has not one.

================================================

On the next place, from your specification, I have more details on your
security issue:

1. You are using Acc97 FE retrieve the data from the Acc97 BE and another
person is using Acc2000 FE retrieve the data from the same BE
2. When he is operating on some objects (such as tables), you are locked
and cannot perform edit/add/delete operation until he exits the database,
and vice versa.
3. The .ldb file is created in the same folder where the BE database
locates. (This isolates the exclusive mode issue.)


I have tested with the following steps:

1. Open the Acc97 Northwind database and split the database for a
standard Acc97 FE and BE.
2. Create an .mdw file and create two different user accounts in the
workgroup information file, share it in the same folder of the Acc97 BE.
3. Join the workgroup information (Acc97 using WRKGADM.EXE in the C:
\Windows\System32, and Acc2000 using WRKGADM.EXE in the <Office
2000 install path>\Office\1033)
3. Open the Northwind_Be.mdb with Acc97 and from another machine, use
Acc2000 (not upgraded from Acc97) to open the Northwind_Be.mdb
4. No locking happens when operating the Acc97 BE both in Acc97 and
Acc2000 with the different user accounts.

================================================

I have not yet addressed the root causes of your problem; however, based
on my experience, if you and another person open the same table in Acc97
BE and operate on it at the same time, the locking issue will occur because
of the exclusive lock.

In Acc97, if one opens the table and operates on the specific row/record,
the exclusive lock will be applied on the whole Data Page, not on the
row/record level (in Acc2000 or later). In this way, if you go ahead to modify
the data in the same data page, the modification will be avoided until he
finishes his work and the exclusive lock is release on that data page.
Please make sure if you are locked with the exclusive lock(s) on the same
data page.

Furthermore, I'd like to confirm the following information:

1. When the "Lock" happens, what object(s) you are
editing/adding/deleting, and how you operate on them.
2. Whether or not you used the shared workgroup information file (.mdw)
3. After you and another person exit Access, is the .ldb deleted
4. What's the accurate error message you received when you were locked
out and cannot modify the data.


Best regards,

Billy Yao
Microsoft Online Support

 

[Laurie Brand]

You might also want to check whether the Access 2000 machine is set to

default to opening for exclusive use.
(That's somewhere in Tools - Options)

HTH
- Turtle

Thanks. I checked and it is set to open in shared mode, not
exclusive.
Appreciate the suggestion.

I have traced the issue with the DoCmd back to the table. The user
cannot add a new record at the table level. I'm double checking with
our network people that he indeed has all the correct rights to the
server and directory, but I'm 99% sure he does. Still, doesn't hurt
to check.

Laurie

 

[susanna]

Laurie,

Regarding the error 2105, you need to requery the object before you ca
add a new record:

Me.Requery
DoCmd.GoToRecord , , acNewRec

Hope that helps,
Susann

 

[susanna]

Laurie,

Regarding the error 2105, you need to requery the object before you ca
add a new record:

Me.Requery
DoCmd.GoToRecord , , acNewRec

Hope that helps,
Susann