ADO Recordset into local access table, null problem

[MikeAuz1979]

Hi,

Using acess 2002 I'm sucessfully running an ado recordset and inserting it into a local access table until the recordset fields have null results. The trouble is that the string that I'm building (sqlIns) converts the nulls to empty strings as such and so I'm getting a syntax error with the insert.

I could put something like 'if isnull(rst!fieldName) then rst!fieldname = "Null" else rst!fieldname' but this seems very cumbersome.

anyone have any ideas?

Thanks for your help
Mike

Public Function tblNonComReport()
Dim sqlRet As String, sqlIns As String
Dim rst As New ADODB.Recordset
Dim Con As New ADODB.Connection
Dim strDate As String

strDate = InputBox("Please enter the date of the monday after the list was generated, in dd/mm/yyyy format.", "Input Date")
If Not IsDate(strDate) Then
    MsgBox "Invalid Date"
    Exit Function
End If
strDate = Format(strDate, "yyyy-mmm-dd")

If IsNull(strDate) Then Exit Function

CurrentDb.Execute "Delete * from tblNonComReport"

sqlRet = "SELECT CRN.[dtmCalMondayStart],CRN.[intPKState],CRN.[lngFKProductNo],CRN.[idsPKCompetitor],CRN.[chrStateName]" & _
",CRN.[chrProductName],CRN.[lngFKFineNo],CRN.[chrFineName],CRN.[lngSubSectionNo],CRN.[chrSubSectionName]" & _
",CRN.[intStoresRanged],CRN.[lngCostUnit],CRN.[lngWBuyCost],CRN.[chrCompetitorsName],CRN.[chrCompetitorLocation]" & _
",CRN.[chrFKBuyDeptNo],CRN.[idsPKPrices],CRN.[idsPKTradingDeptNo],CRN.[idsPkSeniorBusMgr],CRN.[chrFKGenericIndicator]" & _
",RTrim(CRN.[blnMajorLine]),RTrim(CRN.chrBMName) as chrBMName,CRN.[chrSeniorBusinessManagerName],CRN.[chrTradingDepartmentName]" & _
",CRN.[chrMajorLine],CRN.[chrDescription],CRN.[intCompSize],CRN.[chrCompMeasure],CRN.[intWWSize],CRN.[chrWWMeasure]" & _
",CRN.[blnComparable],CRN.[idsFKCheckType],CRN.[intAlternatePrice],CRN.[chrAlternateComment],CRN.[chrCheckName]" & _
",CRN.[intSPG] as SPG,CRN.[lngSaleCompPrice],CRN.[idsPKCompetitorLocationNo],CRN.wg1,twwcr.intOrder" & _
",CRN.[chrComBrand] + ' ' + CRN.[chrCompProductDescription] as CompDesc" & _
",(case CRN.idsPKCompetitorLocationNo when 142 then 10 when 23 then 6 when  144 then 5 when 145 then 5 end) as intSPG" & _
",(case CRN.idsPKCompetitorLocationNo when 142 then CRN.cg10 when 23 then CRN.cg6 when 144 then CRN.cg5 when 145 then CRN.cg5" & _
" end) as WOW_SPG_Sell,CRN.CG1/100 as Comp_Sell" & _
" FROM [CPM].[dbo].[tblCPMReportNew] CRN, CPM.dbo.tblWWCompetitorRelation twwcr" & _
" Where [dtmCalMondayStart] = '" & strDate & "' AND twwcr.[idsFKCompetitorLocationNo] = CRN.idsPKCompetitorLocationNo" & _
" AND twwcr.intOrder = 1 AND CRN.CG1 <> 0 AND CRN.idsFKCheckType = 9"

Con.ConnectionTimeout = 400
Con.CommandTimeout = 400
Con.Open "Provider=SQLOLEDB;Data Source=abbbcdAD03fy002;Initial Catalog=CPM;User Id=xxxx;Trusted_Connection=Yes"
rst.Open sqlRet, Con, adOpenStatic, adLockOptimistic

Do While Not rst.EOF
    sqlIns = "INSERT INTO tblNonComReport (dtmCalMondayStart, intPKState, lngFKProductNo, idsPKCompetitor, chrStateName," & _
    "chrProductName , lngFKFineNo, chrFineName,lngSubSectionNo, chrSubSectionName, intStoresRanged, lngCostUnit, lngWBuyCost," & _
    "chrCompetitorsName, chrCompetitorLocation, chrFKBuyDeptNo, idsPKPrices, idsPKTradingDeptNo, idsPkSeniorBusMgr, chrFKGenericIndicator," & _
    "blnMajorLine,chrBMName, chrSeniorBusinessManagerName, chrTradingDepartmentName, chrMajorLine, chrDescription, intCompSize," & _
    "chrCompMeasure, intWWSize, chrWWMeasure, blnComparable, idsFKCheckType, intAlternatePrice, chrAlternateComment, chrCheckName," & _
    "SPG , lngSaleCompPrice, idsPKCompetitorLocationNo, wg1, intOrder, CompDesc, intSPG, WOW_SPG_Sell, Comp_Sell)" & _
    " values ('" & rst!dtmCalMondayStart & "'," & rst!intPKState & "," & rst!lngFKProductNo & "," & rst!idsPKCompetitor & ",'" & _
    rst!chrStateName & "','" & rst!chrProductName & "'," & rst!lngFKFineNo & ",'" & rst!chrFineName & "'," & _
    rst!lngSubSectionNo & ",'" & rst!chrSubSectionName & "'," & rst!intStoresRanged & "," & rst!lngCostUnit & "," & rst!lngWBuyCost & ",'" & _
    rst!chrCompetitorsName & "','" & rst!chrCompetitorLocation & "','" & rst!chrFKBuyDeptNo & "'," & rst!idsPKPrices & "," & rst!idsPKTradingDeptNo & "," & _
    rst!idsPkSeniorBusMgr & ",'" & rst!chrFKGenericIndicator & "'," & rst!blnMajorLine & ",'" & rst!chrBMName & "','" & rst!chrSeniorBusinessManagerName & "','" & _
    rst!chrTradingDepartmentName & "'," & rst!chrMajorLine & ",'" & rst!chrDescription & "'," & rst!intCompSize & "," & rst!chrCompMeasure & "," & _
    rst!intWWSize & "," & rst!chrWWMeasure & "," & rst!blnComparable & "," & rst!idsFKCheckType & "," & rst!intAlternatePrice & "," & _
    rst!chrAlternateComment & ",'" & rst!chrCheckName & "'," & LTrim(rst!SPG) & "," & rst!lngSaleCompPrice & "," & rst!idsPKCompetitorLocationNo & "," & _
    rst!wg1 & "," & rst!intOrder & ",'" & rst!CompDesc & "'," & rst!intSPG & "," & rst!WOW_SPG_Sell & "," & rst!Comp_Sell & ")"
    CurrentDb.Execute sqlIns
    rst.MoveNext
Loop

Con.Close
Set rst = Nothing

End Function

 

[JerryKlmns]

Let's say that rst!CompDesc is null, the following is not null
rst!CompDesc & ""

But I would suggest that you open a new server-side forwardonly, optimistic-locking DAO recordset to add new records to your mdb during the loop.

There was an excercise couple months ago that a DAO recordset beated to death
CurrentDB.Execute method
CurrentProject.ConnectionExecute method
ADO recordset AddNew with a loop