+ Reply to Thread
Results 1 to 2 of 2

Error: exporting excel data to access

  1. #1
    Registered User
    Join Date
    12-13-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    4

    Error: exporting excel data to access

    Hi,

    Can someone help me on the error when I run my macro in exporting data from excel to access. I am getting a run-time error. some of the cells are empty/Null and won't allow me to transfer all the values in access. Would appreciate your help on this.

    Run Time Error.PNG

    Sub PushTableToAccess()
    Dim cnn As ADODB.Connection
    Dim MyConn
    Dim rst As ADODB.Recordset
    Dim i As Long, j As Long
    Dim Rw As Long

    Sheets("EMP").Activate
    Rw = Range("A60000").End(xlUp).Row

    Set cnn = New ADODB.Connection
    MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB

    With cnn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Open MyConn
    End With

    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseServer
    rst.Open Source:="tblEmployee", ActiveConnection:=cnn, _
    CursorType:=adOpenDynamic, LockType:=adLockOptimistic, Options:=adCmdTable

    'Load all records from Excel to Access.
    For i = 2 To Rw
    rst.AddNew
    For j = 1 To 15
    rst(Cells(1, j).Value) = Cells(i, j).Value
    Next j
    rst.Update
    Next i

    ' Close the connection
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing

    End Sub

  2. #2
    Registered User
    Join Date
    12-13-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Error: exporting excel data to access

    Need help please. Maybe there's a work around on the code.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Exporting data from Excel to Access
    By Dan27 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-04-2013, 08:16 PM
  2. Correcting Decimal Error when Exporting from Access to Excel
    By cricrazy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-28-2009, 08:56 PM
  3. Exporting data from access to excel
    By vinayak in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-11-2006, 03:25 AM
  4. Replies: 0
    Last Post: 06-06-2005, 10:05 AM
  5. Error when exporting data into Access
    By Laurent M in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-18-2005, 05:06 AM

Tags for this Thread

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