Export Data from Excel to Access with VBA

Tabaluga

New Member
Joined
Sep 15, 2020
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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.

VBA Code:
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"
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

dataluver

Well-known Member
Joined
Jan 17, 2020
Messages
530
Office Version
  1. 365
Platform
  1. Windows
You won't be able to establish a connection with an accdb file using Jet 4 AFAIK.
Here is one variant that will work.

VBA Code:
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:

VBA Code:
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:
VBA Code:
? adoconnectstring
Press enter. You can get the connection string from within Excel as well by creating a new connection.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,899
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 15, 2020
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
You won't be able to establish a connection with an accdb file using Jet 4 AFAIK.
Here is one variant that will work.

VBA Code:
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:
VBA Code:
? adoconnectstring
Press enter. You can get the connection string from within Excel as well by creating a new connection.
 

Tabaluga

New Member
Joined
Sep 15, 2020
Messages
3
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

VBA Code:
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:

VBA Code:
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:
VBA Code:
? 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?
 

dataluver

Well-known Member
Joined
Jan 17, 2020
Messages
530
Office Version
  1. 365
Platform
  1. Windows
I don't understand your question. You enter it exactly where I showed you in your code. Did you see Micron's suggestion?
 

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,481
Messages
5,523,188
Members
409,503
Latest member
ATF

This Week's Hot Topics

Top