Hi all.

A database is being tinkered with and we needed a few columns and the data.

So I saved it down to Access and then Excel.

three columns
DateEntered
Date_Expiry
PhotoURL

first two are a datetime format in sql.
third is a varchar.

So now I need to write this back into a database. I have setup a blank db to test on. This is my code:

[vba]
Dim row As Integer
Dim strSQL As String
For row = 2 To 3382
strSQL = "insert into tblTest (DateEntered, DateExpiry, PhotoURL) values ('" & XLSheet.Cells(row, 3).Value & "','" & XLSheet.Cells(row, 4).Value & "','" & XLSheet.Cells(row, 5).Value & "')"
'MsgBox (strSQL)
con.Execute strSQL
Next row

[/vba]
If I set the three columns in SQL as varchar or char, it works fine.

But I need the dates in datetime format. They are formatted correctly in Excel (eg: 01/02/2008)

This is the error I'm getting:

[vba]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
[/vba]
So I tried without quote marks in the vba but I get a syntax error.

Ideas?

Thanks

update -

Actually I'll explain a little more as I've noticed a few things.

Not all the fields have a datestamp on them or a PhotoURL.

It seems to error at line 7. I thought it might be to do with NULLs but that isn't the case. In fact, in my SQL, where the column should be NULL or blank, it has the date - 01/01/1900 in it!

So I don't want that, but I am still getting the above error, even though it is entering the data