CODEWITHSUNDEEP
stringvbaconcatenation
Richard Todd
Richard Todd

Reputation: 2486

VBA Concatenate many variables into string

I'm doing a relatively messy SQL string in VBA by concatenating many variables into SQL String and then executing the string in ADO.

As an example here's some code:

Sql = "insert into mydb.dbo.table values ('" & var1 & "','" & var2 & "','" & double1 & "," & double2 & "," & double3 & ")"

I did not want to copy the entire code because frankly doing this for 27 variables might bore someone to death. Anyway the above code I repeat for 27 values in SQL (the SQL table obviously has 27 columns). However after 21 concatenations like this the string does not concatenate any further.

The total string char length is 208 so surely it cannot be the VBA maximum of 252. Is there a maximum number of concated values to enter to a VBA string? Is there some other method I could use to achieve this goal?

Many Thanks

Upvotes: 2

Views: 45388

Answers (3)

Richard Todd
Richard Todd

Reputation: 2486

I did not find an answer to this. I was able to reconstruct the SQL tables to a better design, but the problem still remains with concatenating many variables in one string.

Upvotes: 1

Transformer
Transformer

Reputation: 379

Maximum Size of string in VBA is more 65536 char...depends on application version but in a single line you can not write this much characters.In case of VBA a single line can have maximum 1021 char. so break your line of code.e.g.

sql = "Insert into....................."
sql = sql & "rest of the query"

Upvotes: 2

Dick Kusleika
Dick Kusleika

Reputation: 33145

I'm not sure what you mean by 'does not concatenate any further', but I generally use an array for unwieldy strings.

Dim aSql(1 to 27) As String

aSql(1) = "'" & var1 & "'"
aSql(2) = "'" & var2 & "'"
aSql(3) = double1
aSql(4) = double2
...

adCmd.Execute "INSERT INTO mydb.dbo.table VALUES (" & Join(aSql,",") & ");"

Upvotes: 1

Related Questions