Tom’s Tutorials For Excel: Export Excel Spreadsheet Into Access Database Table
Here’s the latest of the Tom’s Tutorials for Excel series in which Tom Urtis walks you through a solution step-by-step. If you have any questions please ask them here in comments. Tom takes the time to be sure readers of every level of expertise feel confident using Excel. Are you an Excel professional? Tom welcomes your feedback and the opportunity to network.
Among the more common actions you’ll do when interacting with Access from Excel is to transfer records from an Excel worksheet to an Access database table. Suppose you have an Access database named Database1.accdb that contains a table named Table1 with 8 fields. In Sheet2 of your Excel workbook, you amass records during the day that are added to Table1 at the end of the workday.
You are probably aware that beginning with the release of Office 2007, extensions changed for Microsoft’s applications. For example, Excel workbooks that had the extension .xls now are either .xlsx or .xlsm. Access extensions also changed, from .mdb to .accdb, as shown in the below macro.
Take note of the version(s) of Excel and Access when the time comes to implement this code. Especially, the Provider line in the code is
.Provider = "Microsoft.ACE.OLEDB.12.0;Data Source=" & dbFileName & _
";Persist Security Info=False;".
Had this been a version of Office prior to 2007 to accommodate the JET database engine, that same line might have been
.Provider = "Microsoft.Jet.OLEDB.4.0"
or
.Provider = "Microsoft.Jet.OLEDB.4.0;" & "Data Source=" _
& dbFileName & ";" & "Extended Properties=Excel 8.0;".
To automate the daily task of transferring the day’s records from Excel to Access, you would maintain the Excel table with the fields in the same order as they are found in Table1 of the Access database. The following Excel macro would accomplish this task.
A reference to the Microsoft ActiveX Data Objects 2.8 Library is required for the macro to run. Press Alt+F11 to get into the VBE and from the menu, click Tools > References. Navigate to the reference for Microsoft ActiveX Data Objects 2.8 Library (or the highest Objects Library number you see), select it and click OK.
Sub ExcelToAccess()
'Declare variables
Dim dbConnection As ADODB.Connection
Dim dbFileName As String
Dim dbRecordset As ADODB.Recordset
Dim xRow As Long, xColumn As Long
Dim LastRow As Long
'Go to the worksheet containing the records you want to transfer.
Worksheets("Sheet2").Activate
'Determine the last row of data based on column A.
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
'Create the connection to the database.
Set dbConnection = New ADODB.Connection
'Define the database file name
dbFileName = "C:\YourFilePath\Database1.accdb"
'Define the Provider and open the connection.
With dbConnection
.Provider = "Microsoft.ACE.OLEDB.12.0;Data Source=" & dbFileName & _
";Persist Security Info=False;"
.Open dbFileName
End With
'Create the recordset
Set dbRecordset = New ADODB.Recordset
dbRecordset.CursorLocation = adUseServer
dbRecordset.Open Source:="Table1", _
ActiveConnection:=dbConnection, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
Options:=adCmdTable
'Loop thru rows & columns to load records from Excel to Access.
'Assume row 1 is the header row, so start at row 2.
For xRow = 2 To LastRow
dbRecordset.AddNew
'Assume this is an 8-column (field) table starting with column A.
For xColumn = 1 To 8
dbRecordset(Cells(1, xColumn).Value) = Cells(xRow, xColumn).Value
Next xColumn
dbRecordset.Update
Next xRow
'Close the connections.
dbRecordset.Close
dbConnection.Close
'Release Object variable memory.
Set dbRecordset = Nothing
Set dbConnection = Nothing
'Optional:
'Clear the range of data (the records) you just transferred.
'Range("A2:H" & LastRow).ClearContents
End Sub
I’m having trouble with this line…
dbRecordset(Cells(1, xColumn).Value) = Cells(xRow, xColumn).Value
I get a runtime error of 3265.
Any idea on how I over come this. I figure it has to do with the data type between Excel and Access as noted in the book but even when they are exactly the same it still comes up.
Any ideas ?
Hi Jason – –
Thank you for buying the book.
Error 3265 can be related to fields in some way. Some possible reasons of what to carefully check:
Possible reason #1
Field names not being identical between Excel and Access. Sometimes a stray spacebar character can be in a column header and the header *looks* correct but it really is not. Make sure all headers have totally identical characters between applications.
Possible reason #2
Data types are different. I know you said you checked this, but if by chance a date field really has a text item in it somewhere as an example, this can cause a problem. Make sure all items in all fields in your Excel worksheet truly are of the type they should be for that field.
Possible reason #3
You posted this as the offending line:
dbRecordset(Cells(1, xColumn).Value) = Cells(xRow, xColumn).Value
Please make sure that however many fields you are exporting from Excel into the Access table are (a) of the same count as the count of fields in the destination Access table, and (b) is the same number as the xColumn variable which in my macro was 8:
For xColumn = 1 To 8
Change that 8 to a the actual column count if you have not done so.
Possible reason #4
Be sure the connection variable is set to the correct syntax for your Office version. In the book I have
.Provider = “Microsoft.ACE.OLEDB.12.0;Data Source=” & dbFileName & _
“;Persist Security Info=False;”
Which uses the ACE engine. If your Office version is prior to 2007 (such as 2003) you’d need to use the JET database engine provider with this syntax instead:
.Provider = “Microsoft.Jet.OLEDB.4.0”
or
.Provider = “Microsoft.Jet.OLEDB.4.0;” & “Data Source=” _
& dbFileName & “;” & “Extended Properties=Excel 8.0;”.
Please post back with how it turns out for you.
Tom
Thanks much appreciated. I just did an import from the spreadsheet within Access to set up the table and it now works fine.
Cheers,
Jason
Hi, with in the above code how would I delete all records in the access database using SQL within the code?.
I’m wanting to delete all records in the database first and then import from sheet 2.
Regards,
Jason
Hi Jason – –
In the above code, I used an example name of Table1 for the database table receiving data from Excel. If you want to delete all the records from a table, using Table1 as an example, the SQL statement would be
DELETE FROM Table1
Actually, I wrote a warning message relating to this in my book on page 313, calling attention to the required presence of the WHERE clause in the SQL because omitting it would delete every row (record) from the table. Take heed and make sure that’s what you want before you execute that line, because as I wrote, once the records are deleted they are deleted forever.
Hope this answers your question.
Tom
Hi Tom, thanks for your quick response. This is exactly what I’m after however as I’m new to this I’m having trouble getting the SQL to work.
Could you provide the SQL syntax needed to work with your example above.
Thanks again,
Jason
Here is a full example that I just tested on my 2010 system. This macro will delete all records in a table named tblEmployees in an Access database named Database1. Again, make sure you have established a reference to the Microsoft ActiveX Data Objects 2.8 Library (or 2.6 if you are using an earlier version of Office).
Sub DeleteAllRecords()
Dim dbConnection As ADODB.Connection
Dim dbCommand As ADODB.Command
Dim dbFileName As String
dbFileName = “C:\YourFilePath\Database1.accdb”
Set dbConnection = New ADODB.Connection
With dbConnection
.Provider = “Microsoft.ACE.OLEDB.12.0;Data Source=” & dbFileName & _
“;Persist Security Info=False;”
.Open dbFileName
End With
Set dbCommand = New ADODB.Command
Set dbCommand.ActiveConnection = dbConnection
dbCommand.CommandText = “DELETE FROM tblEmployees”
dbCommand.Execute , , adCmdText
Set dbCommand = Nothing
Set dbConnection = Nothing
End Sub
That’s great thanks Tom
Thank you very much for your great work.
It is one of the best ADO example.
I tried your VBE script BUT it doesn’t work for me. I don’t know the reason why?
I don’t know why either. It worked every time I tested it. It might depend on the version of Office you are using, or are you on a 64-bit ystem, or any IT-imposed security obstacles, or especially, what “doesn’t work” mean to you. Was there a run time error? Did the code execute but you got results different than what you expected? More details would help to figure out what happened.
Hi Danah,
Very pretty tips & your macro are very use full, thanks for the sharing,
I using MS excel 2010 and I have some problem with big data column to export from excel to access using your macro, I have 128 column total in my datas, so I change your column identify command from 8 to 128.
it going an error “type mismatch (80020005)” at
bRecordset(Cells(1, xColumn).Value) = Cells(xRow, xColumn).Value
this part I has changed.
‘—————————————————————————–
For xColumn = 1 To 128
dbRecordset(Cells(1, xColumn).Value) = Cells(xRow, xColumn).Value
Next xColumn
any idea to fix this? please advice and very thanks for your help
BR
JOe
Just to be clear, are you 100% positive of the following:
(1) Your data table in Excel is from column A to column DX which is column 128.
(2) You modified the path and name fo your Access database.
(3) You modified the Table name.
Your loop should be For xColumn = 1 to 128 and not For xColumn = 8 to 128
Great technique, Tom. Working perfectly. It really broadens the horizons of database management using Excel.
I was just wondering how I could prevent duplicate record entry, i.e. currently this code appends this data at the bottom of existing records. How can we prevent that.
Another variation could be, if some records are changed,and the code is run again, it should modify only the changed records, but this is somewhat advanced, so I would appreciate if you could shed some light on the first point, i.e. preventing duplicate recordset entry.
Thank you,
Asad Hamdani a.k.a. Josh Lorg
Hi Asad, thank you for your comment and for following my blog.
See if this link to a previous blog post I wrote can get you closer to what you are asking about.
http://www.atlaspm.com/toms-tutorials-for-excel/1065/
Regarding the ability to operate on only those records that have changed, yes, that is achievable but requires a few considerations. One consideration is to maintain a record of when the last workbook update or save happened, and have a behind-the-scenes date and time stamp next to each record so it can be identified as having changed (or not) after the last workbook update. Here is a link to another post I wrote about that:
http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-stamping-your-worksheet-changes-with-date-and-time/
Hi Tom,
I’m sorry for the late response. For some reason your reply didn’t get to my Mailbox, and I just checked back. Your link is indeed informative; however, my question was in the context of the ADO method to store records in Access Database. I have created a kind of Data Entry table in Excel and use this technique. User enters data every week and presses a button that runs this code, which in turn saves the Excel list as a recordset in the Database file. Here’s the issue; If the user clicks more than once, the same table gets appended at the bottom of Access DB. How can we prevent this? E.g. This code should not send the contents of Excel list / range to Access DB, if it already exists there. A complicated variation could be, that some of the items in the list have changed and some have not. So, the code should append only the rows / records from Excel, that have changed.
I was thinking that there would be some change in the SQL commands within the VBA code, or perhaps some combination of techniques. But you can guide the best, I believe.
Thank you.
Asad Hamdani a.k.a. Josh Lorg
Hi Asad – –
Is it that the duplicate entries specifically in your database file, and only thinking for a moment about your database file, are causing problems? If not, then I’d suggest that the action of deleting duplicate entries can be part of the code that executes when you import the data to Excel, **IF** there is no harm in the database containing duplicates in the first place. It would be the easiest and most time-saving approach in my opinion. Otherwise, if I am following along correctly, this sounds like a database issue with regards to disallowing duplicate entries there, which should be handled on that Access database side.
run time error ‘3265’
item cannot be found in the collection corresponding to the requested name or ordinal.
the error filed is dbRecordset(Cells(1, xColumn).Value) = Cells(xRow, xColumn)
suppose i delit this line macro run but in access dont show any data..please help me…
Difficult for me to guess without knowing your situation. Take a close look at my example and substitute the path and workbook names, and the database name and table names with what you are working with.
Tom U are super awesome ….may god bless you with good health and prosperity