Add record from Excel VBA Form to Access DB

[garden_rael]

I've been looking for the code to add a Record to an Access Database.

The user enters the info in a VBA Form and I want to transfer this data to Access.

How can I do this?

 

[xenou]

This is something that can grow in complexity as you develop your project. Often I try to do things to simplify/speed up the user inputs and to validate the data before it is inserted. However, just to get data into a database is relatively straightforward.

This example uses DAO, which is a good way to connect to Access databases, since DAO is very closely integrated with Access. Others prefer ADO.

The best thing to do is to start googling keywords like:
"Access Excel Integration", "Update Access database from Excel", "Excel Access DAO" "Excel Access ADO". You'll get loads of hits.

There is a tutorial here that you can follow (this comes from a forum member here, SydneyGeek):
http://www.datawright.com.au/excel_resources/excel_access_and_ado.htm

Note that my sample code is adapted from a project I created to keep track of hours spent at different machines by maintenance staff in a machine shop. The data entry is via an excel userform, but the data is stored in an Access DB.

[COLOR="Navy"]Public[/COLOR] [COLOR="Navy"]Function[/COLOR] InsertMachineHoursRecord() [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Boolean[/COLOR]

[COLOR="Navy"]Dim[/COLOR] SaveTime [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Date[/COLOR]
[COLOR="Navy"]Dim[/COLOR] db [COLOR="Navy"]As[/COLOR] DAO.Database
[COLOR="Navy"]Dim[/COLOR] rs [COLOR="Navy"]As[/COLOR] DAO.Recordset

[COLOR="SeaGreen"]'//Variables used to hold field values[/COLOR]
[COLOR="Navy"]Dim[/COLOR] strEmployee = Me.cboEmployee.Value
[COLOR="Navy"]Dim[/COLOR] dtmEventDate = Me.txtDate.Value
[COLOR="Navy"]Dim[/COLOR] lngEventTypeID = Me.cboType.Value
[COLOR="Navy"]Dim[/COLOR] dtmStartTime = me.txtStartTime.Value
[COLOR="Navy"]Dim[/COLOR] dtmEndTime = me.txtEndTime.Value
[COLOR="Navy"]Dim[/COLOR] strMachineNumber = Me.txtMachine.Value
[COLOR="Navy"]Dim[/COLOR] strDescription = Me.txtDescription.Value

[COLOR="SeaGreen"]'//Database Location[/COLOR]
[COLOR="Navy"]Const[/COLOR] DB_LOCATION = "C:\SomeFolder\SomeDatabase.mdb"

    [COLOR="SeaGreen"]'//If errors occur the function will exit with a return value of false (insertion failed)[/COLOR]
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] ErrHandler:

    [COLOR="SeaGreen"]'//Get Field Values from Form and store in variables[/COLOR]
    strEmployee = Me.cboEmployee.Value
    dtmEventDate = Me.txtDate.Value
    lngEventTypeID = Me.cboType.Value
    dtmStartTime = Me.txtStartTime.Value
    dtmEndTime = Me.txtEndTime.Value
    strMachineNumber = Me.txtMachine.Value
    strDescription = Me.txtDescription.Value

    [COLOR="SeaGreen"]'//Recommended: Validate all values here before inserting into DB[/COLOR]
    [COLOR="SeaGreen"]'//   -- Check that data is of right type and meets business rules[/COLOR]
    
    [COLOR="SeaGreen"]'//Table has a datecreated/datemodified timestamp for each record[/COLOR]
    SaveTime = Now
    
    [COLOR="SeaGreen"]'//Open Database[/COLOR]
    [COLOR="Navy"]If[/COLOR] db [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
       [COLOR="Navy"]Set[/COLOR] db = DAO.Workspaces(0).OpenDatabase(DB_LOCATION)
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

    [COLOR="SeaGreen"]'//Open Table[/COLOR]
    [COLOR="Navy"]If[/COLOR] rs [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]Set[/COLOR] rs = db.OpenRecordset("MachineRepairHours", dbOpenDynaset)
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    
    [COLOR="SeaGreen"]'//Create a new record[/COLOR]
    [COLOR="Navy"]With[/COLOR] rs
        .AddNew
        ![Employee] = strEmployee
        ![EventDate] = dtmEventDate
        ![EventTypeID] = lngEventTypeID
        ![StartTime] = dtmStartTime
        ![StopTime] = dtmStopTime
        ![HoursWorked] = ((dtmStopTime - dtmStartTime) * 24)
        ![MachineNumber] = strMachineNumber
        ![DateModified] = SaveTime
        ![Description] = strDescription
        [COLOR="SeaGreen"]'//Insert Record into Database[/COLOR]
        .Update
        InsertMachineHoursRecord = True [COLOR="SeaGreen"]'//SUCCESSFUL INSERTION[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]

    [COLOR="SeaGreen"]'//Note that we use recordset in this example, but equally effective[/COLOR]
    [COLOR="SeaGreen"]'//  is to create an update query command text and simply run the update query:[/COLOR]
    [COLOR="SeaGreen"]'//  (INSERT INTO Table (Field1, Field2) VALUES (Value1, Value2);[/COLOR]
    
[COLOR="SeaGreen"]'//Make sure we have closed the database[/COLOR]
My_Exit:
rs.Close
[COLOR="Navy"]Set[/COLOR] rs = [COLOR="Navy"]Nothing[/COLOR]
db.Close
[COLOR="Navy"]Set[/COLOR] db = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Function[/COLOR]

ErrHandler:
MsgBox Err.Description
[COLOR="Navy"]Resume[/COLOR] My_Exit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]