MS Access Date() syntax in query to SQL Server

Question

I have found several posts on using the GETDATE() function for SQL Server linked table while in an Access front-end VBA procedure. Those posts are focused on the WHERE clause of the query, but I have been unable to find corresponding information on use of GETDATE() for column assignment.

For example, I understand that in the WHERE clause, I would use something like this:

WHERE MyDate = CAST(GETDATE() AS DATE)

However, I am getting syntax errors in VBA when I try to assign the current date to a column, like this:

INSERT INTO MyTable ( SomeValue, TheDate ) SELECT 'Widget' AS Expr1, CAST(GETDATE() AS DATE) AS Expr2;

In this example, TheDate is defined as DateTime in SQL Server. Written like this, VBA reports "Syntax error (missing operator) in query expression 'CAST(GETDATE() AS DATE)'. I tried to surround the expression with Access-friendly # date delimiters, but no luck there.

After spending about 30 minutes searching stackexchange.com various ways for MS Access Date() in SQL, I have been unable to find this. However it is so simple I am sure it was already answered somewhere.

Answer 1

There seems to be some confusing here. If you building a Access query, then ZERO ZERO of the SQL server date functions and syntax matter. Your SQL MUST continue to be written to Access standards unless you using a pass-though query.

However, I seen this 100x times here. What is the data type on sql server side? Is it datetime, or datetime2?

And double, triple, qudadropes, (and more) check the linked table in desing mode.

If you link to SQL server using the standard legacy "SQL Server" driver. The one that been shipped for 20 years since windows 98SE?

You MUST check if Access is seeing those columns as text, or as date columns (which in Access always allow a time part if you want).

Access code, queries, forms and EVERYTHING should require ZERO changes if you migrate that data from Access to SQL server and link the table. Again: ZERO ZERO changes.

However, if you used datetime2 on the SQL server side? Then you CAN NOT use the legacy "SQL server driver" when linking table. The reason is they don't support the newer datetime2 format. As a result, Access will actually see, use, and process that column as a text column. You REALLY, but REALLY do not want that to occur. Why? Becuase then you spend the next week asking questions on SO about how some date code or column or query does not work. again: ZERO ZERO changes are required in Access. If your dates are starting to break, then the issue is not date formats, but that column is now being seen by access as a TEXT data type.

Soltuion: Either change the sql side datetime2 columns to datetime, and re-link.

or re-link your tables using a newer native 11 (or later - up to 18 now). that way, access will see/use/process the datetime2 as a correct date format in Access.

So, before you do anything? Open one of the Access tables linked to SQL server in design mode. (ignore the read only prmompt). Now, look at the data type assigned to the date columns. If they are text, then you have a royal mess.

You need to re-link using the newer ODBC drivers.

Zero of your existing code, sql and quires should be touched or even changed if you using a linked table to sql server. But then again, if you linked using the wrong SQL ODBC driver, then Access cannot see nor process those datetime2 columns as date - it will be using text, and you beyond really don't want to allow that to occur.

In summary: Any date code, SQL updates, sorting, query, VBA code, form code, reports should continue to work with ZERO changes. If you are making changes to dates after a migration, then you done this all wrong, and those date columns are not being seen by access as date columns.

Either get rid of all datetime2 columns and then re-link (change them server side to datetime). Or re-link the tables using a native 11 or later ODBC driver. Either of these choices will fix this issue.

This is a fix that requires ZERO code, and zero changes to Access dealing with dates.

Answer 2

In MS Access you likely (not 100% sure for linked SQL, you have to experiment) should use Now() and Date() functions. First one is equivalent to getdate() in SQL, the second one returns current date without time.

Answer 3

If you run this in Access on a linked table (not a PT query), it should read:

INSERT INTO MyTable ( SomeValue, TheDate ) 
VALUES ('Widget', Date());