application defined or object defined error

[ISUTri]

I have an access database that has been running with no problems (and no changes) since September 05. Today it started getting the following error:

34464 Application Defined or Object Defined Error.

This is erroring out on a SQL Coded Append query that is in VBA. There is an append query (Sql as well) that runs after this one, appends to the same table, and it works. Any suggestions on how to fix this would be greatly appreciated.

Thanks

 

[PHV]

Repair the database ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ISUTri]

Nope didn't work. Any other ideas?

 

[MajP]

Post the query. If you have some functions in this query, could have dropped a reference. Did you compile?

 

[ZmrAbdulla]

Are you sure the fields defined in the VBA SQL is correct? Spelling mistakes on field names will bring error.

________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.

 

[ISUTri]

Below is the code. It's building a sql statement to run in a querydef. There are 2 queries that insert data into the same table. The first query errors off (the one below). The 2nd works just fine. I'm going to try and import everything into a new access database... but other than that I'm running out of ideas.

Hope this doesn't look to ugly:

strsql = "INSERT INTO tblWorkLoadRpt ( TeamRegion, CarrType, RptDay, Shift, PartType, Lines, BinPicks, TitleDates, TitleDteCnt ) "
strsql = strsql & "SELECT DISTINCTROW GetRegion(tblDckScd.TeamRegion, GetCarrCde(tblDailyOrderHeader.SuggCarrCde)), GetCarrType([tblDailyOrderHeader].[SuggCarrCde],[tblDailyOrderHeader].[CarrType]) AS CarrType, '" & intRptDay & "' AS RptDay, GetShift([DayNightInd]) AS Shift, funChooseDispGroup([tblDispatchGroup].[DispatchGroupNbr],[tblDailyOrderHeader].[ShipDte],[tblDispatchGroup].[DispatchGroupDesc]) AS PartType, Sum(GetLines([tblPartInfo].[PartType],[tblDailyOrderDetail].[AllocQty],[tblPartInfo].[DistPackQty])) AS Lines, Sum(GetBinPicks([tblDailyOrderDetail].[AllocQty],[tblPartInfo].[DistPackQty])) AS BinPicks, '" & TitleDates & "' as TitleDates, " & TitleDteCnt & " as TitleDteCnt "
strsql = strsql & "FROM (((tblDailyOrderHeader LEFT JOIN tblDailyOrderDetail ON tblDailyOrderHeader.OrdNbr = tblDailyOrderDetail.OrdNbr) LEFT JOIN tblDckScd ON GetCarrCde(tblDailyOrderHeader.SuggCarrCde) = (tblDckScd.CarrCde)) LEFT JOIN tblDispatchGroup ON left(tblDailyOrderDetail.DisptchNbr,1) = tblDispatchGroup.DispatchGroupNbr) LEFT JOIN tblPartInfo ON tblDailyOrderDetail.PartNbr = tblPartInfo.PartNbr "
strsql = strsql & "WHERE ((tblDailyOrderHeader.ShipDte In(" & TitleDates & ")) AND (tblDailyOrderHeader.CustType In ('FDLR','SGAP','CSAT')) AND (tblDailyOrderHeader.OrdType In ('A','B','C','D','K')) and (tblDailyOrderHeader.ShipDirectInd = 'N') and (GetCarrType([tblDailyOrderHeader].[SuggCarrCde],[tblDailyOrderHeader].[CarrType]) = 'D') and (tblDckScd.DockDay = '" & intRptDay & "' or tblDckScd.DockDay = '%' or tblDckScd.DockDay Is Null)) "
strsql = strsql & "GROUP BY GetRegion(tblDckScd.TeamRegion, GetCarrCde(tblDailyOrderHeader.SuggCarrCde)), GetCarrType([tblDailyOrderHeader].[SuggCarrCde],[tblDailyOrderHeader].[CarrType]), " & intRptDay & ", GetShift([DayNightInd]), funChooseDispGroup([tblDispatchGroup].[DispatchGroupNbr],[tblDailyOrderHeader].[ShipDte],[tblDispatchGroup].[DispatchGroupDesc]);"

 

[MajP]

I see a lot of user defined functions (GetShift, GetCarrType, GetRegion, funChooseDispGroup, etc.) Test each on of these outside of this code. I still think that is where your error is. Do a compile to see if there is a reference problem.

 

[MajP]

Obviously another thing you can do is test this in parts. Basically you already divided this into a "Insert Into","Select,From,Join", "Where" and "Group By" clauses. You could run the "Select,From,Join" portion up to the "Where" and see if that works without error, then add the "Where" clause, then add the "Group by clause", then add the "Insert into".

 

[ZmrAbdulla]

Are you not missing keyword VALUES in the first SQL?

INSERT INTO
    tblWorkLoadRpt 
  (
   TeamRegion
  , CarrType
  , RptDay
  , Shift
  , PartType
  , Lines
  , BinPicks
  , TitleDates
  , TitleDteCnt 
  )
[b]VALUES ([/b]
SELECT DISTINCT
    GetRegion(tblDckScd.TeamRegion
  , GetCarrCde(tblDailyOrderHeader.SuggCarrCde))
  , GetCarrType([tblDailyOrderHeader].[SuggCarrCde],[tblDailyOrderHeader].[CarrType]) AS CarrType
  , '" & intRptDay & "' AS RptDay
  , GetShift([DayNightInd]) AS Shift
  , funChooseDispGroup([tblDispatchGroup].[DispatchGroupNbr],[tblDailyOrderHeader].[ShipDte],[tblDispatchGroup].[DispatchGroupDesc]) AS PartType
  , Sum(GetLines([tblPartInfo].[PartType],[tblDailyOrderDetail].[AllocQty],[tblPartInfo].[DistPackQty])) AS Lines
  , Sum(GetBinPicks([tblDailyOrderDetail].[AllocQty],[tblPartInfo].[DistPackQty])) AS BinPicks
  , '" & TitleDates & "' as TitleDates
  , " & TitleDteCnt & " as TitleDteCnt [b])[/b]

________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.