Access Insert into Table

Jojo69 · #1 (**permalink**) 8th February 2010, 07:51 PM

------ Register to get rid of these "In Post" ads! ------

Hi,

I have the following code to insert into a table

VBA CODE:

Sub RunQry() 
     
    Dim StrSql As String 
     
    StrSql = "SELECT AllocExpQueryRedoneMH.CategoricalId, AllocExpQueryRedoneMH.CAU, AllocExpQueryRedoneMH.Allocation, ExpenditureSumMH.SumOfExpenditure, [allocation]-ExpenditureSumMH.sumofExpenditure AS Carryover, CodesCategoricalLineItemsMH.CategoricalDescription, Mid([CAUDescription],1,InStr([CAUDescription],'" & MH & "')-2) AS County, CodesRegionOrderBy.RegionOrder into MHAllocationsExpenditures0809" _ 
    & "FROM (((AllocExpQueryRedoneMH INNER JOIN CodesCAU ON AllocExpQueryRedoneMH.CAU = CodesCAU.CAU) LEFT JOIN ExpenditureSumMH ON (AllocExpQueryRedoneMH.CAU = ExpenditureSumMH.CAU) AND (AllocExpQueryRedoneMH.CategoricalId = ExpenditureSumMH.CategoricalID)) LEFT JOIN CodesCategoricalLineItemsMH ON AllocExpQueryRedoneMH.CategoricalId = CodesCategoricalLineItemsMH.ID) INNER JOIN CodesRegionOrderBy ON CodesCAU.MHRegionCode = CodesRegionOrderBy.RegionCode" _ 
    & "GROUP BY AllocExpQueryRedoneMH.CategoricalId, AllocExpQueryRedoneMH.CAU, AllocExpQueryRedoneMH.Allocation, ExpenditureSumMH.SumOfExpenditure, [allocation]-ExpenditureSumMH.sumofExpenditure, CodesCategoricalLineItemsMH.CategoricalDescription, Mid([CAUDescription],1,InStr([CAUDescription],'" & MH & "')-2), CodesRegionOrderBy.RegionOrder" _ 
    & "ORDER BY CodesRegionOrderBy.RegionOrder;" 
     
     
    With DoCmd 
        .SetWarnings False 
        .RunSQL StrSql 
        .SetWarnings True 
    End With 
     
     
End Sub

I get an error (runtime error 3067) Query input must contain at least one input table or query

Could someone please help me. Thank you

Ijaz

Simon Lloyd · #2 (**permalink**) 8th February 2010, 10:04 PM

------ Register to get rid of these "In Post" ads! ------

I'm no good on Access, however, i believe your tables should be wrapped in square brackets eg.

VBA CODE:

"SELECT [AllocExpQueryRedoneMH.CategoricalId],............etc

i would double check you concatenation too!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)