Appending data to access from excel vba via loop

Question

Wondering if you could help. I am populating a table on Excel which has an undetermined length (it grows as people add to it). This indeterminable amount of data needs to be added to a database in Access through the use of a button in Excel. I have produced the following code to try to alleviate this but get a

Run-time error '3709': The connection cannot be used to perform this operation. It is either closed or invalid in this context.

When I open up debugging it points to this line:

rs.Open sqlstr, DBCont

This can be found in the code below:

Sub submittoDB()
    Dim DBCont As Variant
    Set DBCont = CreateObject("ADODB.connection")
    Dim StrDBPath As String
    StrDBPath = "PATH Here\Database1.accdb"
    Dim sConn As String
    sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & StrDBPath & ";" & _
        "Jet OLEDB:Engine Type=5;" & _
        "Persist Security Info=False;"
    DBCont.Open sConn
    MsgBox "Open DB"
    Dim rs As Object
    Set rs = CreateObject("ADODB.Recordset")
    Dim sqlstr As String
    Dim endlimit As Integer
    Dim i As Integer
    endlimit = Cells(Rows.Count, "H").End(xlUp).Row + 1
    'need to loop each line and remove test
    For i = 5 To endlimit
        sqlstr = "INSERT INTO DigiFinTracker (ProjectNo, ProjectName, Client, Tool, SuggAct, PercSav,PreDigCost,SuggSave,PropSav) VALUES ('" & Cells(6, 4) & "', '" _
        & Cells(5, 4) & "', '" & Cells(4, 4) & "', '" & Cells(i, 8) & "', '" & Cells(i, 9) & "', '" & Cells(i, 10) _
        & "', '" & Cells(i, 11) & "', '" & Cells(i, 12) & "', '" & Cells(i, 13) & "')"
        rs.Open sqlstr, DBCont
        DBCont.Close
    Next i
    DBCont.Close
End Sub

Apologies if the answer is fairly simplistic or I'm missing something crucial, I can't seem to get my head around what's wrong and whether it is possible to loop this type of process.

Many thanks for the responses in advance!

You are closing the connection each loop

Nathan_Sav
– Nathan_Sav

2018-06-25 10:35:07 +00:00
Commented Jun 25, 2018 at 10:35 — Nathan_Sav
– Nathan_Sav, Commented Jun 25, 2018 at 10:35

Vityata · Accepted Answer · 2018-06-25 10:45:49Z

1

In the loop you are closing the connection on each iteration - DBCont.Close. Thus it does not work on the second iteration.

Either remove the DBCont.Close from the loop (advised) or open it every time and remove the DBCont.Open sConn before the loop:

For i = 5 To endlimit
    DBCont.Open sConn   'It would work better if you delete this line
    sqlstr = "INSERT INTO DigiFinTracker (ProjectNo, ProjectName, Client, Tool, SuggAct, PercSav,PreDigCost,SuggSave,PropSav) VALUES ('" & Cells(6, 4) & "', '" _
    & Cells(5, 4) & "', '" & Cells(4, 4) & "', '" & Cells(i, 8) & "', '" & Cells(i, 9) & "', '" & Cells(i, 10) _
    & "', '" & Cells(i, 11) & "', '" & Cells(i, 12) & "', '" & Cells(i, 13) & "')"
    rs.Open sqlstr, DBCont  
    DBCont.Close        'And delete this line as well
Next i

To see what is causing the Data Missmatch try this:

sqlstr = "INSERT INTO DigiFinTracker (ProjectNo, ProjectName, Client, Tool, SuggAct, PercSav,PreDigCost,SuggSave,PropSav) VALUES ('" & Cells(6, 4) & "', '" _
& Cells(5, 4) & "', '" & Cells(4, 4) & "', '" & Cells(i, 8) & "', '" & Cells(i, 9) & "', '" & Cells(i, 10) _
& "', '" & Cells(i, 11) & "', '" & Cells(i, 12) & "', '" & Cells(i, 13) & "')"
 Debug.Print sqlstr

take a look at the immediate window (Ctrl+G to open);
copy the SQL statement from there;
paste it in Access and work until you do not get some result from the query;

edited Jun 25, 2018 at 10:45

answered Jun 25, 2018 at 10:34

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Too many qs Over a year ago

Thank you for noting that. When I make that change, I get Data type mismatch in criteria expression. It does however, now write the information to the database.

Vityata Over a year ago

@Toomanyqs - welcome. On which line is the Data type mismatch? rs.Open sqlstr, DBCount? Then probably you need rs.Close in the loop as well, depending on what you are doing.

Erik A Over a year ago

The data type mismatch is likely caused because all columns are getting quoted (stored as strings), while some aren't strings. But you shouldn't use multiple insert statements to add multiple rows at all, just open up a recordset to the table, and then use .AddNew to add rows. That way you avoid this error, and the code will run faster.

Collectives™ on Stack Overflow

Appending data to access from excel vba via loop

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related