Here we are going to see how to connect to a database in Access using Excel VBA. Separating data and interface is a common practice in application development as it has the following benefits:<\/p>\n
1. Setting connection<\/strong><\/p>\n a. Set path name<\/p>\n Dim PthName As String b. Set database name<\/p>\n Dim DbName As String c. Set connection to DB<\/p>\n MS Office \u201897-03 MS Office 2007 \/2010 2. Execute SQL Command<\/strong><\/p>\n a. Set string for SQL command<\/p>\n Dim strSQL as String b.i. Execute create, update and delete commands<\/p>\n Conn.Execute strSQL<\/p>\n \u00a0b.ii. Execute query command<\/p>\n Dim RecSet As New ADODB.Recordset \u00a0Example: insert new student record into Students table<\/p>\n Dim strSQL\u000bstrSQL = “INSERT INTO Students (StudentName, Program, AdmYear) ” & _\u000b “VALUES (“”” & stgStrNa & “””,””” & StrPr & “””,””” & StrAY & “””); “\u000bConn.Execute strSQL<\/p>\n 3. Return Data (Queries)<\/strong><\/p>\n <\/strong>Putting result set into an Array makes data processing easier<\/p>\n Dim ResultArray as Variant 4. Close Connection<\/strong><\/p>\n a. Close the recordset<\/p>\n RecSet.Close b. Close the connection<\/p>\n Conn.Close Handling Connection Errors<\/strong><\/p>\n If an SQL command is incorrect or connection can\u2019t be established VBA will generate an error. If this happens we need to close connection and recordset.<\/p>\n On Error GoTo ErrorCloseConnection ErrorCloseConnection:<\/p>\n RecSet.Close Conn.Close Here we are going to see how to connect to a database in Access using Excel VBA. Separating data and interface is a common practice in application development as it has the following benefits: Data Sharing: multiple users can view and handle the same information. This is called concurrency a main property of databases! Improved […]<\/p>\n","protected":false},"author":22982,"featured_media":0,"parent":78,"menu_order":3,"comment_status":"open","ping_status":"closed","template":"page-templates\/full-width.php","meta":{"footnotes":""},"class_list":["post-158","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/158","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/users\/22982"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/comments?post=158"}],"version-history":[{"count":8,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/158\/revisions"}],"predecessor-version":[{"id":183,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/158\/revisions\/183"}],"up":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/78"}],"wp:attachment":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/media?parent=158"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nPthName = ActiveWorkbook.Path ‘if DB and workbook are in same folder<\/p>\n
\nDBName = \u201cDatabaseFile.mdb\u201d<\/p>\n
\nDim Conn As New ADODB.Connection
\nConn.Open “Provider=Microsoft.Jet.OLEDB.4.0; Data Source=” & PthName & DbName & “;\u201c<\/p>\n
\nDim Conn As New ADODB.Connection
\nConn.Open “Provider=Microsoft.ACE.OLEDB.12.0; Data Source=” & PthName & DbName & “;”
\nFirst you have to add ActiveX data objects 2.8 in your project references (Tools menu -> references)<\/p>\n
\nstrSQL = \u201cSELECT * FROM Students\u201d<\/p>\n
\nRecSet.Open strSQL, Conn<\/p>\n
\nResultArray = RecSet.GetRows
\nTotalRows = Ubound(ResultArray, 2)<\/p>\n
\nRecSet = Nothing<\/p>\n
\nConn = Nothing<\/p>\n
\n\u2018
\n\u2018Connection commands here
\n\u2018<\/p>\n
\nSet RecSet = Nothing<\/p>\n
\nSet Conn = Nothing<\/p>\n","protected":false},"excerpt":{"rendered":"