Insert Non Empty Cell into Access Database by using Excel vba

[chacha123]

Hi All,


Any idea how to insert non empty cell into Access Database by using Excel vba.


The below code is I found out.

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=D:\SaveData\Database3.mdb;"
'open a recordset
Set rs = New ADODB.Recordset
rs.Open "Table1", 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
'If Not IsEmpty(Len(Range("B" & r))) Then
.AddNew 'create a new record
'add values to each field in the record
.Fields("IName") = Range("A" & r).Value
.Fields("INumber") = Range("B" & r).Value
.Fields("IAddress") = Range("C" & r).Value
'add more fields if necessary…
.Update 'stores the new record
'Else: Exit Sub
'End If
End With
r = r + 1 'next row
Loop


rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

The below is my insert data




[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Tel[/TD]
[TD]Address[/TD]
[/TR]
[TR]
[TD]Own[/TD]
[TD][/TD]
[TD]10,bb[/TD]
[/TR]
[TR]
[TD]Ali[/TD]
[TD]306660[/TD]
[TD]23,a[/TD]
[/TR]
[TR]
[TD]Abu[/TD]
[TD]5220[/TD]
[TD]7,c[/TD]
[/TR]
[TR]
[TD]Stone[/TD]
[TD][/TD]
[TD]6,b[/TD]
[/TR]
[TR]
[TD]Lily[/TD]
[TD]9600[/TD]
[TD]9,m[/TD]
[/TR]
</tbody>[/TABLE]


I need the data insert into database like this
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Ali[/TD]
[TD]306660[/TD]
[TD]23,a[/TD]
[/TR]
[TR]
[TD]Abu[/TD]
[TD]5220[/TD]
[TD]7,c[/TD]
[/TR]
[TR]
[TD]Lily[/TD]
[TD]9600[/TD]
[TD]9,m[/TD]
[/TR]
</tbody>[/TABLE]


Any idea? Thanks

 

[LockeGarmin]

Try changing the following line in your code:

[COLOR=#333333]If Not IsEmpty(Len(Range("B" & r))) Then[/COLOR]

To this:

[COLOR=#333333]If Not IsEmpty(Range("B" & r)) Then[/COLOR]