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

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

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

Excel Import to Access null issue
 

All,

Thank you for your help. I ended up using Chris' suggestion. Worked
like a charm.

Thanks Again