Hi,

I recently converted the connection on my ASP (with Access 2000 database) website from a DSN connection to a DSN-less connection (the connection string is now Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\inetpub\databases\xxx\data.mdb

Everything works fine and is noticeably quicker but I have a problem with dates. In a couple of places I have hidden form fields which use <%=Date()%> to insert a "created on" or "last updated" date. These now write into the database as mm/dd/yyyy format (instead of dd/mm/yyyy format). I've tried all manner of formatting the date in the hidden field but the Access database still converts it to US format. I've also tried setting the LCID to 2057 on each page but that doesn't make any difference either.

If the date is after the 12th of the month then it inserts the correct date (as I guess it figures that 22/08/2006 can't refer to the 22nd month).

I've tried manually setting the format option in the Access database field to dd/mm/yyyy format but it still writes as mm/dd/yyyy.

I've checked my Regional Settings in my control panel and they're set for UK formats.

I've also got a couple of date pickers on the site and even after you pick the correct date (i.e. 04/08/2006 (4th Aug)) it still writes the wrong format to the database (i.e. 08/04/2006 (8th April)).

I'm really stuck with this one, I've tried everything I know but I can't get it work. I've now had to revert back to a DSN connection (the dates work perfectly with the DSN connection).

Can anybody help?

Bud,

I fought with this exact same problem for about 6 months several years back before I just said to hell with it. I think what I ended up doing was to handle all the field data with a function that converted it into the format needed by Access, THEN inserted/updated it with the SQL call.

Hopefully that will help. It isn't the optimum solution. Another thing I had to do once was store the year , month, date in different integer fields so that I had total control over them.

Crappy solutions, I know, but I never found out how to fix it.

What code you are using to insert the Date into DB?