Link to home
Start Free TrialLog in
Avatar of jaspence
jaspence

asked on

Inserting records in Access database using Excel VBA

I am trying to use a form created in Excel to populate data in an Access database when a button is clicked. Here is an example of my code thus far.

Private Sub MCLApproved_Click()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=U:\MCL\datasources\MCL Work Request Tracker.accdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Work Request Tracker", cn, adOpenKeyset, adLockOptimistic, adCmdTable

rs.AddNew
rs.Fields("DATE SUBMITTED") = ActiveSheet.Range("P4").Value
rs.Fields("COMMITTED DUE DATE") = ActiveSheet.Range("P6").Value

rs.Close
db.Close

Application.ScreenUpdating = True

End Sub

Open in new window


I get an error "Run-Time error '2147217900 (80040e14)': Syntax error in FROM clause on line:
rs.Open "Work Request Tracker", cn, adOpenKeyset, adLockOptimistic, adCmdTable
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jaspence
jaspence

ASKER

Everything is inserting into the table now but I still get the error: Run-time error '404': Object required and de-bug goes to the db.close line
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Works now, thanks!