Export Data from Excel to Access with VBA

[Tabaluga]

I am on my first attempt to use vba to open an Access database from an Excel file and write the data in the Excel file to the database. Not surprisingly I've run into difficulty.
This is my code which I've tried, but the process of opening the database does not work (in red).
Can someone help me with that?
The goal ist to export data from Excel to a data base in access to store it there.

Sub TEST_DB_import()
Dim ADOC As ADODB.Connection
Dim DBS As ADODB.Recordset
Dim lngZeile As Long
Dim intIndex As Integer
Dim arNamen As Variant

On Error GoTo Fehler

Set ADOC = New ADODB.Connection
With ADOC
.Provider = "Microsoft.Jet.OLEDB.4.0"
[COLOR=rgb(184, 49, 47)].Open "C:\Users\tapa\Desktop\Flagging Mastersplit.accdb"[/COLOR]
End With

Set DBS = New ADODB.Recordset
DBS.Open "Flagging", ADOC, adOpenKeyset, adLockOptimistic

With Sheets("Sheet1")
arNamen = .Range(.Range("A2"), .Range("A2").End(xlToRight))
For lngZeile = 3 To .Range("A3").End(xlDown).Row
DBS.AddNew
For intIndex = 1 To UBound(arNamen, 2)
DBS.Fields(arNamen(1, intIndex)) = .Cells(lngZeile, intIndex).Value
Next
DBS.Update
Next
End With

Fehler:
If Err.Number Then MsgBox Err.Description, , Err.Number
If Not DBS Is Nothing Then DBS.Close
If Not ADOC Is Nothing Then ADOC.Close
Set ADOC = Nothing
Set DBS = Nothing
End Sub

The error that I have encountered is "Run-time error '3704'
Operation is not allowedwhen the object is closed"

 

[Legacy 456155]

You won't be able to establish a connection with an accdb file using Jet 4 AFAIK.
Here is one variant that will work.

Set ADOC = New ADODB.Connection
With ADOC
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Properties("DATA SOURCE") = "C:\Users\tapa\Desktop\Flagging Mastersplit.accdb"
    .Open
End With

Here is another way:

Set ADOC = New ADODB.Connection
With ADOC
    .Open "PROVIDER=Microsoft.ACE.OLEDB.12.0;DATA SOURCE=C:\Users\tapa\Desktop\Flagging Mastersplit.accdb;"
End With

One easy way to get the connection string, is to open the immediate window from within the Access file that you wish to connect to.
Type in:

? adoconnectstring

Press enter. You can get the connection string from within Excel as well by creating a new connection.

 

[Micron]

Consider just linking to the workbook spreadsheets from Access. It's basically the same as linking to external tables. When the Excel data changes, Access will pick up the changes automatically.

 

[Tabaluga]

You won't be able to establish a connection with an accdb file using Jet 4 AFAIK.
Here is one variant that will work.

Set ADOC = New ADODB.Connection
With ADOC
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Properties("DATA SOURCE") = "C:\Users\tapa\Desktop\Flagging Mastersplit.accdb"
    .Open
End With
[COLOR=rgb(184, 49, 47)]What do you mean by "Data Source"?[/COLOR]

Here is another way:

[CODE=vba]
Set ADOC = New ADODB.Connection
With ADOC
    .Open "PROVIDER=Microsoft.ACE.OLEDB.12.0;DATA SOURCE=C:\Users\tapa\Desktop\Flagging Mastersplit.accdb;"
End With

One easy way to get the connection string, is to open the immediate window from within the Access file that you wish to connect to.
Type in:

? adoconnectstring

Press enter. You can get the connection string from within Excel as well by creating a new connection.

 

[Tabaluga]

You won't be able to establish a connection with an accdb file using Jet 4 AFAIK.
Here is one variant that will work.

Set ADOC = New ADODB.Connection
With ADOC
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Properties("DATA SOURCE") = "C:\Users\tapa\Desktop\Flagging Mastersplit.accdb"
    .Open
End With

Sorry for the first short reply. I am really new to this.
If I go this way I encounter another error - which says Item cannot be found in the collection corresponding to the requested name or ordinal

Here is another way:

Set ADOC = New ADODB.Connection
With ADOC
    .Open "PROVIDER=Microsoft.ACE.OLEDB.12.0;DATA SOURCE=C:\Users\tapa\Desktop\Flagging Mastersplit.accdb;"
End With

One easy way to get the connection string, is to open the immediate window from within the Access file that you wish to connect to.
Type in:

? adoconnectstring

Press enter. You can get the connection string from within Excel as well by creating a new connection.

I get the connection string but were do I enter it?

 

[Legacy 456155]

I don't understand your question. You enter it exactly where I showed you in your code. Did you see Micron's suggestion?

 

[rondeondo]

Agree with @Micron. I've do this in the past. Do it from access as a linked table. If you want to put the data into an access table you can do it as an add or update query from the Excel table.