How to convert from DAO to ADO?
===

I am using an access data project and need to convert a bill of materials module. I can use the module below in a normal microsoft access mdb but it does not work in a microsoft access adp. 

I am trying to decompose a parent item assembly and all its sub assemblies into a list of just components and required quantities. Then list this on a form. 

I can probably use just the bill details table but I am not sure how to convert the code. 

Server Setup

-Microsoft Sql Server 2008 R2

-Operating System: Windows Server 2008 R2 Standard

Client Setup

-Microsoft Access 2010 (will be using 2010 run time on other clients)

-Operating System - Windows 7 Professional (Service Pack 1)

Tables - List

1) T_BM_BillHeader

Field1 - BillNo

Field2 - Revision

Field3 - BillType (K=Kit, S= Standard Bill, both are assemblies, kits do not contain additional bill levels, standard bills can contain multiple bill levels but typically no more than 3)

2) T_BM_BillDetail

Field1 - BillNo

Field2 - Revision

Field3 - LineKey

Field4 - LineSeqNo

Field5 - ComponentItemCode

Field6 - ComponentRevision (Field value is * by default, 001 or 001+1 otherwise)

Field7 - QuantityPerBill

3) T_CI_Item

Field1 - ItemCode

Field2 - ItemCodeDescription

Field3 - ProductType (R=raw material, K=Kit, F=Finished good)


Module Code (BOM1)

    Option Compare Database
    Option Explicit
    
     
    'The Bill of materials processor:
    '  is designed to allow you to decompose an assembly into its component items.
    '  operates at multiple levels, where each item at each level can be a component
    '  or a sub-assembly. (SubAssemblies are further decomposed by the breakdown).
    '  uses 3 tables
    '   :Table 1,  Assemblies contains the list of items needed to assemble any assembly.
    '              the items can be subassemblies or components.
    '   Comprises the following fields.
    '    : ComponentID, a text field to allow the user to recognise the item.
    '    : ParentID, a code field to allow linking.
    '    : NumberRequired, The number of units required for this level of this assembly.
    '   For a given assembly, the items that go into its construction are entered into the
    '   ComponentID field, while the parentID contains the code identifying the given assembly.
    '
    'Table 2, Components contains the list of items recognised by the Assemblies Table
    '   Comprises the following fields
    '    : ComponentID, a unique key, identifying the component
    '    : ComponentDescription describes the component
    '    : AssemblyBoolean to identify whether this is an assembly which has a breakdown
    '      entered in the Assemblies Table.
    
    'Table 3, the Output Table is a variable purpose table which, in this example, identifies the
        '  the components and the number required to construct any assembly
    '   Comprises the following fields
    '    :ComponentID            the same format as the Component Table.
    '    :Number Required.   A Count of the number of items required.
    
    'To Start the demonstration, select the assembly of choice from the input form
    
    
    Const Qu = """"
    Type typBits
        Component As String
        NumberOF As Integer
        End Type
    
    Sub BOMHost(strAssemblyP As String)
    Dim db1 As Database
    Dim rs1 As Recordset
    
    Dim iArray() As typBits
    'Dim strAssembly As String
    Dim fDone As Integer               ' flag as it is bad practice to modify loop parameters within loop
    Dim iCurrent As Integer            ' where you are in the current array
    Dim intMultiplier As Integer       ' multiplication factor for number of items
    
    Set db1 = CurrentDb()
    ReDim iArray(0)
    
    fDone = False
    iCurrent = 0
    intMultiplier = 1
    
    DoCmd.RunSQL ("Delete *.* from OutPutTable") ' clear the outputTable
    
    If GetSubAssembly(strAssemblyP, db1, rs1) = 0 Then GoTo BOMHost_End
    'Set up the array for the item you wish to decompose.
    ParseList iArray(), rs1, UBound(iArray), intMultiplier
    
    Do Until fDone
    'Take the next item in the array, (for the first item, the next item is the
    'first unit in the array). Gets the constituent items from the assembly table referencing
    'the parentID. If the item has subcomponents then the item can be decomposed further.
    ' if the item is a component (no further decomposition) then it goes to Output.
    'Otherwise  it is a subAssembly and you add the parts to the array  using ParseList.
    
       If GetSubAssembly(iArray(iCurrent).Component, db1, rs1) = 0 Then
            AddtoOutput db1, iArray, iCurrent
       Else
            intMultiplier = iArray(iCurrent).NumberOF
            ParseList iArray(), rs1, UBound(iArray), intMultiplier
       End If
       
    'That has finished the processing for the item in the array, so increment the pointer to
    'your current position and test to see if you have finished.( your current pointer is now
    'equal to the array UBound). ' if not  go back and do the next Item,
    'if so then Output Table contains the BillOfMaterials list and you are finished.
    
       iCurrent = iCurrent + 1
       If iCurrent = UBound(iArray) Then fDone = True
    Loop
    MsgBox "Completed"
    BOMHost_End:
    db1.Close
    
    End Sub
    
    
    Private Function GetSubAssembly(strParentID As String, db1 As Database, rs1 As Recordset) As Integer
    ' returns 0 if no records, else 1 (doesn't move to end of recordset)
    Set rs1 = db1.OpenRecordset("select Assemblies.ComponentID,Assemblies.NumberRequired from Assemblies where " _
    & "(((Assemblies.ParentID)=" & Qu & strParentID & Qu & "))", DB_OPEN_DYNASET)
    'Debug.Print rs1
    GetSubAssembly = rs1.RecordCount
    End Function
    'Gets the individual records from rs1 (argument recordset) and puts them into the array
    Private Sub ParseList(iArray() As typBits, rs1 As Recordset, intLastPosition As Integer, intMultiplier As Integer)
    Dim intSize As Integer
    'iArray() is integer array defined in the host procedure
    'rs1 the recordset to get data from
    'intLastPosition the last position in the array
    ' intMultiplier is the multiplying factor based on the number of parent units required
    intSize = intLastPosition + 1
    Do Until rs1.EOF
        
        ReDim Preserve iArray(intSize)
        iArray(intLastPosition).Component = rs1!ComponentID
        iArray(intLastPosition).NumberOF = rs1!NumberRequired * intMultiplier
        rs1.Move 1
        intSize = intSize + 1
        intLastPosition = intLastPosition + 1
    Loop
        End Sub
    
    'Components are added to the output table . Modify this module to modify Output
    
    Private Sub AddtoOutput(db1 As Database, iArray() As typBits, iICurrent As Integer)
    
    Dim rs1 As Recordset
    Dim strAssemblyID As String
    Dim intNumberOf As Integer
    
    strAssemblyID = iArray(iICurrent).Component
    intNumberOf = iArray(iICurrent).NumberOF
    
    
    Set rs1 = db1.OpenRecordset("select OutPutTable.* from OutputTable where " _
        & "(( OutPutTable.ComponentID=" & Qu & strAssemblyID & Qu & "))", DB_OPEN_DYNASET)
    If rs1.RecordCount = 0 Then  'the component is new
        rs1.AddNew
            rs1!ComponentID = strAssemblyID
            rs1!NumberRequired = intNumberOf
        rs1.Update
    Else
        rs1.Edit
        rs1!NumberRequired = rs1!NumberRequired + intNumberOf
                   rs1.Update
    End If
    End Sub

