VBA Export Data from Excel 2010 to Access 2010 (ADO)

[Jonnyb530]

Hello. I'm trying to create a macro that exports some user inputs from a spreadsheet in Excel 2010 into a database in Access 2010 (.accdb format). I have done this successfully in the past using a DAO connection and the .mdb format, but now I'm having trouble doing it with an ADO connection.

I have been able to successfully establish a connection to the databse. Here is the top part of my code, which is working properly.

Sub Output()
Dim DBFullName As String
Dim Cnct As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset

' Note Access database resides in same directory as workbook
DBFullName = ThisWorkbook.Path & "\DATABASENAME.accdb"

' Open the connection to the data source.
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

Set Recordset = New ADODB.Recordset
'**************
Now, I just need to know how to export into the access database. This database has just one table called "input", and I want the macro to create a new record in this table, and populate the fields with the inputs from excel. In the past, I have used code similar to:

'*********
Recordset.Open ("Input")

With Recordset
.AddNew

.Fields("OneField") = Sheets("EXCELSHEETNAME").Range("C3").Value
'Repeat for all fields

End with
'***********
But I keep getting an error message that I cannot perform the recordset.open operation with the ADO connection. Any help is appreciated, thank you!