+ Reply to Thread
Results 1 to 4 of 4

Error when exporting data into Access

  1. #1
    Laurent M
    Guest

    Error when exporting data into Access

    Hello,

    i'd like to export Excel data into an existing Access database. But when a
    field is empty in Excel, i get an error. I tried to put NOT NULL fields in
    the database but that doesn't change.
    Do you have any ideas?

    Sub ADOFromExcelToAccess()
    ' exports data from the active worksheet to a table in an Access database
    ' this procedure must be edited before use
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
    Dim DBPath As String

    DBPath = "J:\bdd.mdb"

    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=" & DBPath & ";"
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "INSTRUMENT", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    ' all records in a table
    r = 5 ' the start row in the worksheet
    Do While Len(Range("B" & r).Formula) > 0
    ' repeat until first empty cell in column A
    With rs
    .AddNew ' create a new record
    ' add values to each field in the record
    .Fields("Instrument_id") = Range("B" & r).Value
    .Fields("Name") = Range("C" & r).Value
    .Fields("ShortName") = Range("D" & r).Value
    .Fields("MasterInstrument_id") = Range("E" & r).Value
    .Fields("Instrument_Type") = Range("F" & r).Value
    .Fields("Share_Classe") = Range("G" & r).Value
    .Fields("Series") = Range("H" & r).Value
    .Fields("Currency_id") = Range("I" & r).Value
    .Fields("Flag") = Range("J" & r).Value
    .Fields("Management_fee") = Range("K" & r).Value
    .Fields("Administration_fee") = Range("L" & r).Value
    .Fields("Otherfee_x") = Range("M" & r).Value
    .Fields("Performance_fee") = Range("N" & r).Value
    .Fields("Hurdle_Rate") = Range("O" & r).Value
    .Fields("High_Watermark") = Range("P" & r).Value
    .Update ' stores the new record
    End With
    r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    End Sub


    Thanks !

  2. #2
    Jim Thomlinson
    Guest

    RE: Error when exporting data into Access

    The trick is if the cell is empty then you don't want to send anything...
    Here is how I would modify your first field. The same applies for the rest of
    the fields...

    if Range("B" & r).Value <> empty then .Fields("Instrument_id") = Range("B" &
    r).Value

    HTH
    "Laurent M" wrote:

    > Hello,
    >
    > i'd like to export Excel data into an existing Access database. But when a
    > field is empty in Excel, i get an error. I tried to put NOT NULL fields in
    > the database but that doesn't change.
    > Do you have any ideas?
    >
    > Sub ADOFromExcelToAccess()
    > ' exports data from the active worksheet to a table in an Access database
    > ' this procedure must be edited before use
    > Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
    > Dim DBPath As String
    >
    > DBPath = "J:\bdd.mdb"
    >
    > ' connect to the Access database
    > Set cn = New ADODB.Connection
    > cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    > "Data Source=" & DBPath & ";"
    > ' open a recordset
    > Set rs = New ADODB.Recordset
    > rs.Open "INSTRUMENT", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    > ' all records in a table
    > r = 5 ' the start row in the worksheet
    > Do While Len(Range("B" & r).Formula) > 0
    > ' repeat until first empty cell in column A
    > With rs
    > .AddNew ' create a new record
    > ' add values to each field in the record
    > .Fields("Instrument_id") = Range("B" & r).Value
    > .Fields("Name") = Range("C" & r).Value
    > .Fields("ShortName") = Range("D" & r).Value
    > .Fields("MasterInstrument_id") = Range("E" & r).Value
    > .Fields("Instrument_Type") = Range("F" & r).Value
    > .Fields("Share_Classe") = Range("G" & r).Value
    > .Fields("Series") = Range("H" & r).Value
    > .Fields("Currency_id") = Range("I" & r).Value
    > .Fields("Flag") = Range("J" & r).Value
    > .Fields("Management_fee") = Range("K" & r).Value
    > .Fields("Administration_fee") = Range("L" & r).Value
    > .Fields("Otherfee_x") = Range("M" & r).Value
    > .Fields("Performance_fee") = Range("N" & r).Value
    > .Fields("Hurdle_Rate") = Range("O" & r).Value
    > .Fields("High_Watermark") = Range("P" & r).Value
    > .Update ' stores the new record
    > End With
    > r = r + 1 ' next row
    > Loop
    > rs.Close
    > Set rs = Nothing
    > cn.Close
    > Set cn = Nothing
    > End Sub
    >
    >
    > Thanks !


  3. #3
    Laurent M
    Guest

    RE: Error when exporting data into Access

    ok that's cool

    but i have another problem now with this program

    how can i update data from my database?
    The function Update used with the recordset insert rows into the database,
    and i would like to update it. What can i do?

  4. #4
    Jamie Collins
    Guest

    Re: Error when exporting data into Access


    Laurent M wrote:
    > but i have another problem now with this program
    >
    > how can i update data from my database?


    Presumably something very similar, except instead of the .AddNew line:
    ..Filter = "Instrument_id = " & Range("B" & r).Value

    Jamie.

    --


+ 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