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

 

[Laurie Brand]

OK. After relinking the tables a couple of times, I am able to at
least give the user the ability to add and edit records through the
application.

Next, I created two shortcuts pointing to the two different FEs and
two different workgroup files. One workgroup is a copy of the
system.mdw on the 2000 box and one is a copy of the system.mdw on the
NT box.

Using these shortcuts, the Connecticut user can add records and edit
without issue. As soon as I enter the application on the 97FE and
move to any record, the Connecticut user cannot add records. He gets
Error 2105: Can't go to the specified record.

Someone mentioned something about being on the same version of jet. I
see a Jet500.dll on the 2000 machine and a group of files with Jet35
(no .dll's) on the NT box. How would I ensure that I'm using the same
version of Jet? Or is that a non-issue?

Any other ideas?

Thanks again!

Laurie

 

[Billy Yao [MSFT]]

Hi Laurie,

Thanks for your update and efforts on this issue! I'm glad to hear that the
user can add and edit records through the application after relinking the
tables.

As to the Error 2105: Can't go to the specified record, I think the Jet version
is not related with this issue. To isolate this possible cause, you can copy
the DLL files from 2000 machine to your NT machine and register the DLL
files one by one.

Based on my view, if the problem persists after putting the "Order By"
statement (original in queries) in the Form_Open event as below:

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

We may consider the following things:

1. Check the Data tab on the Forms Properties Sheet.
If the "Allow Additions" property is set to false, it might cause this problem.

2. If your Form's RecordSource is a Query or an SQL String combining 2 or
more Tables, check and make sure that the Query / SQL returns an
updateable Recordset.

If the Query / SQL String returns a non-updateable Recordset, you or your
user cannot modify existing Records or adding new Records. You can
open the Query by itself and see if an empty row (for a new record) button is
shown. You can also check whether the NewRec button in the
NavigationBar is enabled or not. If there is no empty row (NewRec button
disabled), your Query is not updateable and NewRec is not allowed and
therefore, on your Form, you cannot go to NewRec.

3. Whether or not there is no PK for the queried table

If it exits, please check if there are any indeterminate relationships between
your tables. It is recommended that you define a primary key for the
queried table if it has not one.

4. Are you able to go to a new record using the navigation buttons?
If you and your user both can, I suspect the problem might be located in the
VBA code. If that, could you help post it to reproduce your issue?

Laurie, if there is any update, please feel free to let us konw. We're happy
to be of assistance.

Best regards,

Billy Yao
Microsoft Online Support

 

[Laurie Brand]

Hi, Billy.

Thanks again for the ideas.

As to the Error 2105: Can't go to the specified record, I think the Jet version
is not related with this issue. To isolate this possible cause, you can copy
the DLL files from 2000 machine to your NT machine and register the DLL
files one by one.

Which files? I don't see any .dlls related to jet in the System32
folder?

Based on my view, if the problem persists after putting the "Order By"
statement (original in queries) in the Form_Open event as below:

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

The Order by is in the Open event.

We may consider the following things:

1. Check the Data tab on the Forms Properties Sheet.
If the "Allow Additions" property is set to false, it might cause this problem.

Allow Additions is being programmatically set to true when the user
clicks on the "Add New Record" button that is built on the form. I've
tried remarking out that code and setting it permanently to true, but
it doesn't change anything.

2. If your Form's RecordSource is a Query or an SQL String combining 2 or
more Tables, check and make sure that the Query / SQL returns an
updateable Recordset.

The forms recordsource is a single table and it is updatable when I am
in it or when the other user is in it, independently. Only when we
both go in does it lock the other one out.

If the Query / SQL String returns a non-updateable Recordset, you or your
user cannot modify existing Records or adding new Records. You can
open the Query by itself and see if an empty row (for a new record) button is
shown. You can also check whether the NewRec button in the
NavigationBar is enabled or not. If there is no empty row (NewRec button
disabled), your Query is not updateable and NewRec is not allowed and
therefore, on your Form, you cannot go to NewRec.

3. Whether or not there is no PK for the queried table

Yes, there is a PK on the bound table.

If it exists, please check if there are any indeterminate relationships between
your tables. It is recommended that you define a primary key for the
queried table if it has not one.

There is only one table in the recordsource with a single field PK.
There are no relationships built-in through Access in this
application. The relationships designed between the tables are
maintained through code, but the Access tools are not used to define
these relationships.

4. Are you able to go to a new record using the navigation buttons?
If you and your user both can, I suspect the problem might be located in the
VBA code. If that, could you help post it to reproduce your issue?

Yes, I can go to a new record if I'm in it alone. If the Access2000
user opens the application, then I cannot go to a new record. As soon
as he exits, I can again go to a new record.

Billy, thanks again. I'm out of ideas, but appreciate any other
thoughts you might have. I don't believe any of this is programmatic.
Is there another news group that I could post to that might be better
in resolving a network/jet/software incompatability issue?

Laurie

 

[Billy Yao [MSFT]]

Hello Laurie,

Thank you for your effots and I really appreciate your time to check and
troubleshoot this issue. Thank you so much!

As to the incompatability issue of Jet version, we can use Component
Checker (CC) to check your MDAC verion and compare the version umber
of each MDAC DLL file to a list of the DLL files that are shipped with each
MDAC version to see if there is any mismatch on the DLLs.

301202 HOW TO: Check for MDAC Version
http://support.microsoft.com/?id=301202

Please download the Component Checker, check your MDAC version(s)
on the machines with 97BE, 97FE and 2000BE. After you determined your
MDAC version(s), you can then use the second option in Component
Checker to check if there is any mismatch in the DLL files version. Note that
Access 97 use MSJET35.DLL and Access 2000 use MSJET40.DLL. If there
is any mismatch, I recommend you apply the latest MDAC 2.8 via the
following link:

http://www.microsoft.com/downloads/details.aspx?FamilyID=6c050fe3-
c795-4b7d-b037-185d0506396c&DisplayLang=en


As to Jet500.dll, it is the JET Engine .dll file for synchronization purposes
among instances of itself when they are loaded in different processes.
Microsoft WINS and DHCP services use Jet500.dll. You can copy it from
the 2000 machine to the "C:\windows\system323" folder on your NT
machine. After that, please register it in the command line with the following
prompt:

register "c:\windows\system32\msjet40.dll"

At last, if the problem persists, I think we have done everything possible. To
further troubleshoot the problem, you can post the issue to
network/software incompatability queue such as:

microsoft.public.internet
microsoft.public.softwareupdatesvcs

Hope that help,

Billy Yao
Microsoft Online Support

 

[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