How to connect Excel and Access using VBA?

to connect with other databases, when working in VBA, you can use either DAO (Data Access Objects), RDO (Remote Data Objects) or ADO (ActiveX Data Objects). After connecting to a database, you can manipulate its data. DAO, RDO and ADO are data access interfaces ie. they are object and programming models used to access data. Earlier, DAO was used to interface with local databases (viz. MS Access), RDO was used to interface with large databases such as Oracle and SQL Server. ADO was their replacement to interface with all types of data sources. Both DAO and ADO are commonly used while connecting to Microsoft Access Database.

Dear Harshal Shah...
Please, Trying to develop a piece of code which will connect to an MS Access database and then kick off a Query on the table Using the following code: Code: Public strDB As String Public A As Object Public Function runaccessmacro() Application.ScreenUpdating = False Set A = CreateObject("Access.Application") strDB = "S:\MI_Portal\TMNT\TMNT (TESTING).mdb" A.OpenCurrentDatabase strDB A.Visible = False A.DoCmd.RunMacro "mcrUpdateNumberStatus" A.Quit Set A = Nothing Application.ScreenUpdating = True End Function Sub run_access_macro() Call runaccessmacro End Sub But this does not work and am presented with error message '7866', which suggests the table can not be found or is locked by another user I have also tried to following code, but get the same error message: Code: Global oApp As Object Sub OpenAccess() Dim LPath As String Dim LCategoryID As Long 'Path to Access database LPath = "\\RLBYCCMPFS06\swap\MI_Portal\TMNT\TMNT (TESTING).mdb" 'Open Access and make visible Set oApp = CreateObject("Access.Application.11") oApp.Visible = True 'Open Access database as defined by LPath variable oApp.OpenCurrentDatabase LPath, False 'Open form called Categories filtering by CategoryID oApp.Visible = False oApp.DoCmd.RunMacro "mcrUpdateNumberStatus" oApp.Quit End Sub

Harshal, why would you want to connect them using VBA if you can do it without coding? It's good to have it hard coded but it usually returns an error nowadays ("Compile Error" and "Run Time Error").
Because in order to connect Excel and Access using VBA a Microsoft DAO Object Library is required, but MS Access2000 and later databases does not reference this object library.
DOING IT WITHOUT CODE: In Excel, Go to Data Tab > From Access > Select your Data Source > Click Open > Select Table > Import Data > Click OK and you are connected! You can refresh (forced) every now and then and you can also set it automatically, every xx minutes.
Simple and will save all your troubles.
Hope it helps