Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
exporting data from access to excel | Excel Discussion (Misc queries) | |||
Exporting data from access to excel | Excel Worksheet Functions | |||
Exporting Data from Excel to Access Problems | Excel Programming | |||
Exporting Data from Access | Excel Programming | |||
Exporting data to access | Excel Programming |