title: Using ADO VBA to Insert Excel Fields into Access Table
body: <p>I am trying to use ADO to access and read some things from an Excel File.  I understand how to get it open and do the SELECT * and put that into a Recordset Object.  What I don't understand is if I am select a group of info, how to access specific fields in that Recordset.</p>
  
  <p>Code:</p>
  
  <pre><code>Private Sub SaveReq_Click()
  ' 
  ' Saves the current entry to the database
  ' Into the TABLE 'pr_req_table'
  ' 
  
  ' Open a connection to the database
  dim data_base as Database
  set data_base = OpenDatabase(CurrentProject.Path &amp; "\test_database.accdb")
  
  Sub InsertRecord()
  Dim data_base As Database
  Set data_base = OpenDatabase(CurrentProject.Path &amp; "\test_database.accdb")
  
  ' Grab all information from form
  ' Add information to pr_req_table
  Dim qd As QueryDef
  Set qd = data_base.CreateQueryDef("")
  qd.sql = "INSERT INTO pr_req_table(pr_no, pr_date, pr_owner, pr_link, pr_signed) " &amp; _
      "values([p1],[p2],[p3],[p4],[p5])"
  qd.Parameters("p1").Value = pr_num.Value
  qd.Parameters("p2").Value = Format(pr_date.Value, "mm/dd/yyyy")
  qd.Parameters("p3").Value = List22.Value
  qd.Parameters("p4").Value = "Excel Copy #" &amp; elec_copy.Value
  qd.Parameters("p5").Value =  "Signed Copy #" &amp; sign_copy.Value 
  qd.Execute
  
  
  ' The following section reads from the elec_copy field's hyperlink
  ' It scans the Excel file for items it needs to include into the table
  ' It enters those cells into the TABLE 'items_needed_table'
  '
  ' Slects row by row, and if the item has been marked TRUE, inserts
  ' That row into the TABLE 'items_needed_table'
  
  
  ' Open a connection to Excel
  On Error Resume Next
  
  Const adOpenStatic = 3
  Const adLockOptimistic = 3
  Const adCmdText = &amp;H0001
  
  Set objConnection = CreateObject("ADODB.Connection")
  
  objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &amp; _
      "Data Source=" &amp; elec_copy.Value &amp; ";" &amp; _
      "Extended Properties=""Excel 8.0;HDR=Yes;"";"
  
  ' Decalre a RecordSet Object
  Set objRecordSet = CreateObject("ADODB.Recordset")
  
  ' Grab all Rows in the Plain_VDR Sheet where 'needed' column == TRUE
  objRecordset.Open "Select line_no, desc, weeks FROM [Plain_VDR$] Where needed = TRUE", _
      objConnection, adOpenStatic, adLockOptimistic, adCmdText
  
  ' Declare a loop counter for row?
  Dim x as Integer
  x = 0
  
  ' Write the information pulled, into the TABLE 'items_needed_table' in Access Database
  Do Until objRecordset.EOF
          qd.sql = "INSERT INTO items_needed_table(pr_no, line_no, desc, weeks) " &amp; _
          "Values([p1],[p2],[p3])"
          ' p1 was declared earlier in code, same value as before
          qd.Parameters("p2").Value = objRecorset.(ROW_X, "line_no")
          qd.Parameters("p3").Value = objRecordset.(ROW_X, "desc")
          qd.Parameters("p4").Value = objRecordset.(ROW_X, "weeks")
          qd.Execute
          x = x + 1
  Loop
  
  ' Close Database connection
  data_base.Close
  
  End Sub
  </code></pre>
  
  <p>My main point of concern is the 'Do Until' loop section.  Doubtful I can insert the entire selection, because 'pr_no' is not defined in the Excel file, but back in Access Database, so I think I will need to loop that command for each row in the Excel file.
  What do I need to use to assign my parameters the values, per row and field, from the Recordset Object?</p>
  
  <p>Thanks in advance for any help!</p>
  
  <p>Nathan</p>