Send data from Excel to Access?

[ossuary]

Hey all,

I'm an intermediate user of Excel with a fair amount of VBA experience, but I'm an absolutely newb at Access - I never use it.

I've been asked to do some work on an Access database, and I need to know how to send data from Excel into Access. Can anyone point me in the right direction for some VBA code in Excel to send new records from a worksheet into an existing database in Access? The only thing I need it to check for before sending the data over is whether or not the record number in column A of the Excel sheet is already listed in the first column / field of the database.

Any help here would be much appreciated... I'm in way over my head!

 

[xld]

Here is some starter ADO code

Sub AddData()
Dim oConn As Object
Dim oRS As Object
Dim sSQL As String

    Set oConn = CreateObject("ADODB.Connection")
    oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=" & "c:\bob.mdb"

    sSQL = "INSERT INTO Contacts (FirstName, LastName,Phone, Notes) " & _
           "        VALUES ('Bob','Phillips','01202 345678','me')"
    oConn.Execute sSQL
    
    oConn.Close
    Set oConn = Nothing
End Sub

Sub GetData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

    sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & "c:\bob.mdb"

    sSQL = "SELECT * From Contacts"
    Set oRS = CreateObject("ADODB.Recordset")
    oRS.Open sSQL, sConnect, adOpenForwardOnly, _
                adLockReadOnly, adCmdText

    ' Check to make sure we received data.
    If Not oRS.EOF Then
        ary = oRS.getrows
        MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
    Else
        MsgBox "No records returned.", vbCritical
    End If

    oRS.Close
    Set oRS = Nothing
End Sub

Sub UpdateData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oConn As Object
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

    sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & "c:\bob.mdb"

    sSQL = "SELECT * From Contacts"
    Set oRS = CreateObject("ADODB.Recordset")
    oRS.Open sSQL, sConnect, adOpenForwardOnly, _
                adLockReadOnly, adCmdText

    ' Check to make sure we received data.
    If oRS.EOF Then
        MsgBox "No records returned.", vbCritical
    Else
        sSQL = "UPDATE Contacts  " & _
               "       SET Phone = 'None' " & _
               "WHERE FirstName = 'Bob' AND LastName = 'Phillips'"
        oRS.ActiveConnection.Execute sSQL
    
        sSQL = "SELECT * From Contacts"
        oRS.ActiveConnection.Execute sSQL
        ary = oRS.getrows
        MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
    End If

    
    oRS.Close
    Set oRS = Nothing
End Sub

 

[ossuary]

So can you explain to me how exactly I would use this, and what areas of it are static and which need to be modified with the data I'm trying to send over? I see 3 different macros there, where do I put them and when do I call them?

 

[xld]

The macro names tell you what they do.

 

[ossuary]

... which is totally meaningless to someone who doesn't even understand how Access works or what the code is doing at any given step.