Hello, I'm trying to use and sql query to insert data into an Access database from an Excel spreadsheet. For some reason, code that was working last week now seems compelled to truncate my string to 255 characters. Here's my code, does anyone see anything wrong? As I said, it was working last week. I even reverted to the same file with no joy.
Sub UploadData()
Dim MyCn As ADODB.Connection
Dim SQLStr As String
Dim counter As Integer
Dim I As Integer
counter = ActiveSheet.UsedRange.Rows.Count
Set MyCn = New ADODB.Connection
MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=H:\Interface Register\DP Interface Register.mdb"
' initialize the counter to be 4 so that it doesn't try to insert the column headers.
For I = 4 To counter
SQLStr = "INSERT INTO interface_master (interface_num,revision_num,contractor,title,description,issue_date,required_date,forecast_date,actual_date,close_date, discipline, status, critical) " _
& "Values ('" & Range("A" & I).Value & "', '" & Range("B" & I).Value & "', '" _
& Range("C" & I).Value & "', '" & Range("D" & I).Value & "', '" & Range("E" & I).Value & "', '" _
& Range("F" & I).Value & "', '" & Range("G" & I).Value & "', '" & Range("H" & I).Value & "', '" _
& Range("I" & I).Value & "', '" & Range("J" & I).Value & "', '" & Range("K" & I).Value & "', '" _
& Range("L" & I).Value & "', '" & Range("M" & I).Value & "')"
On Error Resume Next
MyCn.Execute SQLStr
Next
MyCn.Close
Set MyCn = Nothing
End Sub
I noticed when I previewed the post that the insert line is broken up into two lines, I assure you it is one line in my code so it's not a syntax problem. When I put a watch on the variable, it will truncate to 255. I've even tried making the variable a variant type instead of a string and it still did the same thing!? Is there some weird Excel voodoo magic going on here?
Thanks in advance for any responses, I really appreciate it.
Mike
Sub UploadData()
Dim MyCn As ADODB.Connection
Dim SQLStr As String
Dim counter As Integer
Dim I As Integer
counter = ActiveSheet.UsedRange.Rows.Count
Set MyCn = New ADODB.Connection
MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=H:\Interface Register\DP Interface Register.mdb"
' initialize the counter to be 4 so that it doesn't try to insert the column headers.
For I = 4 To counter
SQLStr = "INSERT INTO interface_master (interface_num,revision_num,contractor,title,description,issue_date,required_date,forecast_date,actual_date,close_date, discipline, status, critical) " _
& "Values ('" & Range("A" & I).Value & "', '" & Range("B" & I).Value & "', '" _
& Range("C" & I).Value & "', '" & Range("D" & I).Value & "', '" & Range("E" & I).Value & "', '" _
& Range("F" & I).Value & "', '" & Range("G" & I).Value & "', '" & Range("H" & I).Value & "', '" _
& Range("I" & I).Value & "', '" & Range("J" & I).Value & "', '" & Range("K" & I).Value & "', '" _
& Range("L" & I).Value & "', '" & Range("M" & I).Value & "')"
On Error Resume Next
MyCn.Execute SQLStr
Next
MyCn.Close
Set MyCn = Nothing
End Sub
I noticed when I previewed the post that the insert line is broken up into two lines, I assure you it is one line in my code so it's not a syntax problem. When I put a watch on the variable, it will truncate to 255. I've even tried making the variable a variant type instead of a string and it still did the same thing!? Is there some weird Excel voodoo magic going on here?
Thanks in advance for any responses, I really appreciate it.
Mike