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

On Error GoTo ADO_ERROR

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

Cn.Open

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

Cn.Close

End If

Application.StatusBar = False

If Not oCm Is Nothing Then Set oCm = Nothing

If Not Cn Is Nothing Then Set Cn = Nothing

ADO_ERROR:

If Err <> 0 Then

Debug.Assert Err = 0

MsgBox Err.Description

Err.Clear

Resume Next

End If

End Sub

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




Access Table with Updated Values


22 comments:

  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?

    ReplyDelete
  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?

    ReplyDelete
  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

    ReplyDelete
  4. That works great, thanks for your help!

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

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

    ReplyDelete
  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: http://www.microsoft.com/downloads/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en

    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.

    ReplyDelete
  8. Hi Marshal

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

    Cheers
    Shasur

    ReplyDelete
  9. I am using Excel and Access 2007.

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

    ReplyDelete
  11. Anonymous7:56 PM

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

    ReplyDelete
  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

    ReplyDelete
  13. Anonymous10:06 AM

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

    ReplyDelete
  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

    ReplyDelete
  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 (http://vbadud.blogspot.com/2010/02/enable-developer-tab-in-office-2010.html)

    ReplyDelete
  16. Anonymous6:10 AM

    it work great , many thanks

    ReplyDelete
  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.

    ReplyDelete
  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

    ReplyDelete
  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.

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

    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

    ReplyDelete
  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.smile@gmail.com

    Thanks
    Vanitha G

    ReplyDelete
  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.

    ReplyDelete

StumbleUpon
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.