Truncation of string problem
 

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

Truncation of string problem
 

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

"tdabel" wrote in message
...
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

Truncation of string problem
 

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

Debug.Print
Join(Application.Transpose(Application.Transpose(R ange("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(R ange("A1:M1"))), ", ")
SQLStr = SQLStr & ") VALUES(" &
Join(Application.Transpose(Application.Transpose(R ange("A3:M3"))), ", ")
SQLStr = SQLStr & ")"

NickHK

"tdabel" wrote in message
...
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

Truncation of string problem
 

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

"NickHK" wrote:

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

Debug.Print
Join(Application.Transpose(Application.Transpose(R ange("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(R ange("A1:M1"))), ", ")
SQLStr = SQLStr & ") VALUES(" &
Join(Application.Transpose(Application.Transpose(R ange("A3:M3"))), ", ")
SQLStr = SQLStr & ")"

NickHK

"tdabel" wrote in message
...
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