Bunion Bunion - 2 months ago 10
SQL Question

Running SQL Query In Access VBA

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?

Answer

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