VBA String being truncated to 255 characters

[mfougere]

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

 

[duGly]

Mike,

When stepping through your code, at which line of your code does the string become truncated?

[red]— Artificial intelligence is no match for natural stupidity.[/red]

 

[mfougere]

Since the insert statement is really just one line, it truncates immediately as you F8 through the line. The value that it gets truncated at is usually the Range("E" & I).Value point, but that's only because that's where the string maxes out at 255. If I remove a bunch of columns and have little to no text in my spreadsheet, the insert statement works perfectly. Also, when I cursor over any of the values, they give the correct value in debug mode, the string simply won't accept them. I'm just confused because I thought a string could be pretty darn huge in VBA.

-Mike

 

[duGly]

Have you checked the value in cell Range("E" & I)? Perhaps there is a rogue value that is wiping out your string. I don't know; just thinking out loud.

[red]— Artificial intelligence is no match for natural stupidity.[/red]

 

[mfougere]

Yes, I just checked the value, and if I take the value down to only a few characters, it will get the full value into the string, but it simply truncates the next value (Range("F" & I).Value) so that once again, the string is only 255 characters long. Bizarre isn't it? I was wondering if there might be a setting somewhere or a reference that might be constraining my string length, but I can't find a thing anywhere except in reference to passing values to a cell in Excel, but that's not what's happening here. I really appreciate your taking the time to look at it though. Any other thoughts?

-Mike

 

[CautionMP]

mfougere,
You probably installed some type of update from Microsoft that has nothing to do with Excel or ADO, but...

You might try opening [tt]interface_master[/tt] as a recordset then add new the new records in your loop.

Sub UploadData()
Dim MyCn As ADODB.Connection
[b]Dim MyRS As New ADODB.Recordset[/b]
...
MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
    "DBQ=H:\Interface Register\DP Interface Register.mdb"
[b]MyRS.Open "SELECT * FROM interface_master;", MyCn, 2[/b]

For I = 4 To counter
[b]  With My.RS
    .Addnew
    .Fields("interface_num") = Range("A" & I).Value
    .Fields("revision_num") = Range("B" & I).Value
    .Fields("contractor") = Range("C" & I).Value
    .Fields("title") = Range("D" & I).Value
    .Fields("description") = Range("E" & I).Value
    .Fields("issue_date") = Range("F" & I).Value
    .Fields("required_date") = Range("G" & I).Value
    .Fields("forecast_date") = Range("H" & I).Value
    .Fields("actual_date") = Range("I" & I).Value
    .Fields("close_date") = Range("J" & I).Value
    .Fields("discipline") = Range("K" & I).Value
    .Fields("status") = Range("L" & I).Value
    .Fields("critical") = Range("M" & I).Value)
    .Update
  End With[/b]
Next I
...
MyRS.Close
Set MyRS = Nothing
...

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[mikej336]

What kind of field is the field defined as in Access? You mention text field a couple of times but not memo.

Text fields are limited to 255 characters and Memo field can be large.

Uncle Mike

 

[mfougere]

Thanks CMP, I'll try that right now. I was thinking of some crazy update messing with me but at my company I have no control over that stuff, it just gets pushed out every couple of weeks. I'll let you know how it goes and thanks again.

-Mike

 

[mfougere]

Hi CMP, I get the following error when I try to step past the .AddNew line.
run-time error '3251' application-defined or object-defined error

Any ideas?

Thanks again for helping me,

Mike

 

[mfougere]

Hey Uncle Mike, I have the fields defined as text fields in the Access Database since this is only intended as a tool to track documents and the real meat of the info will reside at the document level. The problem I'm having is that I can no longer even get my info to the DB since the String variable is being constrained to 255 characters. Again, it wasn't doing this behavior last week and I have 15 or 20 entries in my DB with all the columns filled out that prove (to me anyway) that I'm not actually crazy! Ok, maybe a little...

-Mike (I guess I must be the nephew)

 

[CautionMP]

mfougere,
I forgot to specify a lock type (I've been working with DAO recently), if you don't specify a lock type with ADO the default is read only.

[tt]MyRS.Open "SELECT * FROM interface_master;", MyCn, 2, 3[/tt]

-- OR --​

[tt]MyRS.Open "SELECT * FROM interface_master;", MyCn, adOpenDynamic, adLockOptimistic[/tt]

Sorry about that,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[mfougere]

Excellent, that worked like a charm! Thanks CMP that's a load off my mind. Now I just have to do some error trapping so I can use an update statement if the interface_num is already in the database and I'll be rockin and rollin!
I'm still going to try my old code on my home computer this weekend to see if the truncation problem happens there. I know it's not unheard of, but it's still frustating when something just stops working without any changes on the programming side.
Thanks to all who assisted as well!

-Mike