Export data from Excel to Access (DAO)

If you want to export data to an Access table from an Excel worksheet, the macro example below shows how this can be done:

Sub DAOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
    Set db = OpenDatabase("C:\FolderName\DataBaseName.mdb") 
    ' open the database
    Set rs = db.OpenRecordset("TableName", dbOpenTable) 
    ' get all records in a table
    r = 3 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0 
    ' repeat until first empty cell in column A
        With rs
            .AddNew ' create a new record
            ' add values to each field in the record
            .Fields("FieldName1") = Range("A" & r).Value
            .Fields("FieldName2") = Range("B" & r).Value
            .Fields("FieldNameN") = Range("C" & r).Value
            ' add more fields if necessary...
            .Update ' stores the new record
        End With
        r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

The macro example assumes that your VBA project has added a reference to the DAO object library.
You can do this from within the VBE by selecting the menu Tools, References and selecting Microsoft DAO x.xx Object Library.
Use ADO if you can choose between ADO and DAO for data import or export.

Document last updated 2001-11-27 12:46:39

User comments:

Ole P. from Norway wrote (2006-03-13 00:05:34 CET):

Re: Error when running procedure User-Defined Type not Defined
Read the last paragraph in the article above...

Paul Boie from Portland, australia wrote (2006-03-12 00:30:17 CET):

Error when running procedure User-Defined Type not Defined
Please how do I solve this " User-Defined Type not Defined" problem

Thank You

Regards

Paul Boie

pboie@optusnet.com.au

Ole P. from Norway wrote (2006-02-01 08:48:36 CET):

Re: Set db = Nothing
This line should not open Access again.
This line removes the content of the variable db from your computer memory.
Your problem is elsewhere in your code.
I'm not sure what you are trying to achive, but if you are trying to get Access data from Excel using the code you have supplied, you are probably making things more difficult than necessary.

Sam from Reading, UK wrote (2006-01-30 13:23:30 CET):

Set db = Nothing
When I use this line it opens Access and won't let me close it unless I use Task Manager & End Task.

If I just leave this line out the Database stays in the computer memory & locks me out of the database.

Is there something wrong with my Excel/Access set up?

I get the same problem if I set write:

Dim objAccess as Object

Set objAccess = Access.Application
Set db = objAccess.Opendatabase(...)
db.CloseCurrentDatabase
objAccess.Quit

Ole P. from Norway wrote (2005-03-05 18:04:31 CET):

Re: Checking Duplicating
You will have to check to see if an entry already exists.
Take a look at the last comments on this page to get an idea how to do this.

Bala from Chennai wrote (2005-03-05 11:38:56 CET):

Checking Duplicating
Suppose we have one data there is already existing in Database,it throw error saying don't allow duplicate entries
how to solve this????

Ole P. from Norway wrote (2004-11-04 23:05:53 CET):

Re: Data Types
If you want to update date fields in a table you will have to format your input values to the proper date format.
The proper date format can be different from different database types, e.g. #31/01/2004#, '2004-01-31', 'Jan 31 2004'.
You will have to look up the proper date format for your database type.
Some databases will not allow you to update e.g. an integer field with a decimal value. You might have to convert your numeric values to the proper datatype, e.g. like this: CInt(MyDoubleValue).

Eric Young from Texas, USA wrote (2004-11-04 16:48:04 CET):

Data Types
I tried this code out on one of my current database table and kept getting a data type conversion error. After some trial and error I built a second table in my access database and made all the fields data types to text, which made this work.
Is there a way to do this with data types that inclued date and number fields? If there is please let me know my e-mail address is astrojigma@yahoo.com or just post on this web page. Thanks

Ole P. from Norway wrote (2004-05-13 20:01:17 CET):

Re: Do you need...
I'm really not sure since I have VB installed on all my computers.
My guess is that DAO is already present as a part of the OS or the Office installation.
You can find it from the Visual Basic Editor in e.g. Excel (press Alt+F11) and select the menu Tools, References.
In the dialog that opens you should be able to find e.g. "Microsoft DAO 3.51 Object Library" a litte bit down the list over available references.
An updated version of ADO can be downloaded from Microsoft, just search at microsoft.com for "MDAC" and you will find the latest version available for download.

Catherine from michigan wrote (2004-05-13 17:33:58 CET):

Do you need...
Do you need to have vb 6.0 installed on your computer in order to referance the dao?