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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
L

Legacy 456155

Guest
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
2,037
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?
 
L

Legacy 456155

Guest
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,126,956
Messages
5,621,811
Members
415,857
Latest member
braunReivn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top