Update SQL Server Table from Access Table containing Date/Time Data Type

1
votes

I'm having an issue transferring some existing data from an Access Back-End Table to a SQL Server 2012 Back-End table. I'm up sizing our database, and since our Access database relies on tables from our ERP software which is also on SQL Server, it makes sense to run all queries on SQL server and just retrieve information in Access from SQL Server though ODBC.

Here is the problem. When trying to update all fields from StatusT (Access Table) to dbo_StatusT (SQL Server Table), it only works if I leave out the Date/Time data type columns. The moment I introduce the date/time data type in the update query, it says that over 13000 rows cannot be updated, but at the end it doesn't copy anything over. If I leave out the date/time fields from the query, the rest of the data transfers over easily.

             ACCESS                                          SQL Server
Field Name             Data Type                  Column Name           Data Type
OrderID                Number                     OrderID               int
LoadID                 Number                     LoadID                int
StepNumber             Number                     StepNumber            smallint
InProgress             Yes/No                     InProgress            bit
Completed              Yes/No                     Completed             bit
TimeStamp              Date/Time                  TimeStamp             datetime
TimeStamp_Completed    Date/Time                  TimeStamp_Completed   datetime

As far as I can tell, my tables are set up properly on both ends, and the datetime data type in SQL Server should work with Date/Time data type in Access.

I get the following error in access when I try to move the data over from Access to SQL Server.

Microsoft Access didn't update 13243 field(s) due to a type conversion failure, 0, record(s) due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.

Here is my query.

UPDATE StatusT INNER JOIN dbo_StatusT ON (StatusT.StepNumber = dbo_StatusT.StepNumber) AND (StatusT.LoadID = dbo_StatusT.LoadID) AND (StatusT.OrderID = dbo_StatusT.OrderID) SET dbo_StatusT.Completed = IIf([StatusT].[Completed]=-1,1,0), dbo_StatusT.InProgress = IIf([StatusT].[InProgress]=-1,1,0), dbo_StatusT.[Timestamp] = [StatusT].[Timestamp], dbo_StatusT.Timestamp_Completed = [StatusT].[Timestamp_Completed];

I have adjusted the Yes/No fields to use 1 and 0 due to the bit representation in SQL Server, but I just can't figure out the Date/Time problem. OrderID, LoadID, and StepNumber are the fields that are both the same on each side, the rest of the data needs to update on SQL Server.

2
sql-serversql-server-2012odbcms-access-2013

2 Answers

1
votes

A big silly mistake on my end. I made some changes to the tables, but I never re-linked my tables in Access. Which means that it was still going off of the data types set up in the previous table configuration in MS SQL Server. The moment I refreshed the tables, everything worked without any issues. Thanks for the help.

0
votes

I would suggest converting the date/time in Access to a string - perhaps MS SQL will be able to read this:

UPDATE StatusT 
INNER JOIN dbo_StatusT  ON (StatusT.StepNumber = dbo_StatusT.StepNumber) 
                        AND (StatusT.LoadID = dbo_StatusT.LoadID) 
                        AND (StatusT.OrderID = dbo_StatusT.OrderID) 
SET dbo_StatusT.Completed = IIf([StatusT].[Completed]=-1,1,0), 
dbo_StatusT.InProgress = IIf([StatusT].[InProgress]=-1,1,0), 
dbo_StatusT.[Timestamp] = FORMAT([StatusT].[Timestamp], "yyyymmdd hh:mm:ss"), 
dbo_StatusT.Timestamp_Completed = FORMAT([StatusT].[Timestamp_Completed], "yyyymmdd hh:mm:ss");