Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel to Access using VBA

Excel to Access using VBA

Excel to Access using VBA

I have data in an Excel spreadsheet that I want to create a new record for in an Access table.  I can get the Access database and the table open but I can't figure out which VBA command to use to tell Access to add a record and add the Excel data.  I know that I can use Add-Ins to do this but my client wants to do it with VBA and a command button.  Thank you in advance for any help.  My existing "working" code is shown below.

Set appAccess = CreateObject("Access.Application.9")
appAccess.OpenCurrentDatabase "c:\myfolder\mydatabase.mdb"
appAccess.DoCmd.OpenTable ("MyTable")
appAccess.Visible = True

RE: Excel to Access using VBA

Here is some code that I use to add or update a record in Access. Although this is done in VB, it can easily be ported to Excel VBA. I trimmed this code from one of my projects as a general representation.

Dim db As Database
Dim rs As Recordset
Dim fProjNo As Field
Dim bExistFlag As Boolean

Sub Test()
    bExistFlag = False  'default to no record found

    'find existing data
    Set db = DBEngine.OpenDatabase("C:\YourFile.mdb")
    Set rs = db.OpenRecordset("TableName")
    Set fProjNo = rs.Fields("OrderNo")
    Do While Not rs.EOF
        If fProjNo.Value Like sJobNo Then  'sJobNo is the record to be added or updated
            bExistFlag = True   'flag that the record exists
            Exit Do
        End If

    If bExistFlag = False Then   'create new record
        rs.Fields("OrderNo").Value = sJobNo
    End If

    With rs
        !CustName = Range("A2").Text
        !CustStreet = Range("A3").Text
        '... and so on
    End With
    Set fProjNo = Nothing
End Sub

Hope this helps!

RE: Excel to Access using VBA

VBA and a command button in Excel or in Access. Which way is the data moving?

...any way, moving it into Access, the preferred way to accomplish it would be to 'link' the Excel sheet(s) to Access (using:'Tables/New/Link Table <then select filetype>' and select the Excel file), then create an append query in Access to add records to the access table(s). That's for moving Excell data to Access.

.. to move it into Excell:  Use the code above to open the Database and create a Recordset, then use:

ActiveSheet.cells(n,m).CopyFromRecordset <recordset>
to fill the cells with the data.

... and tell the boss Amiel said to 'loosen up'.

Have Fun.



Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close