Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Import to Access null issue
Hi,
I used this code I found on MrExcel.com however, if my Excel spreadsheet has a null value in the field, it stops. Can someone assist in modifying the code below to account for a null value in a field? Thanks, 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 ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:\FolderName\DataBaseName.mdb;" ' open a recordset Set rs = New ADODB.Recordset rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable ' all records in a table r = 3 ' the start row in the worksheet Do While Len(Range("A" & 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("FieldName1") = Range("A" & r).Value .Fields("FieldName2") = Range("B" & r).Value .Fields("FieldNameN") = Range("C" & r).Value ' add more fields if necessary... .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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Import to Access null issue
Hello Smythe
t I think a good fix is to do a test for NULL first and then assign to the recordset like so: .Fields("FieldName1") = IIF(isNull(Range("A" & r).Value),"",Range("A" & r).Value) Or something along those lines. I might also try an explicit assignment like .Fields("FieldName1") = IIF(isNull(Range("A" & r).Value),NULL,Range("A" & r).Value) Try experimenting with this and I think you should solve your problem Regards, Chris (ct60) " wrote: Hi, I used this code I found on MrExcel.com however, if my Excel spreadsheet has a null value in the field, it stops. Can someone assist in modifying the code below to account for a null value in a field? Thanks, 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 ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:\FolderName\DataBaseName.mdb;" ' open a recordset Set rs = New ADODB.Recordset rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable ' all records in a table r = 3 ' the start row in the worksheet Do While Len(Range("A" & 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("FieldName1") = Range("A" & r).Value .Fields("FieldName2") = Range("B" & r).Value .Fields("FieldNameN") = Range("C" & r).Value ' add more fields if necessary... .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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Import to Access null issue
Excel does not have a concept of null values as is understood by a database.
If you test the value of an empty cell with IsNull() it returns False. However, IsEmpty() will return True. It depends on what the database field is expecting and if null or empty strings are acceptable One way: ..Fields("FieldName1") = Range("A" & r).Value & "" NickHK wrote in message oups.com... Hi, I used this code I found on MrExcel.com however, if my Excel spreadsheet has a null value in the field, it stops. Can someone assist in modifying the code below to account for a null value in a field? Thanks, 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 ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:\FolderName\DataBaseName.mdb;" ' open a recordset Set rs = New ADODB.Recordset rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable ' all records in a table r = 3 ' the start row in the worksheet Do While Len(Range("A" & 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("FieldName1") = Range("A" & r).Value .Fields("FieldName2") = Range("B" & r).Value .Fields("FieldNameN") = Range("C" & r).Value ' add more fields if necessary... .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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Import to Access null issue
All,
Thank you for your help. I ended up using Chris' suggestion. Worked like a charm. Thanks Again |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
access & excel issue | Excel Discussion (Misc queries) | |||
Data import issue from Access | New Users to Excel | |||
issue with excel 2003 data import | Excel Discussion (Misc queries) | |||
Excel to Access (new Issue) | Excel Programming | |||
I can import Access Tables. But, I can't import Access queries | Excel Programming |