Truncation of string problem

[Guest]

Good day all.

I am having a problem with the creation of a SQL string.

I am attempting to upload rows of data to an Access database and need a SQL
command in the following format:

insert into tablename
(column1name,column2name...columnxname)
values (value1,value2...valuex);

The header row (ColumnNames) of my Excel sheet contains the following fields:

resultsFields = " (" & "Date," & "Auditor," & "Shift," & "Product Grade," &
"Ticket #," & "Product #," _
& "Ticket Pieces," & "Ticket Sq Footage," & "Broken
Corner," & "Broke Edge," _
& "Splintered Edge," & "Min/Stain," & "End Overwood," &
"Side Overwood," & "Delam," _
& "Bleed Back," & "Brush Marks," & "Stain Marks," &
"Light/Dark Bevels," _
& "Blisters/Bubbles," & "Trash in Finish," &
"Stain/Filler Pop," & "Stain Skips," _
& "UV Lines," & "Roller Marks," & "UV Skips," &
"Indents," & "Shelly Grain/Shake," _
& "Splits in Veneer," & "Open Checks," & "Hull Out," &
"Open Grain," & "White Grain," _
& "Burnt Bevel/ Edge," & "Bevel Size," & "Sand Dip," &
"Moulder Line," & "Sanding Line," _
& "Chatter," & "Off Square," & "Sander Burn," & "Crush
Groove," & "Raised Checks," _
& "Knots," & "Worm Holes," & "Pin Holes," & "End Lift," &
"Bowed," & "Narrow End," _
& "Core Void," & "Sweep," & "Bark Pocket," & "Misc. /
Other," & "Ticket Pcs out of spec," _
& "Ticket % out of spec," & "Comments)"

I have also attempted to store the fields without concatenation.

The problem that I have is the resultsFields variable only contains:

" (Date,Auditor,Shift,Product Grade,Ticket #,Product #,Ticket Pieces,Ticket
Sq Footage,Broken Corner,Broke Edge,Splintered Edge,Min/Stain,End
Overwood,Side Overwood,Delam,Bleed Back,Brush Marks,Stain Marks,Light/Dark
Bevels,Blisters/Bubbles,Trash in Fl"

The String data type is states it can handle up to 2^31 characters:

There are two kinds of strings: variable-length and fixed-length strings.

A variable-length string can contain up to approximately 2 billion (2^31)
characters.
A fixed-length string can contain 1 to approximately 64K (2^16) characters.

I hope someone can provide me insight to my problem.

Thank you in advance for your assistance.

Sincerely,

Tony D. Abel

 

[NickHK]

Strings can certainly hold that amount of text. I assume you have
Dim resultsFields As String

From the code you have posted there is no reason for the truncation;
something else must be happening.

As an aside, I would avoid using spaces, % and other characters apart from
an underscore "_" in field names. Whilst this is allowed in Access, other
DBs, engines and providers may have problems with them.

NickHK

 

[NickHK]

If you want all the ColumnNames, you can simplify building the SQL string
with:

Debug.Print
Join(Application.Transpose(Application.Transpose(Range("A1:M1"))), ", ")

Similarly for your values, assuming they come from a comparable row of cells
and do not require delimiters of "" or ##. e.g

Dim SQLStr As String
Const TableName As String = "YourTable"

SQLStr = "INSERT INTO " & TableName
SQLStr = SQLStr & " (" &
Join(Application.Transpose(Application.Transpose(Range("A1:M1"))), ", ")
SQLStr = SQLStr & ") VALUES(" &
Join(Application.Transpose(Application.Transpose(Range("A3:M3"))), ", ")
SQLStr = SQLStr & ")"

NickHK

 

[Guest]

Good day Nick.

I have solved my problem with a less elegant solution but it works. I will
try your suggestion on my next project.

Again good sir, I thank you for your response.

Sincerely,

Tony D. Abel