Rameez - I hope that since your post you have been able to get your code running.
Anyhow, I wanted to make some comments about the code in your post.
An attempt to execute the block
Code:
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=" & CurrentProject.Path & "\slatool.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Temp", 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
from within Access could result in a sub or function not defined error as 'Range' is not part of the Access Object model but rather it is part of the Excel Object model. Also, as the comments from the block seem to imply, the intention of the procedure is to export data from the active worksheet to a table in an Access database. This implies that the above procedure is/was meant to be used from within Excel.
I tested the above block in an Excel workbook (set reference to ADO) with the code placed in the WorkBook module of the workbook. I also set up a "Temp" table in a slatool.mdb with the fields: FieldName1, FieldName2, and FieldName3 (all of type 'text') and placed the slatool.mdb file within the same directory as the Excel workbook. I only changed the code 'CurrentProject.Path' (highlighted in red) above to 'ThisWorkbook.Path' as CurrentProject.Path is not defined within the Excel Object model. Once I made this adjustment I was able to execute the code without any problems (the database should be closed) from within Excel.