Running SQL Query In Access VBA

Question

Im trying to run an SQL query in Access VBA but my query is too long to fit in one line

INSERT INTO tblProduct SELECT[SAMPartGrp],[ProductPrefix] ,[ProductCode] ,[pcode1],[pcode2],[SubPart1],[SubPart2],[ProductCodeNew],[ProductDescription],[MadeFrom],[MadeFromDescription],[Field1],[SamFamilySort1],[SamFamilySort2],[SamFamilySort3]
,[SamFamilySort4],[SamFamilySort5],[Grade],[Length],[Thickness],[fWidth],[Factor],[CubicMtrs],[CubicMtrsFull],[Weight(T)],[DrawingFilepath],[PackingFilePath],[EFACSProductGrouping],[BatchSize],[PackSize],[Density],[createdby],[createddate],[ProductType],[reorderpolicy],[EFACSExport],[PreactorExport],[customer],[Obsolete/DoNotUse],[noinminipack],[piecesincrate],[minipackheight],[DimA],[DimB],[DimC],[DimD],[DimE],[DimF],[DimG],[DimH],[DimI],[DimJ],[DimK],[DimL],[DimM],[DimN],[DimO] ,[DimP],[DimQ],[DimR],[DimS],[DimT],[DimU],[DimV],[DimW],[DimX],[DimY],[DimZ],[TolA],[TolB],[TolC],[TolD],[TolE],[TolF],[TolG],[TolH],[TolI],[TolJ],[TolK],[TolL],[TolM],[TolN],[TolO],[TolP],[TolQ],[TolR],[TolS],[TolT],[TolU],[TolV],[TolW],[TolX],[TolY],[TolZ]
  ,[Dimension],[Main],[Saws],[Moulders],[PaintLines],[XCut],[DET],[MitreSaw],[Wrapper],[Blocks]
  ,[HingeRecess],[ShrinkWrap],[CNC],[SW],[ShrinkWrapPackSize] ,[SAMBarCode],[machinedaway],[ExcludeFromPreactorUpload],[UseOtherM3XC],[UseOtherM3XC81],[UseOtherM3MS],[UseOtherM3MS83],[comment],[samtype1],[fsc],[LabelPack],[LabelPiece],[trml],[vtype1],[vtype2],[minipack] ,[profile],[madefromlength],[productchamp],[packtype],[uom],[acumatica],[Cupboard],[AcmtaExport],[ExportedtoAcmta],[PostingClass]
   FROM tblProducts

so it wont run the full query at once, is there a workaround for this?

Unless you run this in a Pass-Through query, you must use the table name that Access knows. It doesn't know `[Genex].[dbo].[tblProducts]`. — Andre, Sep 30 '16 at 14:00
Hi Andrew, tblproducts is now in as a linked table with the same name. — Bunion, Sep 30 '16 at 14:05
You are missing a space after `SELECT`, apart from this it should run. If not, please post your VBA code. — Andre, Sep 30 '16 at 14:09
This is probably very applicable: [How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570) — Andre, Sep 30 '16 at 14:12
Narrow number of the fields thus check if the issue caused by the length. — omegastripes, Sep 30 '16 at 14:29

score 1 · Answer 1 · answered Sep 30 '16 at 13:52

1

SQL doesn't take white space into account, this should run the entire query at once.

I think your problem is that you want to use SELECT INTO with TSQL

See here for more information:

https://msdn.microsoft.com/en-us/library/bb208934(v=office.12).aspx

answered Sep 30 '16 at 13:52

Preston

7,399
8
54
84

If i run this query in SQL server it runs without any issues, i just cant run the whole query in VBA within access as my sql table has too may variables to fit in one line within VBA – Bunion Sep 30 '16 at 13:56
Thats the beauty of Access SQL, it's different from SQL Server :) – Preston Sep 30 '16 at 13:59
Does this still affect me even tho my tables are linked SQL 2008 tables? – Bunion Sep 30 '16 at 14:02
@Bunion, I would imagine if you're running the query in Access then yes. Don't be put off by length though, there's no maximum for Access SQL. Try the select into Query. – Preston Sep 30 '16 at 14:24
@Bunion i say no maximum, it's 64k characters. Source http://stackoverflow.com/questions/7699554/maximum-length-of-a-sql-query-in-microsoft-access-2010 – Preston Sep 30 '16 at 14:24

score 1 · Accepted Answer · answered Sep 30 '16 at 14:08

1

You have several issues.

One is that SQL server and Access SQL are not the same. Access SQL is a lot more limited, so just because an SQL query runs on the SQL server does not mean it will run in Access. To run SQL Server queries that are not Access SQL compatible you have to use a pass-through query.

The other issue is that Access table names and SQL server table names are no necessarily the same.

Now, assuming you have taken all that into account and your query is actually Access SQL compatible, you can run it like this:

Dim sql as String
sql = "Query part A"
sql = sql & "Query Part B"
... repeat as necessary
DoCmd.RunSQL sql

answered Sep 30 '16 at 14:08

SunKnight0

3,331
1
10
8

Exactly what i was looking for! the only problem is data mismatch in criteria expression.. :( – Bunion Sep 30 '16 at 14:29
Since your query does not have any actual criteria, two things are possible, either in your string concatenations you need a space, for example `"SELECT Field FROM" & "table"` should be `"SELECT Field FROM " & "table"`, or the order (or data type) of the fields on one table are not exactly the same on the other, in which case you have to actually list the receiving fields in your query and/or perform some type conversions. – SunKnight0 Sep 30 '16 at 14:53

score 1 · Answer 3 · answered Sep 30 '16 at 18:15

Are you just talking about wraparound formatting, where you use the "& _" to continue your string?

strSQL = "SELECT [SAMPartGrp],[ProductPrefix] ,[ProductCode] ,[pcode1], " & _
   "[pcode2], [SubPart1],[SubPart2],[ProductCodeNew],[ProductDescription], " & _
   "[MadeFrom], [MadeFromDescription],[Field1],[SamFamilySort1], " & _
   "[SamFamilySort2],[SamFamilySort3],[SamFamilySort4], " & _
   "[SamFamilySort5], [Grade], "

score 0 · Answer 4 · 2017-10-27T20:49:31.027

Try this.

Dim strSQL as String

strSQL = "INSERT INTO tblProduct SELECT[SAMPartGrp],[ProductPrefix] , " & _ 
         "[ProductCode] ,[pcode1],[pcode2],[SubPart1],[SubPart2],[ProductCodeNew], " & _
         "[ProductDescription],[MadeFrom],[MadeFromDescription],[Field1], " & _
         "[SamFamilySort1],[SamFamilySort2],[SamFamilySort3], [SamFamilySort4], " _
         "[SamFamilySort5],[Grade],[Length],[Thickness], [fWidth],[Factor], " & _
         "[CubicMtrs],[CubicMtrsFull],[Weight(T)],[DrawingFilepath], " & _ 
         "[PackingFilePath],[EFACSProductGrouping],[BatchSize], " & _                                         
         "[PackSize],[Density],[createdby],[createddate],[ProductType], " & _
         "[reorderpolicy],[EFACSExport],[PreactorExport],[customer], " & _
         "[Obsolete/DoNotUse],[noinminipack],[piecesincrate], [minipackheight], " & _
         "[DimA],[DimB],[DimC],[DimD],[DimE],[DimF],[DimG],[DimH], " & _ 
         "[DimI],[DimJ],[DimK],[DimL],[DimM],[DimN],[DimO] ,[DimP], " &_
         "[DimQ],[DimR],[DimS],[DimT],[DimU],[DimV],[DimW],[DimX], " & _
         "[DimY],[DimZ],[TolA],[TolB],[TolC],[TolD],[TolE],[TolF], " & _       
         "[TolG],[TolH],[TolI],[TolJ],[TolK],[TolL],[TolM],[TolN], " & _
         "[TolO],[TolP],[TolQ],[TolR],[TolS],[TolT],[TolU],[TolV], " & _
         "[TolW],[TolX],[TolY],[TolZ],[Dimension],[Main],[Saws], " &_
         "[Moulders],[PaintLines],[XCut],[DET],[MitreSaw],[Wrapper], " &_
         "[Blocks],[HingeRecess],[ShrinkWrap],[CNC],[SW], " & _
         "[ShrinkWrapPackSize] ,[SAMBarCode],[machinedaway], " & _
         "[ExcludeFromPreactorUpload],[UseOtherM3XC],[UseOtherM3XC81], " & _
         "[UseOtherM3MS],[UseOtherM3MS83],[comment],[samtype1],[fsc], " & _
         "[LabelPack],[LabelPiece],[trml],[vtype1],[vtype2],[minipack] , " & _
         "[profile],[madefromlength],[productchamp],[packtype],[uom], " & _
         "[acumatica],[Cupboard],[AcmtaExport],[ExportedtoAcmta], " & _
         "[PostingClass] FROM tblProducts;"
DoCmd.RunSQL strSQL

In VBA you cannot concatenate lines inside of strings. https://stackoverflow.com/questions/16624550/how-to-break-long-string-to-multiple-lines — Andre, Oct 27 '17 at 13:43
Why do you have to downvote? The & just got missed due to my fast typing plus I am new here. I guarantee you that will work. It worked in most of my codes. — , Oct 27 '17 at 21:19
Your original answer was simply wrong. -- Also, it doesn't help anyone if you add answers to old, already answered questions, if you don't add anything new to the existing answers. You are just wasting your time and ours (because the questions get bumped to the top). — Andre, Oct 28 '17 at 06:51

Running SQL Query In Access VBA

4 Answers4