Ms-Access 2003

[RC]

Hi everyone,

Wondering if anyone out there has encountered this kind of problem.

I am in the process of migrating my client (they were running Windwows NT
4.5 SBS and MS-Access 2000 database) to a brand new server running Windows
2003 SBS with MS-Access 2003. The had an application in MS-Access 2000. After
successfully transferring the data and installing MS-Access 2003 on the
desktops (the database files are on the server), if I try to double-click the
databse to open it, it gives me the following error message:

Your Windows Control Panel Date Settings are Currently Unrecognized. Please
reset your short Date Format to the U.S. Short Date Format (ie 'mm/dd/yy').

I have set the short date formats to mm/dd/yy both on the server as well as
the desktops. However I am still uanble to open the application.

Any helps/suggestions will be greatly appreciated.

Regards
Ravi

 

[Brendan Reynolds]

I don't think that's an Access error message. I think that's something the
developer has built in to these apps. To test this theory, could you try the
sample app, Northwind? Can you open it, under the same conditions, without
getting this message?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[RC]

Thanks for your reply Brendan. I wil ltry that.
Is it necessary to load Acess on the server to use their application? The
desktops are all XP Pro Sp2 (just fyi). In case your theory is correct - how
can I get my client back on their feet with this application? Throw in the
the older server and have the users work of it?
Regards

 

[Brendan Reynolds]

There should not usually be any need for Access to be installed *on the
server* no.

If my theory is correct, then the solution will depend on how, where, and
why the developer did this. Let's establish whether the theory is correct
before we go too far down that road, though!

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[RC]

Hi Brendan,
Just got off the phone with the client. They are able to open the sample
database on their desktops without gettting the error message.
What should I do next?
Regards

 

[Brendan Reynolds]

The safest option would be to do nothing until you talk to the person who
developed the applications. But I'm guessing that person is no longer
available?

Well then, when an Access application starts, it will look for a macro
called AutoExec, and if it finds one, it will run it. If a start-up form or
data access page has been defined (Tools, Startup, Display Form/Page) Access
will open that form or data access page and run any code in the various
event procedures that are fired when the object opens. For example, a form's
Open, Load, and Activate events will fire, and any code in the Open, Load
and Activate event procedures will run. So those are the places to start
looking.

If there are both an AutoExec macro and a startup form or data access page,
they'll both be executed.

Look for an AutoExec macro. Look for the text of the message there. Select
Startup from the Tools menu. Does the 'Display Form/Page' combo box say
'(none)' or does it display the name of a form or data access page? If the
latter, open that form or data access page in design view and look for the
message there.

Although less likely, it is also possible that the code may be in a function
that is called from a query, for example a query that forms the record
source of a startup form.

I probably don't need to tell you this, but let's err on the side of caution
anyway - make sure you have a recent, reliable back up before you change
anything. Make one change at a time, and test thoroughly before making any
further changes.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[RC]

Hi Brendan,
Thanks for the suggestions. Yes the developer is no longer available.
The process that you are talking about - to check the Tools-->Startup etc.
is to be carried out on the old machine I presume. So in case I do find
anything in the Display Form/Page where should I copy this into.
The problem is Brendan I am a systems admin guy with little or no knowledge
of programming.
On the local desktops (I checked this on mine in my office) when there is no
file open the Startup option is dimmed. So when I go to my client site today,
I will open the app on the server and see what are the options - if any -
defined there.
Thanks for all your help.

Regards

 

[Brendan Reynolds]

Responses inline ...

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Hi Brendan,
Thanks for the suggestions. Yes the developer is no longer available.
The process that you are talking about - to check the Tools-->Startup etc.
is to be carried out on the old machine I presume.

It doesn't matter. The problem is, as far as I can tell, in the application.

So in case I do find
anything in the Display Form/Page where should I copy this into.

It's not a question of copying it, it's a question of fixing it.

The problem is Brendan I am a systems admin guy with little or no
knowledge
of programming.

This doesn't appear to be a configuration issue. It appears to be a
programming issue. You would be well advised, therefore, to ask a programmer
to have a look at it. (OK, that's what you're doing here, but I mean at
first hand!

On the local desktops (I checked this on mine in my office) when there is
no
file open the Startup option is dimmed.

That's correct, the option is disabled until you open a file.

So when I go to my client site today,
I will open the app on the server and see what are the options - if any -
defined there.

Let us know what you find, and we may be able to offer further suggestions
then.

 

[Brendan Reynolds]

Is there a macro named AutoExec?

The _be file will be the 'back-end' data file, the problem is unlikely to be
there.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Brendan Reynolds]

Down the left hand side of the database window is a list of object types:
Tables, Queries, Forms, Reports, Pages, Macros, Modules. Click Macros to see
any macros that exist in the MDB. In case the developer may have hidden
them, select Options from the Tools menu, select the View tab, and select
the Hidden Objects and System Objects check boxes. (After you have
determined whether an AutoExec macro exists, it would be a good idea to
unselect these two options again.)

What exactly happens when you open the MDB? At what point is the message
about the date format displayed?

The log-in dialog means that at some point Jet user-and-group level security
was applied to this application. This could complicate matters, but I am not
the best person to advice you on security issues. I leave that to the
security experts who hang out in microsoft.public.access.security

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[RC]

Currently I am not able to open the database at all. When I click on the
database to open it I get a Security warning indicating that the file may not
be safe to open. When I click on open, I get the date format error message
and that's it. I am unable to proceed anywhere further than that.

 

[Brendan Reynolds]

Hold down one of the Shift keys on the keyboard while opening the MDB.
Unless the option has been disabled, that should by-pass any AutoExec macro
or startup form, opening the MDB to the database window.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Brendan Reynolds]

I don't know. There are a lot of things you can do in a macro. One of the
things you can do is to call a VBA function, so even things you can't do
directly in a macro can be done indirectly, by calling a function to do
them. You're going to have to dig around a bit to find out where and how the
original developer is testing the date format. The message that was
displayed is one clue you can use - do you see the text of that message
anywhere?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[RC]

I have found that entry. Tried to moodify my date settings but was not
successful. Is it okay if I send you the part for the date settings? A guy
like you can figure out in one stroke what is amiss. If it is okay - what is
the best way to get it across to you?

 

[Brendan Reynolds]

If it is in VBA code, you can just copy the code and paste it into a
newsgroup post. If it's in the AutoExec macro, could you try to describe
what you see? The advantages of keeping it in the newsgroup are that a) if I
can't see what the problem is, someone else probably will, and b) others get
to benefit from the discussion as well.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[RC]

'Declare Variables
Dim strvalue As String, strValueName As String
Dim lngRetval As Long
'Assign Variables
strValueName = "sShortDate"
'Retreive Windows API Value for Short Date Format
strvalue = Space(adhcMaxSize)
lngRetval = adh_accRegGetVal(adhcHKEY_CURRENT_USER, adhcRegTips,
strValueName, ByVal strvalue, adhcMaxSize)
' If lngRetval = adhcAccErrSuccess Then
strvalue = adhTrimNull(strvalue)
' End If
'If hkey_Current_User\ControlPanel\International\sShortdate = "dd/mm/yy" then
If strvalue Like "*d*/*m*/*y*" Then
MsgBox "Your Windows Control Panel Date Settings" & Chr(13) & _
" are Currently set to the International Date Format." & Chr(13) &
Chr(13) & _ "This Program requires you to reset your short Date Format" &
Chr(13) & _" to the U.S. Short Date Format (ie. 'mm/dd/yy')"
ElseIf strvalue Like "*m*/*d*/*y*" Then
' MsgBox "Your Computer is set to U.S. Date"
Else
MsgBox "Your Windows Control Panel Date Settings" & Chr(13) & _
" are Currently Unrecognized." & Chr(13) & Chr(13) & _
" Please reset your short Date Format" & Chr(13) & _
"to the U.S. Short Date Format (ie. 'mm/dd/yy')"
Application.Quit acPrompt
End If
End Function

 

[Brendan Reynolds]

We're getting close. This code is calling other code (the 'adh_accRegGetVal'
function) so I'm going to have to see that code as well. However, the 'adh_'
prefix indicates that this is almost certainly code from the Access
Developer's Handbook, and we shouldn't really post that code in a newsgroup,
as that could be an infringement of copyright. I have the Access 97 and
Access 2000 Developers' Handbooks, but I don't seem to have the CDs here at
the office, they're probably at home. So I'm going to ask you to e-mail the
MDB to me. If you're willing to do that, please zip it first, and then send
it to (change the obvious) brenreyn at brinkster dot net

Just the application MDB will do - I don't need the big '_be' data MDB.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Brendan Reynolds]

OK, I've received the MDB. The first thing that I did was to change this ...

' If lngRetval = adhcAccErrSuccess Then
strvalue = adhTrimNull(strvalue)
' End If

.... to this ...

If lngRetval = adhcAccErrSuccess Then
strvalue = adhTrimNull(strvalue)
Else
Debug.Print lngRetval
Stop
End If

Sure enough, the code stops on the 'Stop' line and prints the value -201 to
the Immediate window.

Up in the Declarations section of the module I find ...

Public Const adhcAccErrRegKeyNotFound = -201

.... indicating that the problem seems to be an invalid registry key.

The other constants that seem relevant here are ...

Public Const adhcHKEY_CURRENT_USER = &H80000001
Const adhcRegTips = "Control Panel\International"
Const adhcMaxSize = 500

And now I'm stuck - I don't know what the problem is. Anyone else?

BTW: I'm aware, of course, that by far the best long term solution would be
to fix the app so that it is not dependant on a specific date format, but RC
has told us that he is an administrator, not a developer, so I don't think
that would be a practical suggestion right now.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Brendan Reynolds]

It would help if I knew which edition of the ADH this code came from. It
seems there was an error in the code in the Access 2000 edition ...

http://www.developershandbook.com/ErrataAccess2000Desktop.htm

.... but so far I haven't been able to get the proposed solution (download
basRegistry.bas and include it any project that uses the original code) to
work.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Brendan Reynolds]

OK, I can't get the ADH code to work, but the equivalent code from the
Access Web works. RC, please try this ...

Go to the following URL ...

http://www.mvps.org/access/api/api0015.htm

Copy everything between the '***Code Start***' and '***Code End***' lines

Back in Access, in the VBA editor, create a new standard module (choose
Module from the Insert menu) and paste in the code you copied from the web
page.

Back in the original module ('mdl_ReadAPI') find the lines that read ...

lngRetval = adh_accRegGetVal(adhcHKEY_CURRENT_USER, adhcRegTips,
strValueName, ByVal strvalue, adhcMaxSize)
' If lngRetval = adhcAccErrSuccess Then
strvalue = adhTrimNull(strvalue)
' End If

.... and comment out the two lines that are not already commented (an
apostrophe at the beginning of the line comments it out) so that the four
lines now look like this ...

' lngRetval = adh_accRegGetVal(adhcHKEY_CURRENT_USER, adhcRegTips,
strValueName, ByVal strvalue, adhcMaxSize)
' If lngRetval = adhcAccErrSuccess Then
' strvalue = adhTrimNull(strvalue)
' End If

.... and finally, add the following line, immediately after those four lines
....

strvalue = fReturnRegKeyValue(HKEY_CURRENT_USER, "Control
Panel\International", "sShortDate")

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.