{"id":12102,"date":"2024-02-03T07:00:54","date_gmt":"2024-02-03T07:00:54","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/?p=913"},"modified":"2024-02-03T07:00:54","modified_gmt":"2024-02-03T07:00:54","slug":"how-to-connect-to-access-database-excel-macro","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2024\/02\/how-to-connect-to-access-database-excel-macro\/","title":{"rendered":"How to connect to Access Database – ADO Connection String"},"content":{"rendered":"

Using Excel Macros (VBA) you can connect to any Databases like SQL, Oracle or Access DB. In this Article you will learn, how you can do a connection with an Access Database. Access Database connection string is different for Access 2003 (*.mdb)<\/strong> and Access 2007\/2010 (*.accdb)<\/strong> because Drivers are different for both the databases.<\/p>\n

For Access 2003 Database the Provider is: Provider=Microsoft.Jet.OLEDB.4.0<\/strong>.
\nFor Access 2007\/2010 the Provider is Provider=Microsoft.ACE.OLEDB.12.0<\/strong>
\n
\nBefore running the below code, you need to Add reference for ADO DB Connection. Follow below steps to Add Reference:<\/p>\n

How to add References in excel <\/h2>\n
\n1. Go to VB Editor Screen (Alt+F11)
\n2. Tools \u2013> References\u2026
\n3. From the List of Available References Select \u201cMicrosoft ActiveX Data Objects 2.0 Library\u201d You can select 2.0 Version or any other higher version of this reference.
\n4. Click OK\n<\/div>\n

Connection with Access 2003 Database<\/h2>\n
\r\n\r\nSub ADO_Conn()\r\n\r\nDim conn As New Connection\r\nDim rs As New Recordset\r\n   \r\n    strcon = \"Provider=Microsoft.Jet.OLEDB.4.0;\" & _\r\n    \"Data Source=E:\\Student.accdb;\" & _\r\n    \"User Id=admin;Password=\"\r\n    \r\n    conn.Open (strcon)\r\n  \r\n    qry = \"SELECT * FROM students\"\r\n    rs.Open qry, conn, adOpenKeyset\r\n    \r\n    rs.Close\r\n    conn.Close\r\n    \r\nEnd Sub\r\n<\/code>\r\n<\/code><\/pre>\n
Connection with Access 2007\/2010 Database<\/h2>\n
\r\n\r\nSub ADO_Conn()\r\n\r\nDim conn As New Connection\r\nDim rs As New Recordset\r\n   \r\n    strcon = \"Provider=Microsoft.ACE.OLEDB.12.0;\" & _\r\n    \"Data Source=E:\\Student.accdb;\" & _\r\n    \"User Id=admin;Password=\"\r\n    \r\n    conn.Open (strcon)\r\n  \r\n    qry = \"SELECT * FROM students\"\r\n    rs.Open qry, conn, adOpenKeyset\r\n    \r\n    rs.Close\r\n    conn.Close\r\n    \r\nEnd Sub\r\n<\/code>\r\n<\/code><\/pre>\n
Read this Also:<\/h2>\n