I understand from what I have been told that to send data from Excel to Access automatically could be written in vba using ADO connection.

How could I write some code so that once the user clicks on a button that it takes a range of data and connects to a database and imports the data to the relevant table?
My data consists of columns of Text, Numbers and Currency. would it be best to change the Currency to Numbers for smooth transfer?

I am really stuck with this, can anyone help?

Thanks,
Jez

hi Jez,
It sounds a little bit as though you're jumping in at the deep-end. You might find this tutorial helpful..
www.justphukit.com/access/access-tutorials-1.php


Ed.
Ne auderis delere orbem rigidum meum!

The following code works for me when I need to export data to Access. The example assumes the data is the a worksheet named Data and the data excluding the headings is in a range named TheDataRange. The excel data is appended to an Access table named Shipments. Also the Excel workbook and Access files are in the same folder. If your Access file is a different Folder, you will need to use the full path and name for the Access database file. Remember to add the ADO object to your reference in VBA.

   Dim ws As Worksheet
    Dim rngData As Range
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset

    Dim DBPathName As String
    Dim cnnstring As String

    DBPathName = ThisWorkbook.Path & "\" & "SHIPMENTS_WITH_FORECAST.mdb"
    cnnstring = "Provider=microsoft.Jet.OLEDB.4.0;Data Source = " & DBPathName
    cnn.Open cnnstring



    Dim TheTable As String
    TheTable = "Shipments"

    With rst
        .ActiveConnection = cnn
        .LockType = adLockOptimistic
        .CursorType = adOpenKeyset
        .Open TheTable, Options:=adCmdTableDirect
    End With

    Set ws = Sheets("Data")
    Set rngData = ws.Range("TheDataRange")


    Dim I As Long
    Dim J As Long

    Dim Imax As Long
    Dim Jmax As Long

    Imax = rngData.Rows.Count
    Jmax = rngData.Columns.Count


    Dim FirstRow As Integer

    FirstRow = rngData(1, 1).Row

    For I = FirstRow To FirstRow + Imax - 1
        rst.AddNew
        For J = 1 To Jmax
            If Len(ws.Cells(I, J).Value) <> 0 Then
                rst.Fields(J - 1) = ws.Cells(I, J).Value
            End If
        Next J
        rst.Update
    Next I

    rst.Close
    cnn.Close
    Set cnn = Nothing
    Set rst = Nothing