none
Access frontend, SQL backend: Datetime format is changed and VBA-code as Addnew and setting dates are not working

    Question

  • Spent hours googling/binging this, but did not got answer, only found two similar questions - with no solution...

    I recently migrated tables from access database to sql 2008R2 using SSMA. All went well and the access tables were linked to the new sql versions.
    The problem is that although the SQL tables have dates set as 'DateTime2', Access appears to see the dates in another way as before. This is causing an issue in the forms where a date is presented as 2011-02-03 10:00:05 (yyyy-mm-dd hh:mm:ss) rather than 03-02-2011 (dd-mm-yyyy). Setting the format property in a form seems to have no effect. 

    The problem is critical as it is not only is the display, but gives errors when new date-values are set from VBA code.
    (Besides manual change of dates are difficult because of the minutes and seconds (though this may be done by changing the field into a SQL DATE...))
    What to do?

    Wednesday, February 16, 2011 1:07 AM

Answers

  • Thanx for clarifying the ODBC Manager versions, That do also explian my "missing" DataSources (the 64bit only show 64bit sources...).

    I use 64bit Windows, but 32bit Office (to make it possible to create solutions for 32bit systems too).

    I ended with a working solution by implementing the DNS-lessness www.accessmvp.com/djsteele/DSNLessLinks.html,
    first there was no diffrence, buit after changing the driver to "DRIVER=SQL Server Native Client 10.0" it finally worked :-)
    Strange - that using the same driver, created in the ODBC manager, did not work, I tried that before impolementing the DNSless.

    Thanx for the hint :-)


    Sune
    • Marked as answer by Sune Wednesday, February 16, 2011 3:58 PM
    Wednesday, February 16, 2011 3:57 PM

All replies

  • hi,

    can you give us a concrete example:

    What value is stored in SQL Server, what output does a

    SELECT dateTime2Column FROM yourTable ;

    return? How is this value presented in the linked table?
    A simple repro using Access 2010 32bit works fine.

    How do you exactly set a new date value in VBA? What exact error message do you get?


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Wednesday, February 16, 2011 9:19 AM
    1.  SELECT from SQL (Sql Manager) gives f.ex. "2010-11-27 00:00:00
       Similar SELECT from the old ACCDB, linked to a MDB (query in ACCDB) gives "27-11-2010"
       Similar select in new ACCDB, linked to SQL (query in ACCDB) gives "2010-11-27 00:00:00"
    2. In linked table in new ACCDB the date is "2010-11-27 00:00:00"
      In linked table in old ACCDB the date is "27-11-2010" 
    3. In form in new ACCDB is date seen as "2011-11-27 00:00:00"
      When I do a AddNew (by VBA-code) the field change into "16-02-2011" and error while trying to update (wrong date format, I guess)
      The update in this case is done by f.ex. doing a "DoCmd.GoToRecord , , acPrevious", which gives error code 2105 "You can't go to the specified record."
      Another way that worked before is to go to another record by using a value from a Find-Record-form and do a "DoCmd.FindRecord <newrecordname>". This gives error 3146 "ODBC--call failed. [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string. (#241)". Other functions (filling in other values in the form, based on a chosen name f.ex. is working, - but the record is never written to the Table).

      In form in old ACCDB the date is seen as "27-11-2010"
      When I do a AddNew (by VBA-code) the field change into "16-02-2011" and update and everytrhing is fine
    4. Another way (than the AddNew) the date is created/changed is by using VBA "[datefield] = Date". The datefield is set to f.ex. "16-02-2011" (where old value could be "2010-11-27 00:00:00"), - and the same problems as described above is present.

      Only way to "escape" is to click the ESC-key, to cancel all changes of the record in focus. 
    I hope this clarify, and I am at your disposal for further investigations any time ;-) almost 24/7, sigh...


    Sune
    Wednesday, February 16, 2011 12:59 PM
  • hi Sune,

    sounds weird. Which driver are you using to connect to the SQL Server? SQL Native Client or OLEDB for SQL Server? Try relinking the tables with the driver you're actually not using.

    btw, what language and locales are involved on your client system and the server?


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Wednesday, February 16, 2011 1:23 PM
  • more info, testing changing the Regional Settings for the OS (WIN7).

    Previous posting was based on Regional Setting for Denmark.
    Changing to English (usa) and restarting the ACCDB gives different behavior:

    the form presents the date is before ("2010-11-27 00:00:00")
    AddNew makes different new date: "2/16/2011" (remark: No minuts/seconds???).
    Update is working ("DoCmd.GoToRecord , , acPrevious").
    Going back to the newly created record shows "2011-02-16 00:00:00"

    I suspect the ACCDB/SQL connection to have problems to handle the danish date-format (common format for many European countries)?
    Would it be different to migrate by ask SSMA to use DATE instead of the default DateTime2? Not really a solution, as we don't use the time, only the date, in this particular case - but not in all... And to ask the users just to accept usa-formats of times, numbers, dates, etc. would also not work ;-(

     


    Sune
    Wednesday, February 16, 2011 1:24 PM
  • hi Sune,

    I've tested it using DATETIME2 on an EN-US installed Win7 64bit with SQL Server 2008 R2 64bit and Access 2010 32bit using German regional settings, input language and keyboard layout and it works fine.

    So i assume that changing the SQL Server data type to DATE or better if you really want to give it a try I would use DATETIME instead.


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Wednesday, February 16, 2011 1:33 PM
  • I am using ODBC, I made a system DSN and now it gets a bit weird, here:
    I don't remember if I used the SQL Server driver or the SQL Server Native 10.0 driver,
    so I went to ODBC Data Source Administrator to check - and here is my Sytem ODBC Data source invisible or...???!! not to see. But it works... how...?
    In linked tables manager is written 

    I'll try to create a Data Connection by using OLEDB and check for any difference


    Sune
    Wednesday, February 16, 2011 1:34 PM
  • hi Sune

    you may also test it using a DSN-less connection:

    http://www.accessmvp.com/djsteele/DSNLessLinks.html
    http://support.microsoft.com/kb/892490


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Wednesday, February 16, 2011 1:48 PM
  • Thanx - I may have to, but did prefer the advantage of using DSN for the reason of easy developement on another system than the production-system:
    With DSN I can just use similar named DSN's and code will always work (even the DSN's are pointing to different servernames).
    If I use DSN-less - I have to change the connection string before delivering to the production-system...
    Or did I get something wrong here... ;-)

    Actually - I could just put the actual ServerName in a databasefield, an get that one from the DSN, and all other tables by DSN-less...?


    Sune
    Wednesday, February 16, 2011 1:53 PM
  • I found my ODBC-drivers and sources again - they are invisible by starting ODBC Manager from Administrative Tools => ODBC Manager ("%windir%\system32\odbcad32.exe"), 
    but executing the identical named  "c:\Windows\SysWOW64\odbcad32.exe" shows everything again. weird.
    The system32 version ONLY shows the two SQL-server drivers - none of the others.

    Should I just copy the SysWOW64 version to system32 to make it a bit easier to start the ODBC manager?

    MORE interesting is that I found the checkbox in the ODBC Source: "Use regional settings when outputting currency, numbers..:".
    I did not have that selected before, and tried it out.
    Result is that the dates are all shown as "00:00:00" both when looking in the oinked tabel and in the form. Date in the SQL database is still the full date.

    I read about this checkbox that thuis should only be used for application that SHOW data, and not for applications that PROCESS data - so it should not be checked in my case, - just wondering about that "00:00:00" date format...

    What now: As the DSN-less solution will demand A LOT of coding, for all the calls to the database(?) I will give the ODBC a chance more, if any idead pop up.

    Or can you give a hint about what way could be best, cost/benefit  in mind...?

    yours: Sune


    Sune
    Wednesday, February 16, 2011 2:37 PM
  • hi Sune,

    Should I just copy the SysWOW64 version to system32 to make it a bit easier to start the ODBC manager?

    No, don't do that!!!

    The version in SysWOW64 provides the DSN's for 32bit drivers, the other for 64bit drivers.

    What version of Access do you use? If you use an Access version prior to Access 2010 then it is always a 32bit version. If you use Access 2010 then check whether you use the 32bit or 64bit version.

    Depending on the bit-ness of your Access version you need to start the appropriate ODBC manager.

    Or can you give a hint about what way could be best, cost/benefit  in mind...?

    Not really, as I can't reproduce this behaviour...


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Wednesday, February 16, 2011 3:07 PM
  • Thanx for clarifying the ODBC Manager versions, That do also explian my "missing" DataSources (the 64bit only show 64bit sources...).

    I use 64bit Windows, but 32bit Office (to make it possible to create solutions for 32bit systems too).

    I ended with a working solution by implementing the DNS-lessness www.accessmvp.com/djsteele/DSNLessLinks.html,
    first there was no diffrence, buit after changing the driver to "DRIVER=SQL Server Native Client 10.0" it finally worked :-)
    Strange - that using the same driver, created in the ODBC manager, did not work, I tried that before impolementing the DNSless.

    Thanx for the hint :-)


    Sune
    • Marked as answer by Sune Wednesday, February 16, 2011 3:58 PM
    Wednesday, February 16, 2011 3:57 PM
  • hi Sune,

    your always welcome...


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Wednesday, February 16, 2011 4:08 PM
  • I had the same issue as you, I just changed the datatype in sql from datetime2 to normal datetime refreshed the linked table and everythign in access looks ok now
    Thursday, September 29, 2011 10:14 PM
  • Good Morning Sune!

    I have recently ported several databases and some Access Apps over to SQL SERVER 2008 R2, now I am experiencing

    2015 Errors on DoCmd Goto. Record ( New, previous, etc ). I tried to use the DSNLess fix, but fail to see or understand how, or why it would, could or "Like to" fix my problem. Is there something in SQL SERVER that I can "tweak", to let it handle the

    problem with the date time correctly... ( this part I don't understand at all, why does it need do know the date to open a new record, or goto a previous record?

    I was not present when the SQL Server was installed, snce this was outsourced to HP, and we    couldn't view or touch it until they were through. Now, I need to get the apps running with the new server  before the end of the month,

    as they are going to un-install the old server.. regardless or not that our new one works.

    Are you in the US? Maybe I could give you a call?

    Best Regards,

    Hank

    Tuesday, March 13, 2012 2:56 PM
  • Hey Hank, I am in Denmark ;-)

    I did not research 2015 errors, but I can give a few, quick comments on your message:

    The issue about how Date can influence on previous/next record was because of the wrong date-format  the actual record can not update.
    I do not see why AddNew should fail, except if clicking addnew also include attempt to update the actual record...

    For the DNSless fix, did you remember to change the connection code to the newest version (as I described in the thread)?

    Do you notice the difference of 32bit and 64bit drivers for the connection - this is essential, and confusing as the 32/64 managers seems equal, but are placed in different folders (as also described in the thread).

    Also, I notice a newer comment (Peter_Developer_BA), with a - maybe - quick fix, by changing from DateTime2 to Datetime, I hav enot tested this, as now everything works here ;-)

    good luck, no stress (yet)... :-) sune


    Sune

    Tuesday, March 13, 2012 3:28 PM
  • Thank you guys for discussing this matter.  I have several years of experience developing Access databases, and I decided to upsize it to SQL.  Besides the normal issues (opening ports to get a network connection, etc.) the upsizing went smoothly.  However, this date thing was kicking my behind.

    After reading your thread, I realized you had the solution. However, nobody mentioned that the native client had to be downloaded onto the machine!  Maybe I just don't have the common sense, but I finally got it and it worked like a charm.  The link to get it is:

    http://www.microsoft.com/download/en/details.aspx?id=3522

    Look for the Microsoft SQL Server 2008 Native Client about halfway down the page, and install it.

    Since I used the Microsoft SQL Server Migration assistant, my connection string includes DRIVER=SQL SERVER, and not DRIVER=SQL Server Native Client 10.0.

    I can't find the connection string in my database.  Does anyone know how to change the driver name permanently rather than having to re-link every table every time I open my database?  Or am I missing something?

    Thanks again!

    Monday, April 23, 2012 6:29 AM
  • Thank you all for this discussion. I was able to find the solution to the date problem that I had as I am migrating the MS Access tables to MSSQL but continue to use the MS Access for front-end processing. My problem was mostly because I was using the wrong driver, "SQL Server" instead of the "SQL Server Native Client 10.0". 
    Thursday, May 31, 2012 8:07 PM