Though, This example is basic but it give a better idea , how to connect excel with access to fire the sql query from excel to access. i personally like this example to demonstrate how you can use access effectively where multiple user can use the single excel application and use the ms access as back end as a database.
So, this one is a pretty simple employee login/logout tool that use some short of VBA to connect Excel with Access and fire some sql queries to update the database and retrieve the data from database.
First thing is : how to connect excel with access database
Function Connection(blnOpen As Boolean) If blnOpen = False Then ObjConnection.Close Else Set ObjConnection = CreateObject("ADODB.Connection") ObjConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\table.accdb;" ObjConnection.Open End If End Function
Above code will create a connection with access and open it. this connection vary from different office version, we would need to use different connection string to create a connection with excel 2003 or 2007 or later versions.
next i have wrote some other procedures to update database with the Login and Logout, that will check if the entered employee id is exist then it will do Login,
Click here to enrol today to our training program to know more about —
- Connection string
- How to create Connection with all version of Access from excel
- How to fire sql queries
- How to get data from access to excel.