Thursday, September 13, 2007

insert data into Access Table from Excel

Bellow code lists two functions -
- First to insert data into Access Table from Excel
- Second to fetch data from the Access table to Excel (Sheet 2)

Sub InsertRecord()
Dim Com As New ADODB.Command
Set Com = New ADODB.Command

Dim ConStr As String, ComStr As String

ConStr = "Provider = Microsoft.jet.oledb.4.0; data source = H:\trial.mdb; Persist Security Info=False"
ComStr = "Insert into PersonalRecords(Name, Age, DOB) Values ('" + Range("B2").Text + "','" + Range("B3").Text + "','" + Range("B4").Text + "')"

Com.ActiveConnection = ConStr
Com.CommandText = ComStr

Dim RecordsAffected As Integer
Call Com.Execute(RecordsAffected, CommandTypeEnum.adCmdText Or ExecuteOptionEnum.adExecuteNoRecords)
End Sub

''''
Sub FatchRecords()
Dim Com As New ADODB.Command
Set Com = New ADODB.Command

Dim ConStr As String, ComStr As String

ConStr = "Provider = Microsoft.jet.oledb.4.0; data source = H:\trial.mdb; Persist Security Info=False"
ComStr = "Select * from PersonalRecords"

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Call rs.Open(ComStr, ConStr, adOpenForwardOnly)

Call Worksheets(2).Range("A2").CopyFromRecordset(rs)

With Sheet2.Range("A1")
Dim offset As Integer
offset = 0
Dim Field As ADODB.Field
For Each Field In rs.Fields
.offset(0, offset).Value = rs.Fields(offset).Name
offset = offset + 1
Next Field
End With

Sheet2.UsedRange.EntireColumn.AutoFit
End Sub

4 comments:

  1. hi ayush,

    this is sunil, this coding will work only if complete VB application is installed..if u have installed only office applicarion, i think ADODB will not be accessible, isn't it..???? gimme some clear pictutre...I ll be thankful.

    Regards,
    Sunil Thacker

    ReplyDelete
  2. Can u also tell me if we can get PASSWORD CHARACTER in INPUT BOX.... and not in list box, list box i know,..check if we can get password character in input box...!!

    Regards,
    Sunil Thacker

    ReplyDelete
  3. Can u also tell me if we can get PASSWORD CHARACTER in INPUT BOX.... and not in list box, list box i know,..check if we can get password character in input box...!!

    Regards,
    Sunil Thacker

    ReplyDelete
  4. Hi,

    Using the code you provided you can export data from Excel sheet to Access database in VBA. This exports the data one cell at a time. is there a way to export a whole sheet or a specified range in a sheet all at once, without using loops.

    Please advise.

    Thank You.

    ReplyDelete