CODEWITHSUNDEEP
sqlsql-serverexcelvba
rplatt
rplatt

Reputation: 9

SQL command in VBA column limitation

Is there a limitation of how many columns can be brought over in one sqlcommand in VBA code? My code is quite long but is used to ensure that I am pulling from the right database at all times. The m2mdata02 will be replaced with code CatalogM2M upon a user's selection in the database. The total number of columns to bring over is 23 columns from 7 tables.

Sqlcommand = “select left(m2mdata02.dbo.jomast.fjobno,5) as job, m2mdata02.dbo.jomast.fjobno, m2mdata02.dbo.jomast.fpartno, m2mdata02.dbo.jomast.fstatus, m2mdata02.dbo.jomast.fact_rel, m2mdata02.dbo.jomast.fddue_date, m2mdata02.dbo.jomast.fprodcl, m2mdata02.dbo.jomast.frel_dt, m2mdata02.dbo.jomast.frouting, m2mdata02.dbo.inmastx.fpartno, m2mdata02.dbo.inmastx.fdescript, m2mdata02.dbo.inmastx.fprice, m2mdata02.dbo.somast.fsono, m2mdata02.dbo.somast.fcompany, m2mdata02.dbo.somast.fcustpono, m2mdata02.dbo.aritem.fcinvoice, m2mdata02.dbo.aritem.fprice, m2mdata02.dbo.aritem.fcsono, m2mdata02.dbo.armast.fcinvoice, m2mdata02.dbo.armast.finvdate, m2mdata02.dbo.shmast.fshipdate, m2mdata02.dbo.shmast.fshipno, m2mdata02.dbo.shmast.fcsono
from m2mdata02.dbo.jomast
left join m2mdata02.dbo.inmastx on m2mdata02.dbo.inmastx.fpartno = m2mdata02.dbo.jomast.fpartno
left join m2mdata02.dbo.somast on m2mdata02.dbo.somast.fsono = m2mdata02.dbo.jomast.fsono 
left join m2mdata02.dbo.aritem on m2mdata02.dbo.aritem.FCSONO = m2mdata02.dbo.jomast.fsono
left join m2mdata02.dbo.armast on m2mdata02.dbo.armast.fcinvoice = m2mdata02.dbo.aritem.fcinvoice
left join m2mdata02.dbo.shmast on m2mdata02.dbo.shmast.fcsono = m2mdata02.dbo.somast.fsono
where m2mdata02.dbo.jomast.fprodcl = 'FG11' order by m2mdata02.dbo.jomast.fjobno”

Upvotes: 1

Views: 224

Answers (1)

Parfait
Parfait

Reputation: 107687

According to MSDN docs, the character limit of String type in VBA is approximately two billion (2^31) Unicode characters. Your SQL query is no where near that limitation. And if using ADO to connect Excel VBA to database, the server engine here being SQL Server decides column limitation and 23 is again no where near maximum capacity.

However, consider using table aliases to cut down on characters, avoid repeating triple period identifiers, and overall improve readability even maintainability as you can adjust table reference easier for each user selection.

select left(j.fjobno,5) as job
       , j.fjobno
       , j.fpartno
       , j.fstatus
       , j.fact_rel
       , j.fddue_date
       , j.fprodcl
       , j.frel_dt
       , j.frouting
       , i.fpartno
       , i.fdescript
       , i.fprice
       , so.fsono
       , so.fcompany
       , so.fcustpono
       , ari.fcinvoice
       , ari.fprice
       , ari.fcsono
       , arm..fcinvoice
       , arm..finvdate
       , sh.fshipdate
       , sh.fshipno
       , sh.fcsono 
       
from m2mdata02.dbo.jomast j
left join m2mdata02.dbo.inmastx i on i.fpartno = j.fpartno 
left join m2mdata02.dbo.somast so on so.fsono = j.fsono 
left join m2mdata02.dbo.aritem ari on ari.FCSONO = j.fsono 
left join m2mdata02.dbo.armast arm on arm..fcinvoice = ari.fcinvoice 
left join m2mdata02.dbo.shmast sh on sh.fcsono = so.fsono 
where j.fprodcl = 'FG11' 
order by j.fjobno

By the way, for very long queries, avoid the need to build string in VBA with line concatenation and double quotes, and read directly from a formatted SQL text file:

Dim strSQL As String

' READ SQL QUERY FROM FILE
With CreateObject("Scripting.FileSystemObject")
      strSQL = .OpenTextFile("C:\path\to\my\SQL\Query.sql", 1).readall
End With

' REPLACE DB IN STRING
strSQL = Replace(strSQL, "m2mdata02", "someotherdb")

Upvotes: 3

Related Questions