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
Hi,
ReplyDeleteThis 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?
Never Mind--I figured out my issue...
ReplyDeletebut 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?
You can do that by changing the existing hard-coded stuff like:
ReplyDeleteDim oWS As Worksheet
Set oWS = ActiveSheet
sName = oWS.Range("A1").Value
sLocation = oWS.Range("B1").Value
That works great, thanks for your help!
ReplyDeleteI'm not being able to run this code.Can someone help me..what changes need to be done?
ReplyDeleteHi Moitrayee! Are you getting any error running this code. If so can you post them?
ReplyDeleteShasur,
ReplyDeleteI 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.
Hi Marshal
ReplyDeleteWhat is the version of MS Access. I will try to use the same and simulate the error
Cheers
Shasur
I am using Excel and Access 2007.
ReplyDeleteWhen 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.
ReplyDeleteAC how did you solved the "Debug.Assert Err = 0"?
ReplyDeleteI'm using a varient of this script and it keeps stating that the receiving database is locked but i don't have it open.
ReplyDeleteAny thoughts?
I'm also having an issue with the Debug.Assert Err = 0 line
Could anybody tell me where I do paste this code in excle file?
ReplyDeleteHi Larry
ReplyDeleteYou can remove the line
Debug.Assert Err = 0
It is for debugging . Has nothing to do with the logic
@Anonymous. This code has to be placed in a code module of Excel file.
ReplyDeleteYou 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)
it work great , many thanks
ReplyDeleteI want to enter a range of values e.g. a telephone cable has 1800 pairs which are distributed among some distribution cabinets such as:
ReplyDeleteCable 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.
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
ReplyDeleteThe data is stored as below:
ReplyDeleteCable_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.
You want it to be filtered right. You can try something like the
ReplyDeletefollowing:
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
Hi All
ReplyDeleteCan 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
When I run this code insert only one row. How can I insert more colomns and rows? Sometimes rows are empty.
ReplyDelete