Macro to Export Individual Cells to a Table in Access

[mp1115]

Hello All,

I am very new to VBA and macros and am trying to build a macro that will allow me to push the values from specific cells in an Excel worksheet to a table in Access. I know there are tons of posts across the internet about sending ranges or entire sheets to Access, but I can't wrap my head around sending data from a few specific cells.

For example, I have a table, called 'Table', in 'Path\Database.accdb'. Table has four columns, the first of which will be filled manually. The other three columns are called 'Name', 'Phone Number', and 'Address'. The corresponding values for these columns are spread across an Excel worksheet, let's say in cells A1, B3, and C6, respectively.

I'd like to have a macro on the Excel sheet that will push the values in these three cells to the appropriate columns in Table. I have made little to no progess aside from reading up some on ADO and playing around with code I've found in my searches. Ideally I'd like to use the INSERT INTO sql statement somewhere but I am open to all suggestions.

Thanks in advance for any insight or direction towards the solution.

 

[CalcSux78]

Welcome to the forum!

I've used ADO connections to push small or large amounts of data to a 2007+ access database with these steps:

In VB, Tools, References... add "Microsoft ActiveX Data Objects 2.8 Library" and "Microsoft ActiveX Data Objects Recordset 2.8 Library"

Back to your module, I prefer to setup a data source connection string as a constant. (do so in a standard module so it's available throughout your project.

Public Const sConn as String = "Data Source=K:\New Database\Database.accdb;"

Now we're ready to do some coding! It will make a difference if you're adding a new record or updating an existing record. I'm guessing you are only adding to the database.

Private Sub btnSUBMIT_Click()
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim tbl As String

tbl = "Table"

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & sConn
rs.Open tbl, cn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

With rs
    .AddNew
    .Fields("Name") = [A1]
    .Fields("Phone Number") = [B3]
    .Fields("Address") = [C6]
	'repeat for all required fields
    .Update
    .Close
End With
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

 

[mp1115]

That was perfect, thank you muchly!

I didn't even consider that I might have to update records sometimes (oops, haha).

The Table is a many-to-many with 2 primary keys and I had to change my gameplan so that this macro will add an entirely new record with the 2 keys.

So for the update issue, I think the best option would be to include code to first check if those primary keys already exist in Table and if so, update the record. If not, then add the record using the code you gave me. I think I can figure out the If, Else part but how would I adjust the code to update a record as opposed to add a new one?

Thanks again!