Uploading Date Column to ms access from excel using sql vba

Question

I am trying to upload all the columns in excel sheet to msaccess using sql with adodb. Everything works fine except the date columns where some of the rows will have blanks. Suppose If i wanted to upload 5 rows to ms access from excel sheet and any of the date column is blank in the first row, then its throwing me an data type mismatch error. but if i sort the date column in excel by rows with dates being top and blanks being bottom and if i try to upload, it uploads fine.

So my concern is ms access considering the column datatype based on the first row value in excel, even though i declared as proper data type in ms access.

Here is the code i am using

Public Sub Upload_Data_from_excel()
Set cn = CreateObject("ADODB.Connection")
dbPath = ThisWorkbook.Path & "\db.accdb"
dbWb = ThisWorkbook.FullName
dbWs_shtname = "SHEET1"
tbl_name = "TBL_EXCEL_DATA"
scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & ";Jet OLEDB:Database Password=123456"
cn.Open scn

' Delete existing records
ssql = "DELETE * FROM " & tbl_name
cn.Execute ssql

dsh = "[" & dbWs_shtname & "$]"
ssql = "INSERT INTO " & tbl_name & " "
ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh
cn.Execute ssql
Set cn = Nothing
Application.StatusBar = ""

End Sub

any support is much appreciated

I don't understand. NAME2 is blank in both your "working" *and your "non-working" examples, and neither has the "blanks being bottom". Also, what does "not working" mean -- on which line of code do you get the error? Please see "minimal reproducible example". — ashleedawg
– ashleedawg, Commented Jan 24, 2018 at 6:29
In the first scenario, date present in the first row. so ms access considers the whole column as date and uploads without error. but in the second scenario date is blank in the first row and ms access considers the whole column as text and it throws me data type mismatch error while uploading JOINING_DATE FIELD into msaccess. I am not sure, whether ms access considering the column data type based on the first row? — Durgaprasad
– Durgaprasad, Commented Jan 24, 2018 at 6:45

ashleedawg · Accepted Answer · 2018-01-24 07:29:43Z

1

Developers often try to re-write functionality that's already built-in to Access (via either a UI or VBA functions), and as far as I can tell that's the case here.

One line of code will handle your import, including flexibility with issues like you describe.

This code:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
    "TBL_EXCEL_DATA", "C:\tmp-xlimport.xlsm", True, "SHEET1!A:B"

...worked fine for me, error-free, like this:

...and would replace all of your code except for deleting the existing records -- which, personally, I use the clean & simple:

DoCmd.RunSQL ("DELETE FROM TBL_EXCEL_DATA")

...or to avoid the "x records will be deleted" warning:

DoCmd.SetWarnings False
DoCmd.RunSQL ("DELETE FROM TBL_EXCEL_DATA")
DoCmd.SetWarnings True

edited Jan 24, 2018 at 7:29

answered Jan 24, 2018 at 7:24

ashleedawg

21.9k9 gold badges82 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Uploading Date Column to ms access from excel using sql vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related