current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote -1 down vote favorite

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!

share|improve this question
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. –  Michael Blaustein May 14 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. –  David Zemens May 14 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. –  user3637835 May 15 at 18:46

1 Answer 1

up vote 4 down vote

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

share|improve this answer
    
@user3637835 : added further resources for "where to learn about it" –  MikeD May 15 at 8:17
    
Thank you!!!!! I think that's exactly what I needed. –  user3637835 May 15 at 18:50
    
@user3637835 : welcome ... please check and consider accepting the answer so that also other users can benefit. Happy to receive more good questions from you. –  MikeD May 17 at 10:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.