Saving all data to an Access Table using ADODB

[fernando.rosales]

Hi All,

I am having trouble finding a solution to my problem on the internet.

I need help moving all the data from the recordset to a table. I am good looping if it cant be done with with one command just keep in mind there are multiple columns and rows in my recordset.

I also already have a local table that I want to move it into but if its easier to create a table I can work with that.

Function RDM_data_validation(poskustr2 As String)
    Dim conn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rcs As ADODB.Recordset
    Dim stSQL As String
    Dim str As String
    
    DoCmd.RunSQL "delete * from tbl_RDM_Validation"
    
    'Create a Connection
    Set conn = New ADODB.Connection
    With conn
        .ConnectionString = "driver={sql Server}; server=SESQLPRD01; uid=NDSUSER;pwd=XXXX;database=XXXX "
        .Open
    End With
    
    'Set Command to validate
    Set cmd = New ADODB.Command
    With cmd
       .ActiveConnection = conn
       .CommandText = "SELECT * FROM OPENQUERY(SWDC, 'Select hw.company,hw.dc_id,hw.po_nbr,substr(hw.item_id,1,12) as item_id,hw.product_type_desc Product_Type,d.dept_desc Product_Group_Name " & _
        ",c.buyer_code,ia.comp_price,im.retail_price,hw.budget_yyyy,Case when hw.Budget_mm = 1 then ''FEB'' when hw.Budget_mm = 2 then ''MAR'' when hw.Budget_mm = 3 then ''APR'' when hw.Budget_mm = 4 then ''MAY'' " & _
        "when hw.Budget_mm = 5 then ''JUN'' when hw.Budget_mm = 6 then ''JUL'' when hw.Budget_mm = 7 then ''AUG'' when hw.Budget_mm = 8 then ''SEP'' when hw.Budget_mm = 9 then ''OCT'' when hw.Budget_mm = 10 then ''NOV'' " & _
        "when hw.Budget_mm = 11 then ''DEC'' when hw.Budget_mm = 12 then ''JAN'' Else ''Other'' end Budget_Month ,hw.budget_mm,hw.wip_codes,sum(hw.num_lpn) Num_Carton,sum(hw.unit_qty) Units " & _
        "from ross_report_hotel_mlp hw, department d, class c, item_attribute ia, item_master im where hw.facility_id =''PR'' and concat(hw.po_nbr, substr(hw.item_id,1,12)) in  (" & poskustr2 & ")  " & _
        "and hw.wip_codes =''HTRMK'' and hw.department = d.department and concat(hw.department, hw.class) = concat(c.department, c.class) and hw.item_id = ia.item_id and hw.item_id = im.item_id  " & _
        "Group by hw.company,hw.dc_id,hw.po_nbr,substr(hw.item_id,1,12),hw.product_type_desc,d.dept_desc,c.buyer_code,ia.comp_price,im.retail_price,hw.budget_yyyy,hw.Budget_mm ,hw.wip_codes Order by hw.Budget_mm asc')"
    End With
       
    Set rcs = cmd.Execute
    If rcs.EOF Then
        'Do nothing if no data returns. This is handled outside... after this function ends.
    Else
        rcs.MoveFirst
        Do Until rcs.EOF = True
            'At this point I can see the data but not helpful storing in variables, i need to move all to a table
            sunits = rcs!units
            'Other fields ....
            rcs.MoveNext
        Loop
    End If
     
    conn.Close
    Set cmd = Nothing
    Set conn = Nothing
    Set rcs = Nothing
End Function

 

[June7]

Normally I would suggest INSERT SELECT: CurrentDb.Execute "INSERT INTO tablename SELECT …"

But I am not familiar with working with SQLServer backend.

How many fields are involved? - too hard to read through that SQL.

Inside the loop, possibly like (example with number, text, date types):

CurrentDb.Execute "INSERT INTO Tablename(sunits, field2, field3) VALUES(" & rcs!units & ",'" & rcs!field2 & "',#" & rcs!field3 & "#)"

 

[bastanu]

I see you have a linked server (SWDC) in your SQL server which is the source of your data. Why not create a view in your SQL server (SESQLPRD01) that gives you the data then in Access simply dynamically link the view, run an append query based on it to move the data into your tbl_RDM_Validation Access table then simply delete the link.

Cheers,
Vlad

 

[fernando.rosales]

Thank you both. Ended up using part of June7's response.

    Set rcs = cmd.Execute
    If rcs.EOF Then
        'Do nothing if no data returns. This is handled outside... after this module runs.
    Else
        rcs.MoveFirst
        Do Until rcs.EOF = True
            DoCmd.RunSQL "insert into tbl_RDM_Validation (COMPANY,DC_ID,PO_NBR,ITEM_ID,PRODUCT_TYPE,PRODUCT_GROUP_NAME,BUYER_CODE,COMP_PRICE,RETAIL_PRICE,BUDGET_YYYY,BUDGET_MONTH,BUDGET_MM,WIP_CODES,NUM_CARTON,UNITS) " & _
            "values ('" & rcs!company & "', '" & rcs!DC_ID & "', " & rcs!po_nbr & ", " & rcs!item_id & ", '" & rcs!product_type & "', '" & rcs!product_group_name & "', " & rcs!buyer_code & ", " & rcs!comp_price & ", " & rcs!retail_price & ", " & rcs!budget_yyyy & ", '" & rcs!budget_month & "', " & rcs!budget_mm & ", '" & rcs!wip_codes & "', " & rcs!num_carton & ", " & rcs!units & ")"
            rcs.MoveNext
        Loop
    End If