Access appending of records to SQL not working

[alwayslrN]

We have a proces where users inter information in Excel and Access linked to that data. Access is used to extract that data and append it to a table in SQL Server. The rows get added to SQL Server, however, not all of the data is populated.

Looking at the Excel spreadsheet only the data in the first 6 columns makes it to SQL Server. The datatypes in Excel are or should be text,text,numeric,numeric,date,date. Anything after date is not making it to Excel.

This is a process that was working for months then last month the following was received in access

<b>Microsoft Access can't append all the records in the append query</b>

Still not sure why that happened, but we went to Access and for some reason the datatypes were all the same; text. We went through and changed them to match what is in SQL Server. By doing that we no longer get the append error, but, as stated above, we are now missing data after the insert.

If anyone has any ideas how to solve this issue, it is appreciated

 

[genomon]

If you are manipulating dates in Access with VBA, make sure they are delimited with the # character.

Depression is merely anger without enthusiasm.

 

[alwayslrN]

It is straight SQL. No VBA

 

[alwayslrN]

I am wondering, if somehow, if a person typed N/A in a date field if that could have damaged the integrity of either Excel spreadsheet or the link to Access?

I just noticed a user entered N/A in Excel in a column that is a date in SQL Server.

 

[jsteph]

Check the date data-type in sql server. If it's smalldatetime, it will not accept the Excel Null date, it sees that as out of range.
-Jim