Program1 works fine, program2 doesn't, due to a date problem, but code inside are the same (2 Viewers)

amorosik

Active member
Local time
Today, 16:12
Joined
Apr 18, 2020
Messages
581
This is truly incredible (at least for me).
Program1.accde becomes program2.accde after some changes to the 'articles' form.
Both program1 and program2 work correctly on the development PC, with Win10 with Access 2013 32bit
Let's move to user PC2, a Win10 pro pc, where only the Access runtime 2013 32bit is installed.
Program1 works correctly on user PC2.
I send program2 to user PC2, and when I open the 'purchases' form on form_load, it throws me an error 2766 and 3146 on Me.Filter and me.FilterOn

Private Sub Form_Load()
Me.Filter = "REGISTRATION_DATE >= #" & dataSQL(Format$(DateAdd("m", -1, Now))) & "# "
Me.FilterOn = True Me.OrderBy = "ID_DOC desc"
Me.OrderByOn = True
End Sub

The dataSQL function reverses the day and month in the dates field
Tables connected via ODBC to a Firebird database server, the REGISTRATION_DATE field is a Timestamp
The database to which program1 and program2 are connected is the same
Note that the 'purchases' form has NOT been changed from program1 to program2; it is identical.
So on PC2, the 'purchases' form is the same between program1 and program2, the data is identical, and the Windows on PC2 is identical.
There should be no reason for program2 not to work while program1 works correctly.
Access options verified and identical between program1 and program2.

I investigated a bit and saw that the problem on program2 is the dates.
On program1, if I enter "REGISTRATION_DATE >= #07/19/2025#" it works correctly.
However, if I do the same thing on program2, it doesn't work;
On program2 I have to enter "REGISTRATION_DATE >= #19/07/2025#"
Note that this problem occurs on different versions of program1, but on the same PC2, and therefore it doesn't work.
It could be a problem with the date/time settings and similar bullshit, or a different ODBC driver version because both program 1 and program 2 are using on pc2 the same DNS.

What could this depend on?
Any advice?
 
Can't you just Format it without passing it to dataSQL() function:
Code:
Me.Filte = "REGISTRATION_DATE >= #" & Format$(DateAdd("m", -1, Now), "mm/dd/yyyy") & "#"
 
Can't you just Format it without passing it to dataSQL() function:
Code:
Me.Filte = "REGISTRATION_DATE >= #" & Format$(DateAdd("m", -1, Now), "mm/dd/yyyy") & "#"

Yes, of course I could do that.
I simply should modify the dateSQL function and make it return the date exactly as passed
The point is: the system has worked this way for years, for dozens of different versions
Why should it work differently now?
But above all, why does program1.accde still work correctly?
 
Were the ODBC tables relinked on PC2 for one of the applications but not the other?
Did you check the data type of REGISTRATION_DATE in the table in Access? Are they the same in both applications?
Does it make a difference if you explicitly relink the tables on PC2?
 
In your form_load code I can't believe that the syntax of me.filteron =true line works correctly, but maybe it does. I would split that into 2 lines.

I also always define dates as format(somedate,"long date") to ensure there is no ambiguity between UK and US formats, although I'm told that's not 100% the best way.

Instead of using #02/07/2025# it resolves to #2nd July 2025# so there's no possibility of a problem.

What is the text for your error numbers out of interest? 2766 and 3146. Don't the descriptions give you an insight?
 
I also always define dates as format(somedate,"long date") to ensure there is no ambiguity between UK and US formats, although I'm told that's not 100% the best way.
It's actually not. Best use the ISO date format because it is universally unambiguous for computers and for developers.
 
What could this depend on?

It would perhaps depend on whether you actually had the required libraries on PC2, since you only have the Access Runtime installed there. I have not dealt with Runtime so will have to defer to my colleagues on how to properly set that up, but I'm pretty sure that installing a "full" Office package loads up a lot of library files that a "runtime-only" package does not install.

That is particularly likely to be the problem because of the two errors: Error 2766 deals with "automation component" while error 3146 is an ODBC failure. Both of those involve Windows library files or device driver files. The errors both - in their own way - say that the attempt to exercise an option associated with an external, non-Access file didn't work.

I would go to PC1 and check the names of the files listed in the Tools >> References control, then go to PC2 to verify that each of those named files is available on PC2 in the appropriate folders (including "trusted location" issues.) It is possible that you have the files on PC2 but they aren't appropriately registered. But I'd give good odds on it being related to (lack of) library files.
 
Were the ODBC tables relinked on PC2 for one of the applications but not the other?
Did you check the data type of REGISTRATION_DATE in the table in Access? Are they the same in both applications?
Does it make a difference if you explicitly relink the tables on PC2?
Yes, program1 and program2 link tables from the same DNS and therefore from the same database.
The REGISTRATION_DATE field is a timestamp, and both program1 and program2 see it as a timestamp.
What do you mean by 'explicitly relink' the tables?
 
In your form_load code I can't believe that the syntax of me.filteron =true line works correctly, but maybe it does. I would split that into 2 lines.

I also always define dates as format(somedate,"long date") to ensure there is no ambiguity between UK and US formats, although I'm told that's not 100% the best way.

Instead of using #02/07/2025# it resolves to #2nd July 2025# so there's no possibility of a problem.

What is the text for your error numbers out of interest? 2766 and 3146. Don't the descriptions give you an insight?

Right, I hadn't thought of that. It might work.
I'll test it as soon as possible.
 
It would perhaps depend on whether you actually had the required libraries on PC2, since you only have the Access Runtime installed there. I have not dealt with Runtime so will have to defer to my colleagues on how to properly set that up, but I'm pretty sure that installing a "full" Office package loads up a lot of library files that a "runtime-only" package does not install.

That is particularly likely to be the problem because of the two errors: Error 2766 deals with "automation component" while error 3146 is an ODBC failure. Both of those involve Windows library files or device driver files. The errors both - in their own way - say that the attempt to exercise an option associated with an external, non-Access file didn't work.

I would go to PC1 and check the names of the files listed in the Tools >> References control, then go to PC2 to verify that each of those named files is available on PC2 in the appropriate folders (including "trusted location" issues.) It is possible that you have the files on PC2 but they aren't appropriately registered. But I'd give good odds on it being related to (lack of) library files.

That's correct.
Since it's a particularly strange problem, I set up a virtual machine with a clean Windows 10 and installed the entire package with just the runtime.
And the problem with program2 persists.
While with program1, everything works correctly.

Then I installed the full Access, and program2 also started behaving as expected.
Therefore, with the full development environment, libraries and other things are loaded that solve the problem.
 

Users who are viewing this thread

Back
Top Bottom