+ Reply to Thread
Results 1 to 4 of 4

Last record is not adding to database

Hybrid View

ramserp Last record is not adding to... 11-10-2009, 08:33 AM
qff Re: Last record is not adding... 11-10-2009, 09:27 AM
ramserp Re: Last record is not adding... 11-11-2009, 08:19 AM
ramserp Re: Last record is not adding... 11-12-2009, 08:43 AM
  1. #1
    Registered User
    Join Date
    03-21-2009
    Location
    Bangalore
    MS-Off Ver
    Excel 2003
    Posts
    60

    Smile Last record is not adding to database

    Hi Friends,

    When I exporting data from excel to a table in the Access Database. It took all records except last record. I hope it may be wrong in loop. Please kindly rectify it. I attached my code below.


    Sub ADOFromExcelToAccess()
    ' exports data from active worksheet to to a table in Access database
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim r As Long
    
        ' Connect to the access database
        Set cn = New ADODB.Connection
        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data Source=C:\Employee.mdb"
        
        ' open a record set
        Set rs = New ADODB.Recordset
        rs.Open "Emp", cn, adOpenKeyTest, adLockOptimistic, adCmdTable
        
        ' all records in a table
        
        r = 2 ' the start row in the worksheet
        
        Do While Len(Range("A" & r).Formula) > 0
        
            With rs
                .AddNew
                ' add values to each field in the record.
                .Fields("Empno") = Range("A" & r).Value
                .Fields("Empname") = Range("B" & r).Value
                .Fields("Desig") = Range("C" & r).Value
                .Fields("Address") = Range("D" & r).Value
                .Fields("Contactno") = Range("E" & r).Value
                .Fields("Salary") = Range("F" & r).Value
            End With
            
            r = r + 1 'next row
        Loop
        
        cn.Close
        Set cn = Nothing
        rs.Close
        Set rs = Nothing
     End Sub
    Attached Files Attached Files
    Last edited by NBVC; 11-10-2009 at 09:10 AM.

  2. #2
    Registered User
    Join Date
    10-04-2008
    Location
    uk
    Posts
    26

    Re: Last record is not adding to database

    Hi

    add .update to your code and move rs.close above cn.close and it should work

    Do While Len(Range("A" & r).Formula) > 0

    With rs
    .AddNew
    ' add values to each field in the record.
    .Fields("Empno") = Range("A" & r).Value
    .Fields("Empname") = Range("B" & r).Value
    .Fields("Desig") = Range("C" & r).Value
    .Fields("Address") = Range("D" & r).Value
    .Fields("Contactno") = Range("E" & r).Value
    .Fields("Salary") = Range("F" & r).Value
    .Update
    End With

    r = r + 1 'next row
    Loop

    rs.Close
    cn.Close
    Set cn = Nothing
    Set rs = Nothing
    regards
    qff

  3. #3
    Registered User
    Join Date
    03-21-2009
    Location
    Bangalore
    MS-Off Ver
    Excel 2003
    Posts
    60

    Re: Last record is not adding to database

    Hi qff, Thank you very much for replying my post. I have changed my as you told but it is giving Run-Time Error '3219' and error message is operation is not alowed in the context . Please help me in this regard.


    Thanks&Regards
    Ramesh

  4. #4
    Registered User
    Join Date
    03-21-2009
    Location
    Bangalore
    MS-Off Ver
    Excel 2003
    Posts
    60

    Re: Last record is not adding to database

    Hi, dff it's working now


    Thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1