Excel to Access using VBA

[Wallegator]

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&quot
appAccess.OpenCurrentDatabase "c:\myfolder\mydatabase.mdb"
appAccess.DoCmd.OpenTable ("MyTable&quot
appAccess.Visible = True

 

[dsi]

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")
    rs.MoveFirst
    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
    Loop

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

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

Hope this helps!

 

[Amiel]

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.











Amiel
amielzz@netscape.net