Access Front End SQL Server backend date error

Maggs

Registered User.
Local time
Yesterday, 23:02
Joined
Feb 6, 2012
Messages
15
Hi
I have migrated a access 2007 DB to SQL Server. The Access front end links to the SQL Server.
In SQL Server the dates were converted to DateTime2 from Date in Access. In SQL Server the Date Time is shown in international format and in Access the same.
Now the Date picker does not work.
For migration I used MS migration assistant
Is there a way of migrating into SQL Server as Date as I couldn't see any options on changing any of the fields. Failing that is there a way for Access to see the Datetime2 as Date.
thanks
Maggs
 
I have basically the same thing since 2007... now 2010 and we are testing 2013.
SQL Server 2008 Back-End. Date picker works fine.
Let me explain how my process is different. Perhaps it is one or maybe all of them that will make the difference.

1. Create tables in MS Access - Migrate to SQL Server using the Microsoft SQL Server Migration Assistant for Access. It is a free download from Microsoft. You can search on my other post and see details about why I like it more.
2. Microsoft SQL Server Native Client (built on ODBC). My idea is that it really handles data types. It is a free MS download too. Search on my postings. It is kind of hard to find in the SQL Server download section. It must be installed on each client workstation. Or, in my case, on the Citrix Application Server.
3. Use VBA to link the tables. My Access has a local table that holds the names of tables in SQL Server to link. A Script runs the linked table process. Myself and others have posted various code segments about doing that.
My application can link to the production DB or test DB by running a command. The Test DB is a restored of a backup of the production SQL Server. It only takes a minute to re-link over 130 tables to SQL using the scripts.

Be sure to share your success, challenge for others to learn from.
 

Users who are viewing this thread

Back
Top Bottom