Using ADO VBA to Insert Excel Fields into Access Table

Question

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.

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 & "\test_database.accdb")

Sub InsertRecord()
Dim data_base As Database
Set data_base = OpenDatabase(CurrentProject.Path & "\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) " & _
    "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 #" & elec_copy.Value
qd.Parameters("p5").Value =  "Signed Copy #" & 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 = &H0001

Set objConnection = CreateObject("ADODB.Connection")

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & elec_copy.Value & ";" & _
    "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) " & _
        "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

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?

Thanks in advance for any help!

Nathan

Solution

In your connection string, you have said HDR=Yes, which means that the first row of your range contains the names of your fields, so, very roughly:

Do Until objRecordset.EOF
    qd.Sql = "INSERT INTO items_needed_table(pr_no, line_no, desc, weeks) " & _
    "Values([p1],[p2],[p3])"
    ' p1 was declared earlier in code, same value as before
    '**No it was not, the earlier stuff is mostly irrelevant

    qd.Parameters("p2").Value = objRecorset.Fields("line_no")
    qd.Parameters("p3").Value = objRecordset.Fields("desc")
    qd.Parameters("p4").Value = objRecordset.Fields("weeks")
    qd.Execute
    ''You are moving through a recordset, not a worksheet
    objRecordset.MoveNext
Loop

If this all that you are doing with the selection from Excel, it could be inserted with one query, because you are not changing pr_num.

Comments

[nathansizemore]

Thanks @Remou. So you are saying that the value of ("p1") is null after the execution command from earlier above?

[Fionnuala]

It may or may not be, it is a little difficult to unravel :) I thought you had reset the query, but perhaps you did not.