-1

I have three cells in an in Excel 2010 worksheet, say a1, a2, and a3. Every time the user runs my Excel macro, I need it to take the info in those cells and append it to an existing Access DB file. That is all that will be in the db file, just a running list.

So, I don't want to IMPORT from Access. I want this all to happen on the Excel side, preferably without opening access at all. Is this possible or can I just tell my husband to forget about it?

If it IS possible, can someone give me a clue as to how to go about it? Or where to learn about it? I'm ok with VBA in Excel but have zero experience with Access or even with databases.

Thanks!

Improve this question
3
  • 3
    Definitely possible. You can access the Microsoft Access Object Library from within Excel VBA, and make an ADO or ODBC connection to the database, and run a SQL statement in your Excel VBA. Commented May 14, 2014 at 18:06
  • 1
    The code from THIS QUESTION should hopefully get you started :) Otherwise, lots of tips on Google. Start there, and if you have specific problems implementing the code, update your question accordingly. As currently stated, questions asking for resources to learn about topics are generally off-limits at SO. Commented May 14, 2014 at 18:12
  • Thank you for the replies. And I'm sorry I did not mean to break any rules. I do appreciate the help. Commented May 15, 2014 at 18:46

1 Answer 1

Reset to default
6

Create a reference to the Microsoft DAO 3.6 object library and start playing with this code:

Sub DBInsert()
Dim DB As DAO.Database
Dim RS As DAO.Recordset

    ' open database
    Set DB = DAO.OpenDatabase("C:\Users\Myself\Desktop\MyDB.mdb")

    ' open table as a recordset
    Set RS = DB.OpenRecordset("Table1")

    ' add a record to the recordset
    RS.AddNew

    ' fill fields with data ... in this case from cell A1
    RS.Fields("Field1") = [A1]

    ' write back recordset to database
    RS.Update

    ' important! cleanup
    RS.Close

    ' forget to close the DB will leave the LDB lock file on the disk
    DB.Close

    Set RS = Nothing
    Set DB = Nothing
End Sub

Create a button on the sheet and place this code inside the Button_Click() so the user can send the data to your DB when all entry is done.

Further resources:

Office 2013 / Data Access / How do I ...

Choosing ADO or DAO for Working with Access Databases

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

@user3637835 : added further resources for "where to learn about it"
@user3637835 : welcome ... please check and consider accepting the answer so that also other users can benefit. Happy to receive more good questions from you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.