Pushing excel data in Access

Hello all,

I've been stuck for a while on this problem and I don't know if there is a solution... maybe someone can help. I created a frontend on an excel worksheet used for data entry. The add record button puts what the user has entered into a new row on the next worksheet. I'm looking for a way (vb macro) to push this data into an Access table rather than an excel worksheet. I tried the following, but it results in an error. Any help would be appreciated.

Dim currentDB As Database
Dim Data As Recordset

Public Sub Form_Load()

Set currentDB = OpenDatabase("Template.mdb")
Set currentRS = currentDB.OpenRecordset("Data", dbOpenDynaset)

End Sub

I get an error on when I set the database.

ActiveX component can't create an object...

thanks,
--patrick-->

Hello Padesso,

You can't use CurrentDB as a variable since the CurrentDb function returns an object variable of type Database. I think you expect something like this. Hope it will help.

'----------------------------
Public Sub Form_Load()
Dim MyDb As Database
Dim MySet As Recordset

Set MyDb = CurrentDB.OpenDatabase("Template.mdb")
Set MySet = CurrentDB.OpenRecordset("Data", dbOpenDynaset)

End Sub
'----------------------------

Thanks JamesLE,

Once I installed the proper DAO references, that very question came up and I changed the names and poof... An access entry from excel. magic.

Patrick