CODEWITHSUNDEEP
sqlvbams-access
Bunion
Bunion

Reputation: 461

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?

Upvotes: 1

Views: 7446

Answers (4)

user8244044
user8244044

Reputation:

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

Upvotes: 0

geeFlo
geeFlo

Reputation: 375

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], "

Upvotes: 1

SunKnight0
SunKnight0

Reputation: 3351

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

Upvotes: 1

Preston
Preston

Reputation: 8197

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

Upvotes: 1

Related Questions