Hi Ron,
Actually i hav added the reference in the very begining.
the excel sheet has 7 columns....ServerName,PrinterName.......
when im running the debugger it is pointing to ---.Fields("ServerName")
= Range("A" & r).Value
i had created a database by the name testaccess and tablename as
table1.
*******************************
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 1/4/2006 by Infosys
'
'
' 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=E:\testaccess\testaccess.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Table1", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 2
' 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("ServerName") = Range("A" & r).Value
.Fields("PrinterName") = Range("B" & r).Value
.Fields("ShareName") = Range("C" & r).Value
.Fields("PortName") = Range("D" & r).Value
.Fields("DriverName") = Range("E" & r).Value
.Fields("Comment") = Range("E" & r).Value
.Fields("Location") = Range("E" & r).Value
.Fields("Sepfile") = Range("E" & 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
