none
Enter Line of data into MS Access with Excel VBA RRS feed

  • Question

  • Good Morning,
    I have created an Excel sheet. There are a couple copies, and users can access any one of them. The user enters data on the sheet, clicks a button and this section code below opens another workbook, gets a unique number to identify the copy of the workbook, and closes it again.
                    Workbooks.Open Filename:=filelocation & filenamex & ".xlsx "
                    Application.DisplayAlerts = True
                    Range("2:2").Insert
            Dim workordernumber
            workordernumber = Range("G1")
                    Range("A2") = workordernumber
                    Range("B2") = customername
                    Range("C2") = formatdater
                    Range("X2") = xp
                    ActiveWorkbook.Save
                    ActiveWorkbook.Close
    So this workbook stores all the numbers, dates, and customers. I would like to put this information in MS Access, but I don't know how to do it. I am comparatively new to VBA and very new to Access, I'm not sure how it all works. I have found enough in forums to know that I should probably be using DAO or ADO, but I'm not sure how that works. Also, this Access database would be on the server. Would several users accessing it, and potentially at the same time, cause a problem?

    Thanks in advance.
    Wednesday, February 1, 2017 2:12 PM

Answers

  • Hi iamrick,

    there are several ways to enter the data in Access from Excel.

    below is one of the example of that.

    Sub ADOFromExcelToAccess()
    ' exports data from the active worksheet to a table in an Access database
    ' this procedure must be edited before use
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
        ' connect to the Access database
        Set cn = New ADODB.Connection
        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
            "Data Source=C:\FolderName\DataBaseName.mdb;"
        ' open a recordset
        Set rs = New ADODB.Recordset
        rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable  
        ' all records in a table
        r = 3 ' the start row in the worksheet
        Do While Len(Range("A" & r).Formula) > 0 
        ' repeat until first empty cell in column A
            With rs
                .AddNew ' create a new record
                ' add values to each field in the record
                .Fields("FieldName1") = Range("A" & r).Value
                .Fields("FieldName2") = Range("B" & r).Value
                .Fields("FieldNameN") = Range("C" & r).Value
                ' add more fields if necessary...
                .Update ' stores the new record
            End With
            r = r + 1 ' next row
        Loop
        rs.Close
        Set rs = Nothing
        cn.Close
        Set cn = Nothing
    End Sub
    

    Reference:

    Export data from Excel to Access (ADO)

    other references:

    Using ADO to Export data from Excel worksheet (your host application) to Access Database Table.

    Excel VBA, Export Worksheet to Existing Access Table

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 2, 2017 3:15 AM
    Moderator

All replies

  • Hi iamrick,

    there are several ways to enter the data in Access from Excel.

    below is one of the example of that.

    Sub ADOFromExcelToAccess()
    ' exports data from the active worksheet to a table in an Access database
    ' this procedure must be edited before use
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
        ' connect to the Access database
        Set cn = New ADODB.Connection
        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
            "Data Source=C:\FolderName\DataBaseName.mdb;"
        ' open a recordset
        Set rs = New ADODB.Recordset
        rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable  
        ' all records in a table
        r = 3 ' the start row in the worksheet
        Do While Len(Range("A" & r).Formula) > 0 
        ' repeat until first empty cell in column A
            With rs
                .AddNew ' create a new record
                ' add values to each field in the record
                .Fields("FieldName1") = Range("A" & r).Value
                .Fields("FieldName2") = Range("B" & r).Value
                .Fields("FieldNameN") = Range("C" & r).Value
                ' add more fields if necessary...
                .Update ' stores the new record
            End With
            r = r + 1 ' next row
        Loop
        rs.Close
        Set rs = Nothing
        cn.Close
        Set cn = Nothing
    End Sub
    

    Reference:

    Export data from Excel to Access (ADO)

    other references:

    Using ADO to Export data from Excel worksheet (your host application) to Access Database Table.

    Excel VBA, Export Worksheet to Existing Access Table

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 2, 2017 3:15 AM
    Moderator
  • Thanks for your help, I think it would have worked, but another course worked better for me then. 

    Richard

    Wednesday, February 15, 2017 7:59 PM