VBA for upload to SQL from Excel stopped working?

Question

So, I frankensteined a bit of code together last week, to upload data from my excel sheet to my database. It worked perfectly. I've come back to it today, and now it's not working... I keep getting the error message:

"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

I think the fact that it WAS working has left me a bit blind to find the problem... So I'd be really grateful if someone with fresh eyes could have a look over it, and see if they can see the problem!

Sub Button1_Click()

    If MsgBox("Are you sure you want to upload this data?", vbYesNo) = vbNo Then Exit Sub

    Dim conn As New ADODB.Connection
    Dim iRowNo, sArtistID, sSaleMonth, sSaleYear As Integer
    Dim sValue As Long
    Dim sSaleroom As String
    Dim sUploaded As Date

    sUploaded = Now()

    With Sheets("DataUpload")

        'Open a connection to SQL Server

        'Skip the header row
        iRowNo = 2

        'Loop until empty cell in CustomerId
        Do Until .Cells(iRowNo, 1) = ""
            sArtistID = .Cells(iRowNo, 1)
            sSaleMonth = .Cells(iRowNo, 2)
            sSaleYear = .Cells(iRowNo, 3)
            sSaleroom = .Cells(iRowNo, 4)
            sValue = .Cells(iRowNo, 5)

            'Generate and execute sql statement to import the excel rows to SQL Server table
            conn.Execute "insert into dbo.Data (ArtistID, SaleMonth, SaleYear, SaleRoom, Value, Uploaded) values ('" & sArtistID & "', '" & sSaleMonth & "', '" & sSaleYear & "', '" & sSaleroom & "', '" & sValue & "', '" & sUploaded & "')"

            iRowNo = iRowNo + 1
        Loop

        MsgBox "Data imported."

        conn.Close
        Set conn = Nothing

    End With


End Sub

My Database:

DataID (int) has Identity Specification on, increment 1. ArtistID, salemonth/year and value are all int, saleroom is char and uploaded is datetime.

Thanks very much!

Obviously, one of the columns in which you are trying to upload data is expecting a datetime and the data you are currently uploading is not recognized as a datetime. So, we need to know which column is expecting a datetime. Is SalesMonth a datetime or maybe SaleYear? Maybe some sample data from the table dbo.Data could be helpful. So, you might want to include some in your question. — Ralph
– Ralph, Commented Mar 14, 2016 at 15:38

zaptask · Accepted Answer · 2016-03-14 15:46:11Z

1

We should look for the problem in the description.

"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

For example you might be using a European date convention dd/mm/yyyy and SQL server is expecting American mm/dd/yyyy. Today we have 14th of March. In that case on Friday we had 11th of March which could be interpreted as 3rd of November. There is no 14th month so you get an error today and it was fine 3 days ago.

Before uploading I do

o_adodb_connection.Execute ("set dateformat dmy")

so that the SQL knows what to expect (SQL Server in my case - I do not know what you use).

edited Mar 14, 2016 at 15:46

answered Mar 14, 2016 at 15:44

zaptask

7072 gold badges8 silver badges19 bronze badges

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

1 Comment

MBt93 Over a year ago

That's it!!! Something so simple, I wouldn't have thought of that... Damn the different date conventions! Thank you so much!

Collectives™ on Stack Overflow

VBA for upload to SQL from Excel stopped working?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related