none
Difference way of Connection RRS feed

  • General discussion

  • I have search a lot in internet to find in which ways you can make connection between Excel and Access database with help of VBA.
    And if I say that I just find in generally just two main ways libraries. ADO and ACEDAO, i sea also some where about RDO (Stackoverflow/ difference between ado and dao).

    But I am wondering if there is more ways or libraries which can make connection between Excel worksheet and Access database? 

    In my case I want to read and write Access database with Excel worksheet. And it will update Access database whenever any change made.

    Any suggestion and help well be appreciated


    Thursday, April 14, 2016 10:49 AM

All replies

  • Hi Ali,

    We usually use ADO or Dao to connect data.

    >> I want to read and write Access database with Excel worksheet

    Do you mean you want to read and write data in Access database from Excel. If so, you could use ADO or DAO, but you will need to write your own code to update Access database when necessary.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, April 15, 2016 1:59 AM
  • Hi Edward,

    First of al thank you for your respons.

    And yeah you are right i have write some codes in Excel VBA to read and write from Excel the Access database. 
    Hier is just a exmaple what i use.

    Private Sub prosescoderingChanged_internet()
        Dim Conn As New ADODB.Connection
        Dim mrs As New ADODB.Recordset
        Dim sFind As String
        Dim str As String
        Dim iMax As String
        Dim sDbLocatie As String

        sDbLocatie = "C:\Users\Database.mdb" 'zet locatie database in sDBLocatie


        sFind = ThisWorkbook.Sheets("Sheet1").Cells(3, 4).Value
        MsgBox sFind


        If Not sFind = vbNullString Then    'voert functie uit als de cel niet leeg is

            'opent connectie met de database
            Conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sDbLocatie & ";"
            Conn.Open

            'zoeken in database
            mrs.ActiveConnection = Conn
            mrs.Open "SELECT Verbr_Naam FROM Verbruikers WHERE Verbr_Naam= '" & sFind & "'"


            If Not mrs.EOF Then 'kijkt of procescodering al bestaad
                mrs.Close
                MsgBox ("de procescodering bestaad al")
            Else
                mrs.Close
                mrs.Open "SELECT MAX(Verbr_Id) FROM Verbruikers" 'vraagd huidige maximale id nummer op

                iMax = mrs.Fields(0).Value
                mrs.Close
                iMax = iMax + 1 'telt er een bij op voor nieuw Id nummer tabel

                'voegt nieuwe procescodering toe aan dataabase
                mrs.Open "INSERT INTO Verbruikers(Verbr_Id, Verbr_naam) VALUES(" & iMax & ", '" & sFind & "')"
                Workbooks(1).RefreshAll 'vernieuwd ale Excel data op Werkblad 2
            End If
            Conn.Close 'sluit connectie met de database
        End If
    End Sub

    and some other where i use.

                                

    Sub oefenen()
        Dim db As Database
        Dim curDatabase As DAO.Database

        Set curDatabase = currentDb
        Set db = OpenDatabase("C:\Users\file.mdb")
        'DBEngine(0).Databases.Refresh
        'Debug.Print DBEngine(0)(0).Name

        db.Close
        Set db = Nothing

    End Sub

    But my questions is of there any other ways or libraries are that i can use for this purpose like above? 

    thanks in advance

    Friday, April 15, 2016 6:33 AM
  • Hi Ali,

    >> But my questions is of there any other ways or libraries are that i can use for this purpose like above?

    For another suggestion, I suggest you use linked table in Access, you could link to Excel range from Access, and while your data change in Excel, the new data will show up in Access, you could refer the link below.

    # Link to data in Excel

    https://support.office.com/en-us/article/Import-or-link-to-data-in-an-Excel-workbook-a1952878-7c58-47b1-893d-e084913cc958#__link_to_data

    In addition, while you want to change data in Excel file, you need to close the linked table in Access.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, April 18, 2016 3:35 AM