a column is not importing date data into a linked sql table

I am importing data from an excel file (Stock_Status_Report.xls) into a linked SQL table in my MS access application.  The linked table is called dbo_Stock_Status_Report.  This SQL table has a field called "Next Delivery Date".  The excel file also has the same field name.  The docmd.transferspreadsheet method imports all of the data except for the date data in the column Next Delivery Date with no errors.  I have no idea why this is happening.  COuld really use expert help.


Sub Excel_Import_Stock()

Dim table1 As String, table2 As String, table3 As String
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim indx As DAO.Index
Dim PrimaryKey As String
Dim qry As String

Dim xlObj As Excel.Application
Dim shtName As String
Dim xlPath As String, xlFile As String
Dim filArr(), j

DoCmd.SetWarnings False

table1 = "dbo_Stock_Status_Report"
table2 = "dbo_On_Hand_Value"

SaveEmailAttachments_Stock    'SAVES OHM E-MAIL EXCEL ATTACHMENTS TO DESKTOP

Excel_Rename_Columns

filArr = Array("Stock_Status_Report.xls", "On_Hand_Value.xls")
xlPath = GetUSERPROFILE() & "\Desktop"
Set xlObj = CreateObject("Excel.Application")

'import the excel file
For j = LBound(filArr) To UBound(filArr)
    xlObj.Workbooks.Open xlPath & "\" & filArr(j)
    shtName = xlObj.Worksheets(1).Name
    If filArr(j) = "Stock_Status_Report.xls" Then
        DoCmd.RunSQL "DELETE * FROM " & table1
        DoCmd.TransferSpreadsheet acImport, , table1, xlPath & "\" & filArr(j), True, shtName & "!"
'    ElseIf filArr(j) = "On_Hand_Value.xls" Then
'        DoCmd.RunSQL "DELETE * FROM " & table2
'        DoCmd.TransferSpreadsheet acImport, , table2, xlPath & "\" & filArr(j), True, shtName & "!"
    End If
Next

For j = LBound(filArr) To UBound(filArr)
    xlObj.Workbooks.Close
    Set xlObj = Nothing
    On Error Resume Next
    Kill xlPath & "\" & filArr(j)    'Delete the Excel files
Next

xlObj.Workbooks.Close
Set xlObj = Nothing

TerminateExcelProcess

'x_DropStockImportErrorsTables

'Add_Current_Date_Time

DoCmd.SetWarnings True

MsgBox "Stock data import complete."

End Sub





Sub Excel_Rename_Columns()

Dim xlObj As Excel.Application
Dim xlPath As String, shtName As String
Dim filArr(), j
Dim i As Integer

DoCmd.SetWarnings False

filArr = Array("Stock_Status_Report.xls")
xlPath = "C:\Users\sanjayg\Desktop"
Set xlObj = CreateObject("Excel.Application")

For j = LBound(filArr) To UBound(filArr)
    xlObj.Workbooks.Open xlPath & "\" & filArr(j)
    shtName = xlObj.Worksheets(1).Name
    For i = 1 To xlObj.Worksheets(shtName).UsedRange.Columns.Count
        If xlObj.Worksheets(shtName).Cells(1, i).Value = "Description" Then
            xlObj.Worksheets(shtName).Cells(1, i).Value = "Desc"
        ElseIf xlObj.Worksheets(shtName).Cells(1, i).Value = "Annual Dep. Usage" Then
            xlObj.Worksheets(shtName).Cells(1, i).Value = "Annual Dep Usage"
        ElseIf xlObj.Worksheets(shtName).Cells(1, i).Value = "Annual Indep. Usage" Then
             xlObj.Worksheets(shtName).Cells(1, i).Value = "Annual Indep Usage"
        End If
    Next
   
    xlObj.Worksheets(shtName).Columns("H:H").NumberFormat = "m/d/yyyy"
   
Next

xlObj.ActiveWorkbook.Save
'xlObj.Workbooks.Close
Set xlObj = Nothing

TerminateExcelProcess

'DoCmd.SetWarnings True

End Sub
sxxguptaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sxxguptaAuthor Commented:
This worked.  Had to save the file first as fileformat = 56.




Sub Excel_Rename_Columns()

Dim xlObj As Excel.Application
Dim xlPath As String, shtName As String
Dim filArr(), j
Dim i As Integer

DoCmd.SetWarnings False

filArr = Array("Stock_Status_Report.xls")
xlPath = "C:\Users\sanjayg\Desktop"
Set xlObj = CreateObject("Excel.Application")

For j = LBound(filArr) To UBound(filArr)
    xlObj.Application.DisplayAlerts = False
    xlObj.Workbooks.Open xlPath & "\" & filArr(j)
    xlObj.ActiveWorkbook.SaveAs xlPath & "\" & filArr(j), FileFormat:=56
Next

Set xlObj = Nothing

TerminateExcelProcess

filArr = Array("Stock_Status_Report.xls")
xlPath = "C:\Users\sanjayg\Desktop"
Set xlObj = CreateObject("Excel.Application")

For j = LBound(filArr) To UBound(filArr)
    xlObj.Workbooks.Open xlPath & "\" & filArr(j)
    shtName = xlObj.Worksheets(1).Name
    For i = 1 To xlObj.Worksheets(shtName).UsedRange.Columns.Count
        If xlObj.Worksheets(shtName).Cells(1, i).Value = "Description" Then
            xlObj.Worksheets(shtName).Cells(1, i).Value = "Desc"
        ElseIf xlObj.Worksheets(shtName).Cells(1, i).Value = "Annual Dep. Usage" Then
            xlObj.Worksheets(shtName).Cells(1, i).Value = "Annual Dep Usage"
        ElseIf xlObj.Worksheets(shtName).Cells(1, i).Value = "Annual Indep. Usage" Then
             xlObj.Worksheets(shtName).Cells(1, i).Value = "Annual Indep Usage"
        End If
    Next
   
    xlObj.Worksheets(shtName).Columns("H:H").NumberFormat = "m/d/yyyy"
   
Next

xlObj.ActiveWorkbook.Save
'xlObj.ActiveWorkbook.SaveAs xlPath & "\" & filArr(j), FileFormat:=56
'xlObj.Workbooks.Close
Set xlObj = Nothing

TerminateExcelProcess

'DoCmd.SetWarnings True

End Sub
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I would assume this is because of the format of the Dates in the Excel sheet.

Try moving the data into a local Access table first, and then see what that column looks like.

In general, it's better to import to a local Access table first, and then use standard SQL to move the data over to SQL Server. This allows you to have much more control over the import, and allows you to detect problems with the data before it gets to the live tables.
0
sxxguptaAuthor Commented:
I have resolved my own question. Thanks.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.