Date handling problem in an ACCESS/VBA application with SQL Server database

Asked

Viewed 919 times

0

I needed to migrate the database of an application made in vba using access. The database was native to access and passed to SQL Server.

In access, as everything is native everything worked perfectly, but in migration a problem that is giving me a lot of headache is the conversion of dates.

Access displays and saves records in dd/mm/yyyy format 

25/06/1987

SQL Server however has the format yyyy-mm-dd hh:MM:ss

1987-06-25 00:00:00

With some changes of default date format configuration (in SQL Server) I was successful only in inserting data. However I could not in any way in the queries return the date in the format dd/mm/yyyy.

I am currently having to prepare the application always before inserting, formatting the dates to the SQL Server format, as well as in the queries also formatting the date back to the Access format.

In addition to not being a "beautiful" solution I could not remedy all the bugs that this causes in the system. Sometimes I don’t have absolute control of what access wants to save or return from the bank and the dates in the fields are in the wrong format.

Anyway. The question is:

Is there any way to configure SQL Server dates to receive and return data in dd/mm/yyyy format?

Or else there is some solution to this in access itself that saves me the trouble of handling all queries?

sql-server vba datetime ms-access

  • https://social.technet.microsoft.com/Forums/pt-BR/5202d93b-eacb-4dfd-bd76-0c069b47f254/mudar-formato-data-para-pt?forum=infrageral

  • https://adrianobrandaodba.wordpress.com/2013/08/24/alterar-o-formato-de-data-do-sql-server/

  • I believe that if you only search for the date without the time the Access will already read as it needs. Do a test there.

1 answer

0


After many searches on the internet and nothing enlightening ended up coming to a post in a forum that clarified the issue.

While looking at the design of one of the sql server ODBC tables within access, I noticed that the date fields were set as short text.

When bringing the sql server tables through the Access odbc Wizard he understood that the datetime2(0) fields were short text instead of date, and so did not treat this correctly in the application.

To correct this, I had to conclude three actions: - Ensure that all tables have a key Primary. - Ensure that all Bit fields have a default value - Change all date fields to "datetime"

After following these steps and updating the linked tables, access correctly read the fields as being of date, instead of short text and all my requests could be made without the need of date formatting.

NOTE: My SQL Server database was migrated from Access via a migration tool and set all date fields to datetime2(0).

Browser other questions tagged sql-server vba datetime ms-access

You are not signed in. Login or sign up in order to post.