Hi AWF,
I've read and tried to understand various topics regarding Access bloating on AWF and via Google, yet fail to come to grips on how to deal with it in my particular problem.
Let me explain: a 3rd party programmer has provided a lengthy code to process (even lengthier) xml messages into various Access tables. Each segment can end up in a different table. These messages contain large numbers of records and segments. An average XML has about 3000 records and is sized approx 10mb.
After processing one XML file, the Access database has increased approx....400mb! (imagine trying to process 5xmls)
Certainly there's the option of autocompacting but I want to avoid the bloat rather than fixing it afterwards.
If read about using different recordsets (ie DAO instead of ADODB) but haven't got any real confirmation if this is the real solution.
Therefore my question: what should be coded differently to avoid the bloating??
This is the code (sorry for the length, I've already shortened it by removing simialer code-pieces for similar xml segments):
I've read and tried to understand various topics regarding Access bloating on AWF and via Google, yet fail to come to grips on how to deal with it in my particular problem.
Let me explain: a 3rd party programmer has provided a lengthy code to process (even lengthier) xml messages into various Access tables. Each segment can end up in a different table. These messages contain large numbers of records and segments. An average XML has about 3000 records and is sized approx 10mb.
After processing one XML file, the Access database has increased approx....400mb! (imagine trying to process 5xmls)
Certainly there's the option of autocompacting but I want to avoid the bloat rather than fixing it afterwards.
If read about using different recordsets (ie DAO instead of ADODB) but haven't got any real confirmation if this is the real solution.
Therefore my question: what should be coded differently to avoid the bloating??
This is the code (sorry for the length, I've already shortened it by removing simialer code-pieces for similar xml segments):
Code:
Public Function OnixImport()
Dim mn As MSXML2.DOMDocument60, xmlist As MSXML2.IXMLDOMNodeList
Dim Node2 As MSXML2.IXMLDOMNode
Dim Node1 As MSXML2.IXMLDOMNode
Dim varRecordReference, varCollectionType, varColTitleType, varFeatureType, varTextType, varTitleID, varPriceID, varIDRelatedProducts, varIDPartOfCollection, varIDSupportingResource, varTitleType, varRFType, VarUpdated
Dim xDoc As MSXML2.DOMDocument60
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rstMeasure As New ADODB.Recordset
Dim rstTitle As New ADODB.Recordset
Dim rstTitleElement As New ADODB.Recordset
Dim rstPartOfCollection As New ADODB.Recordset
Dim rstTitleDetail As New ADODB.Recordset
Dim rstContributor As New ADODB.Recordset
Dim rstTextContent As New ADODB.Recordset
Dim rstSupportingResource As New ADODB.Recordset
Dim rstXMLMessageHeaders As New ADODB.Recordset
Dim rstProductPart As New ADODB.Recordset
Dim rstEpubTechnicalProtection As New ADODB.Recordset
Dim rstProductClassification As New ADODB.Recordset
Dim rstSubject As New ADODB.Recordset
Dim rstImprint As New ADODB.Recordset
Dim rstPrice As New ADODB.Recordset
Dim rstSalesRestriction As New ADODB.Recordset
Dim rstProductAvailability As New ADODB.Recordset
Dim rstSupplyDetail As New ADODB.Recordset
Dim rstRelatedProducts As New ADODB.Recordset
Dim rstProductIdentifier As New ADODB.Recordset
Dim rstCitedContent As New ADODB.Recordset
Dim rstLanguage As New ADODB.Recordset
Dim rstResourcesFeature As New ADODB.Recordset
Dim rstTax As New ADODB.Recordset
Set cnn = CurrentProject.Connection
rst.Open "SELECT * FROM tblProducts", cnn, adOpenDynamic, adLockOptimistic
rstMeasure.Open "SELECT * FROM tblMeasure", cnn, adOpenDynamic, adLockOptimistic
rstTitle.Open "SELECT * FROM tblTitle", cnn, adOpenDynamic, adLockOptimistic
rstTitleElement.Open "SELECT * FROM tblTitleElement", cnn, adOpenDynamic, adLockOptimistic
rstPartOfCollection.Open "SELECT * FROM tblPartOfCollection", cnn, adOpenDynamic, adLockOptimistic
rstTitleDetail.Open "SELECT * FROM tblTitleDetail", cnn, adOpenDynamic, adLockOptimistic
rstContributor.Open "SELECT * FROM tblContributor", cnn, adOpenDynamic, adLockOptimistic
rstTextContent.Open "SELECT * FROM tblTextContent", cnn, adOpenDynamic, adLockOptimistic
rstSupportingResource.Open "SELECT * FROM tblSupportingResource", cnn, adOpenDynamic, adLockOptimistic
rstXMLMessageHeaders.Open "SELECT * FROM tblXMLMessageHeaders", cnn, adOpenDynamic, adLockOptimistic
rstProductPart.Open "SELECT * FROM tblProductPart", cnn, adOpenDynamic, adLockOptimistic
rstEpubTechnicalProtection.Open "SELECT * FROM tblEpubTechnicalProtection", cnn, adOpenDynamic, adLockOptimistic
rstProductClassification.Open "SELECT * FROM tblProductClassification", cnn, adOpenDynamic, adLockOptimistic
rstSubject.Open "SELECT * FROM tblSubject", cnn, adOpenDynamic, adLockOptimistic
rstImprint.Open "SELECT * FROM tblImprint", cnn, adOpenDynamic, adLockOptimistic
rstPrice.Open "SELECT * FROM tblPrice", cnn, adOpenDynamic, adLockOptimistic
rstSalesRestriction.Open "SELECT * FROM tblSalesRestriction", cnn, adOpenDynamic, adLockOptimistic
rstProductAvailability.Open "SELECT * FROM tblProductAvailability", cnn, adOpenDynamic, adLockOptimistic
rstSupplyDetail.Open "SELECT * FROM tblSupplyDetail", cnn, adOpenDynamic, adLockOptimistic
rstRelatedProducts.Open "SELECT * FROM tblRelatedProducts", cnn, adOpenDynamic, adLockOptimistic
rstProductIdentifier.Open "SELECT * FROM tblProductIdentifier", cnn, adOpenDynamic, adLockOptimistic
rstCitedContent.Open "SELECT * FROM tblCitedContent", cnn, adOpenDynamic, adLockOptimistic
rstLanguage.Open "SELECT * FROM tblLanguage", cnn, adOpenDynamic, adLockOptimistic
rstResourcesFeature.Open "SELECT * FROM tblResourcesFeature", cnn, adOpenDynamic, adLockOptimistic
rstTax.Open "SELECT * FROM tblTax", cnn, adOpenDynamic, adLockOptimistic
Set xDoc = New MSXML2.DOMDocument60
xDoc.Load ("C:\TRIPS\Home\OnixImport\Processing\OnixImport.xml")
Set xmlist = xDoc.getElementsByTagName("*")
For Each Node1 In xmlist
For Each Node2 In Node1.childNodes
If Node2.nodeType = NODE_TEXT Then
'rstXMLMessageHeaders
If Node1.nodeName = "SenderIDType" And Node1.parentNode.nodeName = "SenderIdentifier" Then
rstXMLMessageHeaders.AddNew
rstXMLMessageHeaders!SenderIDType = Node1.Text
End If
If Node1.nodeName = "IDValue" And Node1.parentNode.nodeName = "SenderIdentifier" Then rstXMLMessageHeaders!IDValue = Node1.Text
If Node1.nodeName = "SenderName" And Node1.parentNode.nodeName = "Sender" Then rstXMLMessageHeaders!SenderName = Node1.Text
If Node1.nodeName = "ContactName" And Node1.parentNode.nodeName = "Sender" Then rstXMLMessageHeaders!ContactName = Node1.Text
If Node1.nodeName = "EmailAddress" And Node1.parentNode.nodeName = "Sender" Then rstXMLMessageHeaders!EmailAddress = Node1.Text
If Node1.nodeName = "MessageNumber" And Node1.parentNode.nodeName = "Header" Then rstXMLMessageHeaders!MessageNumber = Node1.Text
If Node1.nodeName = "SentDateTime" And Node1.parentNode.nodeName = "Header" Then rstXMLMessageHeaders!SentDateTime = Node1.Text
'/rstXMLMessageHeaders
'Block 0
If Node1.nodeName = "RecordReference" And Node1.parentNode.nodeName = "Product" Then
rst.AddNew
rst!Product_RecordReference = Node1.Text
varRecordReference = Node1.Text
VarUpdated = Format(Now(), "YYYYMMDD")
End If
If Node1.nodeName = "NotificationType" And Node1.parentNode.nodeName = "Product" Then rst!Product_NotificationType = Node1.Text
If Node1.nodeName = "ProductIDType" And Node1.parentNode.nodeName = "ProductIdentifier" Then rst!ProductIdentifier_ProductIDType = Node1.Text
If Node1.nodeName = "IDValue" And Node1.parentNode.nodeName = "ProductIdentifier" Then rst!ProductIdentifier_IDValue = Node1.Text
'/Block 0
'ProductForm
If Node1.nodeName = "ProductComposition" And Node1.parentNode.nodeName = "DescriptiveDetail" Then rst!DescriptiveDetail_ProductComposition = Node1.Text
If Node1.nodeName = "ProductForm" And Node1.parentNode.nodeName = "DescriptiveDetail" Then rst!DescriptiveDetail_ProductForm = Node1.Text
If Node1.nodeName = "ProductFormDetail" Then rst!ProductFormDetail = Node1.Text
If Node1.nodeName = "ProductFormdescription" Then rst!ProductFormdescription = Node1.Text
If Node1.nodeName = "EpubtechnicalProtection" Then rst!EpubtechnicalProtection = Node1.Text
''ProductPart
If Node1.nodeName = "ProductIDType" And Node1.parentNode.nodeName = "ProductIdentifier" Then
rstProductPart.AddNew
rstProductPart!ProductIdentifier_ProductIDType = Node1.Text
rstProductPart!Product_RecordReference = varRecordReference
VarUpdated = Format(Now(), "YYYYMMDD")
End If
If Node1.nodeName = "IDValue" And Node1.parentNode.nodeName = "ProductIdentifier" Then rstProductPart!ProductIdentifier_IDValue = Node1.Text
If Node1.nodeName = "ProductForm" And Node1.parentNode.nodeName = "DescriptiveDetail" Then rstProductPart!DescriptiveDetail_ProductForm = Node1.Text
If Node1.nodeName = "ProductFormDetail" Then rstProductPart!ProductFormDetail = Node1.Text
If Node1.nodeName = "NumberOfItemsOfThisForm" Then rstProductPart!NumberOfItemsOfThisForm = Node1.Text
''/ProductPart
'/ProductForm
''SupportingResource
If Node1.nodeName = "ResourceContentType" And Node1.parentNode.nodeName = "SupportingResource" Then
rstSupportingResource.AddNew
rstSupportingResource!SupportingResource_ResourceContentType = Node1.Text
varRFType = rstSupportingResource!SupportingResource_ResourceContentType
rstSupportingResource!Product_RecordReference = varRecordReference
rstSupportingResource.Update
varIDSupportingResource = rstSupportingResource!IDSupportingResource
VarUpdated = Format(Now(), "YYYYMMDD")
End If
If Node1.nodeName = "ContentAudience" And Node1.parentNode.nodeName = "SupportingResource" Then _
rstSupportingResource!SupportingResource_ContentAudience = Node1.Text
If Node1.nodeName = "ResourceMode" And Node1.parentNode.nodeName = "SupportingResource" Then _
rstSupportingResource!SupportingResource_ResourceMode = Node1.Text
If Node1.nodeName = "ResourceForm" And Node1.parentNode.nodeName = "ResourceVersion" Then _
rstSupportingResource!ResourceVersion_ResourceForm = Node1.Text
'Measure
If Node1.nodeName = "MeasureType" And Node1.parentNode.nodeName = "Measure" Then
rstMeasure.AddNew
rstMeasure!Measure_MeasureType = Node1.Text
rstMeasure!Product_RecordReference = varRecordReference
VarUpdated = Format(Now(), "YYYYMMDD")
End If
If Node1.nodeName = "Measurement" And Node1.parentNode.nodeName = "Measure" Then rstMeasure!Measure_Measurement = Node1.Text
If Node1.nodeName = "MeasureUnitCode" And Node1.parentNode.nodeName = "Measure" Then rstMeasure!Measure_MeasureUnitCode = Node1.Text
'/Measure
'Edition
If Node1.nodeName = "EditionNumber" And Node1.parentNode.nodeName = "DescriptiveDetail" Then rst!DescriptiveDetail_EditionNumber = Node1.Text
If Node1.nodeName = "EditionVersionNumber" And Node1.parentNode.nodeName = "DescriptiveDetail" Then rst!DescriptiveDetail_EditionVersionNumber = Node1.Text
If Node1.nodeName = "EditionStatement" And Node1.parentNode.nodeName = "DescriptiveDetail" Then rst!DescriptiveDetail_EditionStatement = Node1.Text
'/Edition
'Language
If Node1.nodeName = "LanguageRole" And Node1.parentNode.nodeName = "Language" Then
rstLanguage.AddNew
rstLanguage!Product_RecordReference = varRecordReference
rstLanguage!Language_LanguageRole = Node1.Text
VarUpdated = Format(Now(), "YYYYMMDD")
End If
If Node1.nodeName = "LanguageCode" And Node1.parentNode.nodeName = "Language" Then rstLanguage!Language_LanguageCode = Node1.Text
'/Language
'Extent
If Node1.nodeName = "ExtentType" And Node1.parentNode.nodeName = "Extent" Then rst!Extent_ExtentType = Node1.Text
If Node1.nodeName = "ExtentValue" And Node1.parentNode.nodeName = "Extent" Then rst!Extent_ExtentValue = Node1.Text
If Node1.nodeName = "ExtentValueRoman" And Node1.parentNode.nodeName = "Extent" Then rst!Extent_ExtentValueRoman = Node1.Text
If Node1.nodeName = "ExtentUnit" And Node1.parentNode.nodeName = "Extent" Then rst!Extent_ExtentUnit = Node1.Text
If Node1.nodeName = "Illustrated" And Node1.parentNode.nodeName = "DescriptiveDetail" Then rst!DescriptiveDetail_Illustrated = Node1.Text
'/Extent
'RelatedProducts
If Node1.nodeName = "ProductRelationCode" And Node1.parentNode.nodeName = "RelatedProduct" Then
rstRelatedProducts.AddNew
rstRelatedProducts!Product_RecordReference = varIDRelatedProducts
rstRelatedProducts!ProductRelationCode = Node1.Text
rstRelatedProducts.Update
varIDRelatedProducts = rstRelatedProducts!IDRelatedProducts
rstRelatedProducts!Product_RecordReference2 = varRecordReference
VarUpdated = Format(Now(), "YYYYMMDD")
End If
'/RelatedProducts
'ProductIdentifier
If Node1.nodeName = "ProductIDType" And Node1.parentNode.nodeName = "ProductIdentifier" Then
rstProductIdentifier.AddNew
rstProductIdentifier!IDRelatedProducts = varIDRelatedProducts
rstProductIdentifier!ProductIdentifier_ProductIDType = Node1.Text
rstProductIdentifier!Product_RecordReference = varRecordReference
VarUpdated = Format(Now(), "YYYYMMDD")
End If
If Node1.nodeName = "IDValue" And Node1.parentNode.nodeName = "ProductIdentifier" Then rstProductIdentifier!ProductIdentifier_IDValue = Node1.Text
'/ProductIdentifier
'ProductAvailability
If Node1.nodeName = "SupplyDateRole" And Node1.parentNode.nodeName = "SupplyDate" Then
rstProductAvailability.AddNew
rstProductAvailability!Product_RecordReference = varRecordReference
rstProductAvailability!SupplyDate_SupplyDateRole = Node1.Text
VarUpdated = Format(Now(), "YYYYMMDD")
End If
If Node1.nodeName = "Date" And Node1.parentNode.nodeName = "SupplyDate" Then rstProductAvailability!SupplyDate_Date = Node1.Text
If Node1.nodeName = "OrderTime" And Node1.parentNode.nodeName = "SupplyDetail" Then rstProductAvailability!SupplyDetail_OrderTime = Node1.Text
'/ProductAvailability
'SalesRestriction
If Node1.nodeName = "SalesRestrictionType" And Node1.parentNode.nodeName = "SalesRestriction" Then
rstSalesRestriction.AddNew
rstSalesRestriction!Product_RecordReference = varRecordReference
rstSalesRestriction!SalesRestrictionType = Node1.Text
VarUpdated = Format(Now(), "YYYYMMDD")
End If
'/SalesRestriction
'Price
If Node1.nodeName = "PriceType" And Node1.parentNode.nodeName = "Price" Then
rstPrice.AddNew
rstPrice!Product_RecordReference = varRecordReference
rstPrice!Price_PriceType = Node1.Text
rstPrice.Update
varPriceID = rstPrice!IDPrice
VarUpdated = Format(Now(), "YYYYMMDD")
End If
If Node1.nodeName = "UnpricedItemType" Then rstPrice!UnpricedItemType = Node1.Text
If Node1.nodeName = "PriceQualifier" And Node1.parentNode.nodeName = "Price" Then rstPrice!Price_PriceQualifier = Node1.Text
If Node1.nodeName = "PriceTypeDescription" And Node1.parentNode.nodeName = "Price" Then rstPrice!Price_PriceTypeDescription = Node1.Text
If Node1.nodeName = "DiscountCodeType" And Node1.parentNode.nodeName = "DiscountCoded" Then rstPrice!DiscountCoded_DiscountCodeType = Node1.Text
If Node1.nodeName = "DiscountCode" And Node1.parentNode.nodeName = "DiscountCoded" Then rstPrice!DiscountCoded_DiscountCode = Node1.Text
If Node1.nodeName = "PriceAmount" And Node1.parentNode.nodeName = "Price" Then rstPrice!Price_PriceAmount = Node1.Text
If Node1.nodeName = "CurrencyCode" And Node1.parentNode.nodeName = "Price" Then rstPrice!Price_CurrencyCode = Node1.Text
If Node1.nodeName = "PriceDateRole" And Node1.parentNode.nodeName = "PriceDate" Then rstPrice!PriceDate_PriceDateRole = Node1.Text
If Node1.nodeName = "Date" And Node1.parentNode.nodeName = "PriceDate" Then rstPrice!PriceDate_Date = Node1.Text
'/Price
'Tax
If Node1.nodeName = "TaxRateCode" And Node1.parentNode.nodeName = "Tax" Then
rstTax.AddNew
rstTax!IDPrice = varPriceID
rstTax!Tax_TaxRateCode = Node1.Text
rstTax!Product_RecordReference = varRecordReference
VarUpdated = Format(Now(), "YYYYMMDD")
End If
If Node1.nodeName = "TaxableAmount" And Node1.parentNode.nodeName = "Tax" Then rstTax!Tax_TaxableAmount = Node1.Text
If Node1.nodeName = "TaxType" And Node1.parentNode.nodeName = "Tax" Then rstTax!Tax_TaxType = Node1.Text
'/Tax
'Product - update date bijwerken met VarUpdated
'rst!UpdateDate = VarUpdated
End If
Next Node2
If Not rst.EOF Then rst.Update
If Not rstMeasure.EOF Then rstMeasure.Update
If Not rstTitle.EOF Then rstTitle.Update
If Not rstTitleElement.EOF Then rstTitleElement.Update
If Not rstPartOfCollection.EOF Then rstPartOfCollection.Update
If Not rstTitleDetail.EOF Then rstTitleDetail.Update
If Not rstContributor.EOF Then rstContributor.Update
If Not rstTextContent.EOF Then rstTextContent.Update
If Not rstSupportingResource.EOF Then rstSupportingResource.Update
If Not rstXMLMessageHeaders.EOF Then rstXMLMessageHeaders.Update
If Not rstProductPart.EOF Then rstProductPart.Update
If Not rstEpubTechnicalProtection.EOF Then rstEpubTechnicalProtection.Update
If Not rstSubject.EOF Then rstSubject.Update
If Not rstImprint.EOF Then rstImprint.Update
If Not rstPrice.EOF Then rstPrice.Update
If Not rstSalesRestriction.EOF Then rstSalesRestriction.Update
If Not rstProductAvailability.EOF Then rstProductAvailability.Update
If Not rstSupplyDetail.EOF Then rstSupplyDetail.Update
If Not rstRelatedProducts.EOF Then rstRelatedProducts.Update
If Not rstProductIdentifier.EOF Then rstProductIdentifier.Update
If Not rstCitedContent.EOF Then rstCitedContent.Update
If Not rstLanguage.EOF Then rstLanguage.Update
If Not rstResourcesFeature.EOF Then rstResourcesFeature.Update
If Not rstTax.EOF Then rstTax.Update
Next Node1
rst.Close
rstMeasure.Close
rstTitle.Close
rstTitleElement.Close
rstPartOfCollection.Close
rstTitleDetail.Close
rstContributor.Close
rstTextContent.Close
rstSupportingResource.Close
rstXMLMessageHeaders.Close
rstProductPart.Close
rstEpubTechnicalProtection.Close
rstSubject.Close
rstImprint.Close
rstPrice.Close
rstSalesRestriction.Close
rstProductAvailability.Close
rstSupplyDetail.Close
rstRelatedProducts.Close
rstProductIdentifier.Close
rstCitedContent.Close
rstLanguage.Close
rstResourcesFeature.Close
rstTax.Close
Set rst = Nothing
Set rstMeasure = Nothing
Set rstTitle = Nothing
Set rstTitleElement = Nothing
Set rstPartOfCollection = Nothing
Set rstTitleDetail = Nothing
Set rstContributor = Nothing
Set rstTextContent = Nothing
Set rstSupportingResource = Nothing
Set rstXMLMessageHeaders = Nothing
Set rstProductPart = Nothing
Set rstEpubTechnicalProtection = Nothing
Set rstSubject = Nothing
Set rstImprint = Nothing
Set rstPrice = Nothing
Set rstSalesRestriction = Nothing
Set rstProductAvailability = Nothing
Set rstSupplyDetail = Nothing
Set rstRelatedProducts = Nothing
Set rstProductIdentifier = Nothing
Set rstCitedContent = Nothing
Set rstLanguage = Nothing
Set rstResourcesFeature = Nothing
Set rstTax = Nothing
End Function
Last edited: