Connecting excel with access

[akar]

Hi All,

I am new to VBA in excel and trying to achieve a funcitonality. Any help will be greatly appreciated.

I have an excel sheet (priceReport.xls) and an access table(Database7.accdb). The functionality that i want to achieve is to enter two dates from the excel sheet to the "Table3" in the access db by a click of a command button. Everytime the command button is clicked, the Table3 in access is erased and the two dates from excel are entered in Table3.

I am having problems connecting to the database as I am not sure of ADO and DAO functionality for access2007. Following is the code that i have used:

Private Sub CommandButton1_Click()
Dim ar As ADODB.Recordset
Dim ad As ADODB.Connection
Dim databaseName As String, SQL As String, AccessConnect As String
Dim fromdt As Date
Dim todt As Date
Set ad = New ADODB.Connection
SQL = "Select * From Table3"
AccessConnect = "Driver={Microsoft Access Driver (*.accdb)};" & _
"Dbq=Database7.accdb;" & _
"DefaultDir=C:\Project1\MatchPrice;" & _
"Uid=;Pwd=;"
databaseName = "C:\Project1\MatchPrice\Database7.accdb"
ad.Open AccessConnect
Set ar = ad.Execute(SQL)
fromdt = Sheets("Sheet1").Cells(2, 2)
todt = Sheets("Sheet1").Cells(2, 3)
If ar.EOF Then
ar.AddNew
Else
ar!DateFrom = Null
ar!DateTo = Null
ar.Update
End If
ar!DateFrom = fromdt
ar!DateTo = todt
ar.Update

End Sub


I am getting a runtime error '-2147467259 (80004005)'

if anyone can give me a sample code or any corrections in this one, that will be great.


Thanks,
-Akar.