How to transfer data stored as variables in an Excel VBA program to an Access Database

Question

I have data from a series of Excel VBA calculations stored as variables in the VBA code. What I want to do at the end of the code is to transfer the data from these variables into an MS Access database as a new record.

The Access database already exists and is ready. Do I have to put the data in the variables into cells on a worksheet before I can transfer them to Access?

Here are an example of the variables in the VBA code that I am trying to store in the database (variable name corresponds to the database column heading)

Dim customerName As String
Dim customerAge As Integer
Dim customerSpend As Double

customerName = "Tim"
customerAge = 26
customerSpend = 12876

This is the code I've managed to write so far to try and transfer the data, but I'm stuck at the 'Range' part, as they are variables and not a range of cells:

Dim strPath As String
Dim objAccess As Object

strPath = "C:\db1.accdb"
Set objAccess = CreateObject("Access.Application")
Call objAccess.OpenCurrentDatabase(strPath)
objAccess.Visible = True


Call DoCmd.TransferSpreadsheet(acImport, acSpreadsheetTypeExcel12, CustomerDetails, Range)

How do I transfer the data stored in the variables in the VBA code into the corresponding columns in the Access database to make a new record?

Is that code in Excel, or in Access? DoCmd.TransferSpreadsheet is an Access thing, yet you're creating a late-bound Access application (why???). TransferSpreadsheet doesn't transfer variables or data, it transfers a spreadsheet, so it wants a Range that contains the data you want to "transfer". Dump the variables into a worksheet, transfer that worksheet. — Mathieu Guindon
Or, make an INSERT query that takes a parameter for every value you want to insert, and invoke that. There's tons of ways to do this. — Mathieu Guindon
Hi - thanks for the suggestions - this code was meant to be at the end of a VBA program running in Excel. I'd like to then open up an Access database and stored the results of the Excel VBA program in that database. — Ollie Williams
Would the INSERT query be in SQL but generated by the Excel VBA code? I like that idea. Do you have any links that might show me how to do that? — Ollie Williams

Parfait Parfait · Accepted Answer · 2017-08-13T04:27:44

Currently, you are treating Access as its MS Office frontend GUI application and not as a relational backend database that can receive SQL calls. DoCmd.TransferSpreadsheet is an Access application method requiring a saved Excel spreadsheet which you do not use.

However, you can pass variable values into an Access table using an SQL append query. And not just Access, any application layer code like VBA can connect to a database like Access and append/update/delete table data with SQL. Below demonstrates with DAO, the default Access database API, using the industry best practice of query parameterization.

Sub RunSQL()
    Dim objAccess  As Object
    Dim db As Object, qdef As Object
    Dim strPath As String, strSQL As String
    Dim customerName As String, customerAge As Integer, customerSpend As Double

    customerName = "Tim": customerAge = 26: customerSpend = 12876
    strPath = "C:\db1.accdb"        

    ' OPEN ACCESS APPLICATION
    Set objAccess = CreateObject("Access.Application")
    objAccess.OpenCurrentDatabase strPath

    ' OPEN INTERNAL DATABASE
    Set db = objAccess.CurrentDb

    ' PREPARE STATEMENT
    strSQL = "PARAMETERS [nameparam] TEXT(255), [ageparam] INTEGER, [spendparam] DOUBLE;" _
               & " INSERT INTO CustomerDetails ([name], [age], [spend]);"

    ' BUILD TEMP QUERYDEF
    Set qdef = db.CreateQueryDef("", strSQL)

    ' BIND PARAMS TO VARIABLES
    qdef!nameparam = customerName
    qdef!ageparam = customerAge
    qdef!spendparam = customerSpend

    ' EXECUTE ACTION QUERY
    qdef.Execute

    Set qdef = Nothing
    Set db = Nothing
    Set objAccess = Nothing
End Sub

How to transfer data stored as variables in an Excel VBA program to an Access Database

1 Answers