Storing data in MS Access from Excel VBA

[S Chakraborty]

I've been trying to store data from an Excel spreadsheet into an MS Access
table using Excel VBA Macro. I tried using the following macro. However, it
appears some of the functions I'm using are VB functions (e.g. OpenDatabase,
OpenRecordset, etc.) and may not be applicable in VBA. Also, the datatypes
Database and Recordset seem invalid in VBA. Is there a way to perform this
task using functions available in Excel VBA?

Would appreciate any help/pointers on this. Please cc me at
(e-mail address removed) as well.

Thanks in advance.

Soumyo

--- Excel VBA Macro I'm trying to use -------


Sub StoreInDatabase()
NumberOfRows = Application.WorksheetFunction.CountA(Range("A:A"))
Dim dbsKeywords As Database
Dim rstKeywords As Recordset
Dim kwName As String
Dim activityDate As DateTime

Set dbsKeywords = OpenDatabase("WebMarketing.mdb")
Set rstKeywords = _
dbsKeywords.OpenRecordset("KeywordSummary", dbOpenDynaset)

For i = 2 To NumberOfRows

With rstKeywords
.AddNew
!Date = strFirst
!Keyword = strLast
!AvgPosition = Cells(i, 4)
!TotalImpressions = Cells(i, 5)
!TotalClicks = Cells(i, 6)
!AvgBid = Cells(i, 8)
!CPC = Cells(i, 10)
!Conversions = Cells(i, 11)

.Update
.Bookmark = .LastModified
End With

Next i

rstKeywords.Close
dbsKeywords.Close

End Sub