Export and Import from Excel to Access
- Export and Import from Excel to Access
- Export from Excel to Access with ADO
- Video: Overview of Excel to Access
- Download the Template – Excel to Access
- What is ADO
- Video 2: Exporting data from Excel into Access
- Enabling ADO
- Creating our Microsoft Access database
- Export from Excel to Access: Process and Code
- In Conclusion
- Video: Importing data from Access into Excel
Export from Excel to Access with ADO
In this tutorial we will look at how you can Import and Export data from a Microsoft Excel spreadsheet to an M S Access database.
I need to say at the beginning of this tutorial that I am not an Expert in ADO DB or OLE DB but I am merely passing on the knowledge that I have with the hope that you can use it to your benefit.
What I have done is tried to simplify what is often explained in a very complex manner and presented to you a process that you may be able to use even with limited Excel and Access skills.
Here is what we will be discussing and demonstrating:
- What is ADO and how to enable it in your application
- When you might use this process
- Sending data (Exporting) to Access with ADO DB from Excel
In our second web article I will show you how you can:
- Importing data from Access to Excel with ADO DB
- Create the table in your Access database from your Excel file
In the third article I will show you how to:
- Sending data (Exporting) to Access with ADO DB from a Userform
- Importing data from Access to Excel with ADO DB from a Userform
In the forth article I will show you how to:
( tentative, depending on the level of interest in the subject)
- Appending data in the Access from Excel with ADO DB from a Userform
- Deleting data in the Access from Excel with ADO DB from a Userform
Video: Overview of Excel to Access
This video is an overview of the exporting importing process using ADO DB and OLE DB.
Download the Template – Excel to Access
I would recommend that you download the template because it will make it easier for you to complete this tutorial. The references below are designed for this template. So save time and heartache – use the template.
Template Excel Access
What is ADO
ADO stands for Active X Data Objects (ADO) which is basically a program interface developed by Microsoft that will let us when working in Windows applications, obtain access to a relational or even non-relational database both from Microsoft Access and from other providers such as SQL DB.
When it is used with OLE DB (Object Linking and Embedding) as a source it can provide a consistent and effective way of accessing data in a database regardless of the data structure.
Please view the videos is for more information.
When might you use ADO DB
There are many genuine situations when you would use this process to export and import data from Microsoft Excel into and out of an MS Access database.
To name just a few:
- Many organisations do not like their employees storing a lot of data and so are hesitant to install MS Access onto each PC. For control reasons though they may allow access to be used on a network drive where they can monitor what is being stored.
- Cost parameters may inhibit the installing of access on multiple computers.
- You may need to import data from one database, change its formats and even manipulate the data with formulas and then exported to a new database. (This has been my experience in using this process)
Note: Please do not think for a moment that I’m recommending that you should use Microsoft Excel as the front end to an access database. It would be far easier to just simply work in Microsoft Access. However as shown above there are times when process could be used.
Video 2: Exporting data from Excel into Access
In this video I show you how to set up the Access database and also the Excel spreadsheet to be able to export the data. I also discuss the breakdown of the code and the methods and arguments that are used.
- Hit ALT +F11 to open the VBA Editor in Microsoft Excel and then also repeat this process in your Microsoft Access application as well.
- Click on Tools and then References, scroll down until you find Microsoft ActiveX Data Objects.
- Click on the latest available library as shown in the illustration below
Creating our Microsoft Access database
Before we start we need to have a little database that we can send and retrieve data to and from.
Create a simple database as shown in the video above.
- Name the database PhoneBook
- Name the table PhoneList
- Add seven 7 fields
- ID – data type Number
- Surname – data type Text
- FirstName – (one word) data type Text
- Address – data type Text
- Phone – data type Text
- Mobile – data type Text
- Email – data type Text
Note 1: You may be wondering why I have set the data types for the phone number and mobile as text. This is because we will be using separators such as brackets and spaces so they cannot be viewed as numbers. You can even set a Mask that will force the user to use a specific format when entering the number which cannot be done when the data type is set to numbers.
When the data is imported from Microsoft Access the back to your Excel file you will notice that the numbers will be shown in your Excel file appropriately with the red smart tag showing Text.
Note 2: We have set the ID to Number not Auto Number in this example.
This will require that we know what the next ID number is in our database. If you were to use Auto number then you would not be sending the ID number from Excel to the database. You will notice on the worksheet Export that cell J3 is the next database ID number. And when we send the data to Access we replace that with our next ID number. (This ID number in a real application would probably be a Receipt number or an employee number but it must be unique)
Adding the formula to get our ID number
In cell K3 on the Export worksheet add this formula =MAX(A2:A1000)
Export from Excel to Access: Process and Code
Adding the code to get the File Path
Because we will be referencing a file path to the Access database and that file path could change because the database could be moved to another folder or to a network drive, we want to make that file path is a flexible part of our code. To do this we will put the file path into a cell reference and then reference that cell location to gain the path at any time or location.
Hit ALT +F11 to open the VBA editor in Microsoft Excel and then in the module called Excel_Access in the template provided paste the code below.
Dim FName As Variant
FName = Application.GetOpenFilename(filefilter:="Access Files,*.acc*")
Sheet1.Range("I3").Value = FName
Assign this macro to the shape called Get the File Path on the Export worksheet by right clicking the shape and then choosing Assign Macro and selecting the macro and then clicking OK.
Adding the code to Export from Excel to Access
With the Visual Basic editor open paste this code into the same module. This is the code that will send your data (single or multiple lines of data from your spreadsheet to the Access database).
Please watch the video for an explanation of the code. You will notice also that I’ve put in extensive comments to try to help you to see the Library Object Class/Method/Arguments that are used to access the data object in Microsoft Access using ADO DB and source OLE DB.
Dim cnn As ADODB.Connection 'dim the ADO collection class
Dim rst As ADODB.Recordset 'dim the ADO recordset class
Dim x As Long, i As Long
Dim nextrow As Long
'add error handling
On Error GoTo errHandler:
'Variables for file path and last row of data
dbPath = ActiveSheet.Range("I3").Value
nextrow = Cells(Rows.Count, 1).End(xlUp).Row
'Initialise the collection class variable
Set cnn = New ADODB.Connection
'Check for data
If Sheet1.Range("A2").Value = "" Then
MsgBox " Add the data that you want tot send to MS Access"
'Connection class is equipped with a —method— named Open
'—-4 aguments—- ConnectionString, UserID, Password, Options
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
'two primary providers used in ADO SQLOLEDB —-Microsoft.JET.OLEDB.4.0 —-Microsoft.ACE.OLEDB.12.0
'OLE stands for Object Linking and Embedding, Database
'ADO library is equipped with a class named Recordset
Set rst = New ADODB.Recordset 'assign memory to the recordset
'ConnectionString Open '—-5 aguments—-
'Source, ActiveConnection, CursorType, LockType, Options
rst.Open Source:="PhoneList", ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
'you now have the recordset object
'add the values to it
For x = 2 To nextrow
For i = 1 To 7
rst(Cells(1, i).Value) = Cells(x, i).Value
'close the recordset
' Close the connection
Set rst = Nothing
Set cnn = Nothing
'communicate with the user
MsgBox " The data has been successfully sent to the access database"
'Update the sheet
Application.ScreenUpdating = True
'show the next ID
Sheet1.Range("J3").Value = Sheet1.Range("K3").Value + 1
'Clear the data
On Error GoTo 0
Set rst = Nothing
Set cnn = Nothing
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Export_Data"
Assigning the macro to the shape on the worksheet
Assign this macro to the shape called Send to Access on the Export worksheet by right clicking the shape and then choosing Assign Macro and selecting the macro and then clicking OK.
Now it is time to test your file by exporting data to your access database.
I hope you have found this tutorial helpful and you can use it to speed up your work processes.
Our next article
Our next web article will demonstrate the process of importing and creating the table in Microsoft Access from your Excel file.
Here is a video of the process that will be dealt with as a separate web article.
Video: Importing data from Access into Excel