Saturday, February 07, 2009

How to Insert Data to an Access 2007 Database Table using Excel VBA

VBA code for inserting data to Access 2007 database

Inserting data to Access database can be performed from VBA using ADO commands. The following code uses ActiveX Data Objects (ADO) to insert data to an Access 2007 database (accdb)

The code needs a reference to Microsoft ActiveX Data Objects library

Microsoft ActiveX Data Objects library Reference

This example uses a sample table with three fields (ID – autogenerated one , Name, and Location)

Sub Simple_SQL_Insert_Data()

Dim Cn As ADODB.Connection '* Connection String

Dim oCm As ADODB.Command '* Command Object

Dim sName As String

Dim sLocation As String

Dim iRecAffected As Integer


Set Cn = New ADODB.Connection

Cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\comp\Documents\SampleDB.accdb;Persist Security Info=False"

Cn.ConnectionTimeout = 40


sName = "Krishna Vepakomma"

sLocation = "Narayanaguda"

Set oCm = New ADODB.Command

oCm.ActiveConnection = Cn

oCm.CommandText = "Insert Into SampleTable (UserName, Location) Values ('" & sName & "','" & sLocation & "')"

oCm.Execute iRecAffected

If iRecAffected = 0 Then

MsgBox "No records inserted"

End If

If Cn.State <> adStateClosed Then


End If

Application.StatusBar = False

If Not oCm Is Nothing Then Set oCm = Nothing

If Not Cn Is Nothing Then Set Cn = Nothing


If Err <> 0 Then

Debug.Assert Err = 0

MsgBox Err.Description


Resume Next

End If

End Sub

The above code uses ADO’s command object to execute the insert query.

Access Table with Updated Values


  1. Hi,

    This code works GREAT!!! However, when I tried to change the default "UserName" and "Location" to "FirstName" and "LastName" (b/c that's what they are in my database), It doesn't work--the line "Debug.Assert Err = 0" gets highlighted in VBA. Any ideas?

  2. Never Mind--I figured out my issue...

    but I now have a new question; what if you want the VBA to insert data from a specific cell to Access (instead of something static like an assigned UserName and Location). In other words, i plan on changing the contents of Cells A1 and B1 on a regular basis and each time I do, I want to be able to hit a button and have those specific cells updated to Access. Is this possible?

  3. You can do that by changing the existing hard-coded stuff like:

    Dim oWS As Worksheet

    Set oWS = ActiveSheet

    sName = oWS.Range("A1").Value

    sLocation = oWS.Range("B1").Value

  4. That works great, thanks for your help!

  5. I'm not being able to run this code.Can someone help me..what changes need to be done?

  6. Hi Moitrayee! Are you getting any error running this code. If so can you post them?

  7. Shasur,
    I ran this code and got the following errors when it ran:

    Could not find installable ISAM.
    Requested operation requires an OLE DB Session object, which is not supported by the current provider.
    The connection cannot be used to perform this operation. It is either closed or invalid in this context.

    I Googled some of the terms and came across this download from microsoft:

    Do you think installing it will help? Do you have any other ideas? I have to wait for the admins at work to install the above mentioned drivers on my computer.

  8. Hi Marshal

    What is the version of MS Access. I will try to use the same and simulate the error


  9. I am using Excel and Access 2007.

  10. When I step through the procedure, the first error occurs after the statement. The other errors seem to be be a result of the fact that there is no open connection.

  11. Anonymous7:56 PM

    AC how did you solved the "Debug.Assert Err = 0"?

  12. I'm using a varient of this script and it keeps stating that the receiving database is locked but i don't have it open.

    Any thoughts?

    I'm also having an issue with the Debug.Assert Err = 0 line

  13. Anonymous10:06 AM

    Could anybody tell me where I do paste this code in excle file?

  14. Anonymous9:55 PM

    Hi Larry

    You can remove the line

    Debug.Assert Err = 0

    It is for debugging . Has nothing to do with the logic

  15. @Anonymous. This code has to be placed in a code module of Excel file.

    You can go to the code module from any Excel workbook by pressing Alt + F11 or from the Developer tab. If Developer tab is not visible you can enable it (

  16. Anonymous6:10 AM

    it work great , many thanks

  17. Anonymous12:11 PM

    I want to enter a range of values e.g. a telephone cable has 1800 pairs which are distributed among some distribution cabinets such as:

    Cable Name Cabinet Cable Pairs
    CTH1 C1 1 to 300
    CTH1 C2 301 to 600
    CTH1 C3 601 to 1200
    CTH1 C2 1201 to 1500
    CTH1 C3 1501 to 1800

    I want to give the above cited ranges (i.e. 1 to 300) to access and it should create all pairs in between automatically, rather than entering numbers from 1 to 300 one by one or exporting from excel or any other source. If any one know, please do help me.

  18. Can you brief how it is stored in the database. Couple of rows with column names would help. I think the solution can be achieved using a SQL query using Like or Group etc

  19. Anonymous8:34 AM

    The data is stored as below:

    Cable_Name Cabinet Pairs
    CTH1 C1 1
    CTH1 C1 2
    CTH1 C1 3
    ……. …… …….
    CTH1 C1 300

    I want to enter all pairs by defining a range rather than one by one.

  20. You want it to be filtered right. You can try something like the

    Select * from Sheet1$ where Pairs between 1 and 300.

    You need to use ADO (Excel as a Database) for this. Please let us know if you are looking for something else

  21. Hi All

    Can anyone explain , how to add ADO control in VBA tool box... when i tried from components dialogue box, its not getting added.Instead of ADO ctrl, some other ctrl is getting added.Please give me reply to

    Vanitha G

  22. Anonymous2:44 AM

    When I run this code insert only one row. How can I insert more colomns and rows? Sometimes rows are empty.


Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.