2

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

Richard Todd
  • 2,406
  • 5
  • 32
  • 40
  • Bobby Tables aside, it seems to me that this goal would be more easily achieved using an import in SQL instead of an export via VBA. http://stackoverflow.com/questions/3474137/how-to-export-data-from-excel-spreadsheet-to-sql-server-2008-table – mr.Reband Jul 22 '13 at 17:55
  • [link1](http://stackoverflow.com/questions/15384545/how-to-export-spreadsheet-data-into-sqlserver), [link2](http://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm), [link3](http://www.erlandsendata.no/english/index.php?d=envbadacexportado) - and that should be enough - took me less than 2 mnutes to find the links. –  Jul 22 '13 at 18:22
  • copy the entire code so that issue can be identified and please clear what do you mean by "does not concatenate further". – Mechanic Jul 23 '13 at 12:23

3 Answers3

2

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"
Transformer
  • 379
  • 1
  • 8
  • char length is only 208 in total so none of the above should matter, also i tried breaking into separate lines and event two separate strings then consing the strings which didn't work either. – Richard Todd Jul 23 '13 at 08:39
1

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,",") & ");"
Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73
  • This will not work in all languages, e.g. in German doubles are written like "5,33" instead of "5.33" and the comma will f-up the SQL-Command, just saying... – AKDADEVIL Jul 23 '13 at 06:40
  • Think this is the best answer so far, although havn't implemented it yet. I'll let you know. – Richard Todd Jul 23 '13 at 08:38
1

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.

Richard Todd
  • 2,406
  • 5
  • 32
  • 40