Problem handling dates in an ACCESS / VBA application with SQL Server database

Question

Problem handling dates in an ACCESS / VBA application with SQL Server database

Navigation

#1 by (0 votes)

0

I needed to migrate the bank of an application made in vba using the access. The database was native to access and I switched 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.

The access displays and saves the records in the format dd / mm / yyyy

25/06/1987

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

1987-06-25 00:00:00

With some configuration changes to the default date format (in SQL Server) I only succeeded in inserting data. However, I was not able to return the date in dd / mm / yyyy in any way.

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

In addition to not being a "pretty" solution I have not been able to fix all the bugs that this causes in the system. Sometimes I do not have full control of what access wants to save or return from the database 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 the format dd / mm / yyyy?

Or is there any solution to this in access itself that spares me the trouble of handling all queries?

sql-server datetime vba ms-access

asked by anonymous 20.10.2017 / 06:19

1 answer

Variable Enclosing Scope - Java SE How to put html links / tags in models.TextField

score 0 · Accepted Answer

After many searches on the internet and nothing enlightening I ended up getting a post in a forum that elucidated the issue.

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

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

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

NOTE: My SQL Server database was migrated from Access through a migration tool and defined all date fields as datetime2 (0).