{"id":11633,"date":"2022-09-26T18:12:18","date_gmt":"2022-09-26T09:12:18","guid":{"rendered":"https:\/\/prodskill.com\/?p=11633"},"modified":"2022-10-04T23:10:08","modified_gmt":"2022-10-04T14:10:08","slug":"excel-vba-coding-pattern-ado-db-connection-string","status":"publish","type":"post","link":"https:\/\/prodskill.com\/en\/excel-vba-coding-pattern-ado-db-connection-string\/","title":{"rendered":"VBA Coding Pattern: Create ADO DB Connection String"},"content":{"rendered":"<p>This article examines the implementation of functions for creating and editing ADO DB Connection Strings in Excel VBA.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity is-style-dots\"\/>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">&lt;&lt;Table of Contents&gt;&gt;<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewbox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewbox=\"0 0 24 24\" version=\"1.2\" baseprofile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/prodskill.com\/en\/excel-vba-coding-pattern-ado-db-connection-string\/#1_ADO_%EA%B0%9C%EB%85%90%EA%B3%BC_%ED%95%84%EC%9A%94%EC%84%B1\" >1. ADO concept and necessity<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/prodskill.com\/en\/excel-vba-coding-pattern-ado-db-connection-string\/#11_ADOActiveX_Data_Objects_%EA%B0%9C%EB%85%90\" >1.1. ActiveX Data Objects (ADO) concepts<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/prodskill.com\/en\/excel-vba-coding-pattern-ado-db-connection-string\/#12_ADOActive_Data_Objects_%ED%95%84%EC%9A%94%EC%84%B1\" >1.2. Need for Active Data Objects (ADO)<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/prodskill.com\/en\/excel-vba-coding-pattern-ado-db-connection-string\/#2_ADO_DB_Connection_String_%EC%83%9D%EC%84%B1\" >2. Create ADO DB Connection String<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/prodskill.com\/en\/excel-vba-coding-pattern-ado-db-connection-string\/#21_%EC%B0%B8%EC%A1%B0_%EC%B6%94%EA%B0%80\" >2.1. Add reference<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/prodskill.com\/en\/excel-vba-coding-pattern-ado-db-connection-string\/#22_ADO_DB_Connection_String_%EC%83%9D%EC%84%B1_%EA%B8%B0%EB%8A%A5_%EA%B5%AC%ED%98%84\" >2.2. Implementation of ADO DB Connection String creation function<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\" id=\"1._ADO_\uac1c\ub150\uacfc_\ud544\uc694\uc131\"><span class=\"ez-toc-section\" id=\"1_ADO_%EA%B0%9C%EB%85%90%EA%B3%BC_%ED%95%84%EC%9A%94%EC%84%B1\"><\/span>1. ADO concept and necessity<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"1.1._ADO(Active_Data_Objects)_\uac1c\ub150\"><span class=\"ez-toc-section\" id=\"11_ADOActiveX_Data_Objects_%EA%B0%9C%EB%85%90\"><\/span>1.1. ActiveX Data Objects (ADO) concepts<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>ADO is a library that can access various data stores and CRUD (Create, Read, Update, Delete) data.<\/p>\n\n\n\n<p>The Microsoft docs site describes ADO as follows.<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p>ActiveX Data Objects (ADO) is a high-level, easy-to-use interface to OLE DB. OLE DB is a low-level, high-performance interface to a variety of data stores. Both ADO and OLE DB can work with relational (tabular) and nonrelational (hierarchical or stream) data.<\/p><p>ADO provides a layer of abstraction between your client or middle-tier application and the low-level OLE DB interfaces. ADO uses a small set of Automation objects to provide a simple and efficient interface to OLE DB. This interface makes ADO a good choice for developers in higher level languages, such as Visual Basic and VBScript, who want to access data without having to learn the intricacies of COM and OLE DB.<\/p><\/blockquote>\n\n\n\n<p>source:&nbsp;<a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/ado\/guide\/ado-introduction?view=sql-server-ver15\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/docs.microsoft.com\/en-us\/sql\/ado\/guide\/ado-introduction?view=sql-server-ver15<\/a>&nbsp;<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"1.2._ADO(Active_Data_Objects)_\ud544\uc694\uc131\"><span class=\"ez-toc-section\" id=\"12_ADOActive_Data_Objects_%ED%95%84%EC%9A%94%EC%84%B1\"><\/span>1.2. Need for Active Data Objects (ADO)<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>previous post&nbsp;<a href=\"https:\/\/prodskill.com\/en\/excel-vba-lecture-1-overview\/#13_%EC%97%91%EC%85%80_VBA%EB%A1%9C_%EB%AC%B4%EC%97%87%EC%9D%84_%ED%95%A0_%EC%88%98_%EC%9E%88%EB%82%98\">Excel VBA Lecture (1): Excel VBA Overview_1.3. What can you do with Excel VBA?<\/a>&nbsp;Among the contents introduced in , there were contents related to the database.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><a href=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-180.png\"><img loading=\"lazy\" decoding=\"async\" width=\"977\" height=\"548\" src=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-180.png\" alt=\"\uc5d1\uc140 VBA\ub85c \ud560 \uc218 \uc788\ub294 \uac83\ub4e4\uc911 Database \uad00\ub828\" class=\"wp-image-11634\" srcset=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-180.png 977w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-180-300x168.png 300w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-180-768x431.png 768w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-180-18x10.png 18w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-180-24x13.png 24w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-180-36x20.png 36w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-180-48x27.png 48w\" sizes=\"auto, (max-width: 977px) 100vw, 977px\" \/><\/a><figcaption>Database-related things you can do with Excel VBA<\/figcaption><\/figure>\n<\/div>\n\n\n<p>Among them, ADO is required when searching, saving, changing, or deleting data related to the database. There are several database-related libraries (ADO, OLE DB, ODBC, etc.) that can be used in Excel VBA, but ADO is the most general-purpose and convenient to use.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"2._ADODB_Connection_String_\uc0dd\uc131\"><span class=\"ez-toc-section\" id=\"2_ADO_DB_Connection_String_%EC%83%9D%EC%84%B1\"><\/span>2. Create ADO DB Connection String<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"2.1._\ucc38\uc870_\ucd94\uac00\"><span class=\"ez-toc-section\" id=\"21_%EC%B0%B8%EC%A1%B0_%EC%B6%94%EA%B0%80\"><\/span>2.1. Add reference<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>This code needs to add references to two libraries.<\/p>\n\n\n\n<p>\u25bc Add Microsoft ActiveX Data Objects 2.8 Library Reference<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><a href=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-181.png\"><img loading=\"lazy\" decoding=\"async\" width=\"524\" height=\"338\" src=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-181.png\" alt=\"Microsoft ActiveX Data Objects 2.8 Library \ucc38\uc870 \ucd94\uac00\" class=\"wp-image-11635\" srcset=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-181.png 524w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-181-300x194.png 300w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-181-18x12.png 18w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-181-24x15.png 24w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-181-36x23.png 36w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-181-48x31.png 48w\" sizes=\"auto, (max-width: 524px) 100vw, 524px\" \/><\/a><figcaption>Added Microsoft ActiveX Data Objects 2.8 Library Reference<\/figcaption><\/figure>\n<\/div>\n\n\n<p>\u201cMicrosoft ActiveX Data Objects 6.1 Library\u201d is a more recent version, but 2.8 is usually used because there is no significant difference.<\/p>\n\n\n\n<p>\u25bc Add Microsoft OLE DB Service Component 1.0 Type Library Reference<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><a href=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-182.png\"><img loading=\"lazy\" decoding=\"async\" width=\"524\" height=\"338\" src=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-182.png\" alt=\"Microsoft OLE DB Service Component 1.0 Type Library \ucc38\uc870 \ucd94\uac00\" class=\"wp-image-11636\" srcset=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-182.png 524w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-182-300x194.png 300w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-182-18x12.png 18w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-182-24x15.png 24w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-182-36x23.png 36w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-182-48x31.png 48w\" sizes=\"auto, (max-width: 524px) 100vw, 524px\" \/><\/a><figcaption>Added Microsoft OLE DB Service Component 1.0 Type Library reference<\/figcaption><\/figure>\n<\/div>\n\n\n<p>Add a reference to use the MSDASC.DataLinks class, which allows you to create or edit Connection Strings.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"2.2._\uae30\ub2a5_\uad6c\ud604\"><span class=\"ez-toc-section\" id=\"22_ADO_DB_Connection_String_%EC%83%9D%EC%84%B1_%EA%B8%B0%EB%8A%A5_%EA%B5%AC%ED%98%84\"><\/span>2.2. Implementation of ADO DB Connection String creation function<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>previous post&nbsp;<a href=\"https:\/\/prodskill.com\/en\/excel-vba-lecture-10-self-developed-tools\/#57_Excel_Data_Downloader\">Excel VBA Lecture (10): Tools being developed and used with Excel VBA_5.7. Excel Data Downloader<\/a>There was a button to create a connection string as follows.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><a href=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-183.png\"><img loading=\"lazy\" decoding=\"async\" width=\"939\" height=\"665\" src=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-183.png\" alt=\"Excel Data Downloader: Connection String \uc0dd\uc131 \ubc84\ud2bc\" class=\"wp-image-11637\" srcset=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-183.png 939w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-183-300x212.png 300w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-183-768x544.png 768w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-183-18x12.png 18w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-183-24x17.png 24w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-183-36x25.png 36w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-183-48x34.png 48w\" sizes=\"auto, (max-width: 939px) 100vw, 939px\" \/><\/a><figcaption>Excel Data Downloader: Create Connection String Button<\/figcaption><\/figure>\n<\/div>\n\n\n<p>The click event processing code of the \u201cConnection String\u201d button in the screen above is as follows.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"15\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\" data-no-auto-translation=\"\">Private Sub btnBuildConnectionString_Click()\nOn Error GoTo 0\n\n    Dim cn As ADODB.Connection, MSDASCObj As MSDASC.DataLinks, oCurrentRange As Range\n    Dim eOrgXlEnableCancelKey As XlEnableCancelKey\n\n    Set oCurrentRange = ActiveCell\n\n    Set MSDASCObj = New MSDASC.DataLinks\n\n    eOrgXlEnableCancelKey = Application.EnableCancelKey\n    Application.EnableCancelKey = xlDisabled\n    Set cn = New ADODB.Connection\n    cn.ConnectionString = oCurrentRange\n    If MSDASCObj.PromptEdit(cn) = True Then\n        oCurrentRange = cn.ConnectionString\n    End If\n\n    Set cn = Nothing\n    Set MSDASCObj = Nothing\n\n    Application.EnableCancelKey = eOrgXlEnableCancelKey\nEnd Sub<\/pre>\n\n\n\n<p>This code implements the function of creating or editing Connection String by using MSDASC.DataLinks and ADODB.Connection classes.<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Line 7: Assign the current location cell (ActiveCell) in the sheet to oCurrentRange. Since you can input multiple connection strings downward from cell C16, first select the current location and then execute the function.<\/li><li>Lines 13-14: Create an ADODB.Connection object (cn) and assign its ConnectionString property to the oCurrentRange value.<\/li><li>Line 15: Execute by passing the ADODB.Connection object (cn) as a parameter to the PromptEdit function of the MSDASC.DataLinks object. When this function is executed, the \u201cData Connection Properties\u201d window appears as follows. (The list of providers can be different depending on the user environment because the list of installed providers is shown. The \u201cConnection\u201d tab below is the screen that is displayed when \u201cMicrosoft OLE DB Provider for ODBC Drivers\u201d is selected.)<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-gallery has-nested-images columns-default is-cropped wp-block-gallery-1 is-layout-flex wp-block-gallery-is-layout-flex\">\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-184.png\"><img loading=\"lazy\" decoding=\"async\" width=\"421\" height=\"531\" data-id=\"11638\" src=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-184.png\" alt=\"\" class=\"wp-image-11638\" srcset=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-184.png 421w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-184-238x300.png 238w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-184-10x12.png 10w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-184-19x24.png 19w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-184-29x36.png 29w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-184-38x48.png 38w\" sizes=\"auto, (max-width: 421px) 100vw, 421px\" \/><\/a><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-185.png\"><img loading=\"lazy\" decoding=\"async\" width=\"421\" height=\"531\" data-id=\"11639\" src=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-185.png\" alt=\"\" class=\"wp-image-11639\" srcset=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-185.png 421w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-185-238x300.png 238w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-185-10x12.png 10w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-185-19x24.png 19w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-185-29x36.png 29w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-185-38x48.png 38w\" sizes=\"auto, (max-width: 421px) 100vw, 421px\" \/><\/a><\/figure>\n<figcaption class=\"blocks-gallery-caption\">Microsoft OLE DB Provider for ODBC Drivers: Data Link Properties Window<\/figcaption><\/figure>\n\n\n\n<p>For reference, when \u201cMicrosoft OLE DB Provider for Oracle\u201d is selected as the provider, the \u201cConnection\u201d tab changes as follows.<\/p>\n\n\n\n<figure class=\"wp-block-gallery has-nested-images columns-default is-cropped wp-block-gallery-2 is-layout-flex wp-block-gallery-is-layout-flex\">\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-186.png\"><img loading=\"lazy\" decoding=\"async\" width=\"421\" height=\"531\" data-id=\"11640\" src=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-186.png\" alt=\"\" class=\"wp-image-11640\" srcset=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-186.png 421w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-186-238x300.png 238w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-186-10x12.png 10w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-186-19x24.png 19w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-186-29x36.png 29w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-186-38x48.png 38w\" sizes=\"auto, (max-width: 421px) 100vw, 421px\" \/><\/a><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-187.png\"><img loading=\"lazy\" decoding=\"async\" width=\"421\" height=\"531\" data-id=\"11641\" src=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-187.png\" alt=\"\" class=\"wp-image-11641\" srcset=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-187.png 421w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-187-238x300.png 238w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-187-10x12.png 10w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-187-19x24.png 19w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-187-29x36.png 29w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-187-38x48.png 38w\" sizes=\"auto, (max-width: 421px) 100vw, 421px\" \/><\/a><\/figure>\n<figcaption class=\"blocks-gallery-caption\"><span style=\"color: rgb(0, 0, 0); font-family: Bitter, IropkeBatang, &quot;Noto Sans DemiLight&quot;, AppleSDGothicNeo, &quot;Malgun Gothic&quot;, &quot;\ub9d1\uc740 \uace0\ub515&quot;, \ub3cb\uc6c0, dotum, sans-serif, Font_Awesome_5_Free; white-space: normal;\">Microsoft OLE DB Provider for Oracle: Data Link Properties Window<\/span><\/figcaption><\/figure>\n\n\n\n<ul class=\"wp-block-list\"><li>Line 16: If the return value of the PromptEdit function is True, that is, if ConnectionString (connection string) is created\/changed by clicking the \u201cOK\u201d button in the \u201cData Connection Properties\u201d window, the value is recorded in oCurrentRange.<\/li><\/ul>\n\n\n\n<p>Applying this method has the following advantages:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Reduced chance of error<ul><li>If you create a DB ConnectionString by typing, you may make a typo or specify a provider that is not installed and cause an error.<\/li><li>DB ConnectionString can be created without error by applying this method.<\/li><\/ul><\/li><li>Improved reusability<ul><li>If DB ConnectionString is managed as a string in the source code (hard coded), the source code must be changed every time you want to run the function in a different environment. In this way, only users who know how to edit a certain source code can reuse it.<\/li><li>If DB ConnectionString is visible in the UI (Excel sheet, etc.) rather than the source code, and the function is provided so that it can be edited, anyone can reuse it, improving reusability.<\/li><\/ul><\/li><li>Convenience improvements (no need to create ODBC DSN)<ul><li>When using ODBC, pre-set connection information and its name (DSN, Data Source Name) are required.<\/li><li>Applying this method is convenient because there is no need to create an ODBC DSN.<\/li><\/ul><\/li><\/ul>\n\n\n\n<p>The downside is that if you include a password when creating the ConnectionString, the password can be exposed in the UI. You need to be careful if you do not want to disclose your password.<\/p>","protected":false},"excerpt":{"rendered":"<p>This article examines the implementation of functions for creating and editing ADO DB Connection Strings in Excel VBA. 1. ADO concept and necessity 1.1. Concept of ADO (ActiveX Data Objects) ADO is a library that can access various data stores and CRUD (Create, Read, Update, Delete) data. Microsoft docs...<\/p>","protected":false},"author":1,"featured_media":11634,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29],"tags":[],"class_list":["post-11633","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-vba-coding-pattern"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.3 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>VBA \ucf54\ub529 \ud328\ud134: ADO DB Connection String \uc0dd\uc131 - \uc0dd\uc0b0\uc131 Skill<\/title>\n<meta name=\"description\" content=\"\uc774\ubc88 \uae00\uc740 \uc5d1\uc140 VBA\uc5d0\uc11c ADO DB Connection String \uc0dd\uc131 \uacfc \ud3b8\uc9d1\uc5d0 \ub300\ud55c \uae30\ub2a5 \uad6c\ud604\uc744 \uc0b4\ud3b4\ubcf8\ub2e4. ADO \uac1c\ub150, \ud544\uc694\uc131, \uad6c\ud604\uc2dc \ud544\uc694\ud55c \ub77c\uc774\ube0c\ub7ec\ub9ac \ucc38\uc870 \ucd94\uac00, \uae30\ub2a5\uc744 \uad6c\ud604\ud55c \uc18c\uc2a4\ucf54\ub4dc\ub97c \ud655\uc778\ud560 \uc218 \uc788\ub2e4.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/prodskill.com\/en\/excel-vba-coding-pattern-ado-db-connection-string\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"VBA \ucf54\ub529 \ud328\ud134: ADO DB Connection String \uc0dd\uc131 - \uc0dd\uc0b0\uc131 Skill\" \/>\n<meta property=\"og:description\" content=\"\uc774\ubc88 \uae00\uc740 \uc5d1\uc140 VBA\uc5d0\uc11c ADO DB Connection String \uc0dd\uc131 \uacfc \ud3b8\uc9d1\uc5d0 \ub300\ud55c \uae30\ub2a5 \uad6c\ud604\uc744 \uc0b4\ud3b4\ubcf8\ub2e4. ADO \uac1c\ub150, \ud544\uc694\uc131, \uad6c\ud604\uc2dc \ud544\uc694\ud55c \ub77c\uc774\ube0c\ub7ec\ub9ac \ucc38\uc870 \ucd94\uac00, \uae30\ub2a5\uc744 \uad6c\ud604\ud55c \uc18c\uc2a4\ucf54\ub4dc\ub97c \ud655\uc778\ud560 \uc218 \uc788\ub2e4.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/prodskill.com\/en\/excel-vba-coding-pattern-ado-db-connection-string\/\" \/>\n<meta property=\"og:site_name\" content=\"\uc0dd\uc0b0\uc131 Skill\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-26T09:12:18+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-10-04T14:10:08+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-180.png\" \/>\n\t<meta property=\"og:image:width\" content=\"977\" \/>\n\t<meta property=\"og:image:height\" content=\"548\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Zerom\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Zerom\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/prodskill.com\\\/excel-vba-coding-pattern-ado-db-connection-string\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/excel-vba-coding-pattern-ado-db-connection-string\\\/\"},\"author\":{\"name\":\"Zerom\",\"@id\":\"https:\\\/\\\/prodskill.com\\\/ko\\\/#\\\/schema\\\/person\\\/bbad0870c78008c82edbe0960fe768bd\"},\"headline\":\"VBA \ucf54\ub529 \ud328\ud134: ADO DB Connection String \uc0dd\uc131\",\"datePublished\":\"2022-09-26T09:12:18+00:00\",\"dateModified\":\"2022-10-04T14:10:08+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/excel-vba-coding-pattern-ado-db-connection-string\\\/\"},\"wordCount\":315,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/ko\\\/#\\\/schema\\\/person\\\/bbad0870c78008c82edbe0960fe768bd\"},\"image\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/excel-vba-coding-pattern-ado-db-connection-string\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/prodskill.com\\\/wp-content\\\/uploads\\\/2022\\\/09\\\/image-180.png\",\"articleSection\":[\"\uc5d1\uc140 VBA \ucf54\ub529\ud328\ud134\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/prodskill.com\\\/excel-vba-coding-pattern-ado-db-connection-string\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/prodskill.com\\\/excel-vba-coding-pattern-ado-db-connection-string\\\/\",\"url\":\"https:\\\/\\\/prodskill.com\\\/excel-vba-coding-pattern-ado-db-connection-string\\\/\",\"name\":\"VBA \ucf54\ub529 \ud328\ud134: ADO DB Connection String \uc0dd\uc131 - \uc0dd\uc0b0\uc131 Skill\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/ko\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/excel-vba-coding-pattern-ado-db-connection-string\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/excel-vba-coding-pattern-ado-db-connection-string\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/prodskill.com\\\/wp-content\\\/uploads\\\/2022\\\/09\\\/image-180.png\",\"datePublished\":\"2022-09-26T09:12:18+00:00\",\"dateModified\":\"2022-10-04T14:10:08+00:00\",\"description\":\"\uc774\ubc88 \uae00\uc740 \uc5d1\uc140 VBA\uc5d0\uc11c ADO DB Connection String \uc0dd\uc131 \uacfc \ud3b8\uc9d1\uc5d0 \ub300\ud55c \uae30\ub2a5 \uad6c\ud604\uc744 \uc0b4\ud3b4\ubcf8\ub2e4. ADO \uac1c\ub150, \ud544\uc694\uc131, \uad6c\ud604\uc2dc \ud544\uc694\ud55c \ub77c\uc774\ube0c\ub7ec\ub9ac \ucc38\uc870 \ucd94\uac00, \uae30\ub2a5\uc744 \uad6c\ud604\ud55c \uc18c\uc2a4\ucf54\ub4dc\ub97c \ud655\uc778\ud560 \uc218 \uc788\ub2e4.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/excel-vba-coding-pattern-ado-db-connection-string\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/prodskill.com\\\/excel-vba-coding-pattern-ado-db-connection-string\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/prodskill.com\\\/excel-vba-coding-pattern-ado-db-connection-string\\\/#primaryimage\",\"url\":\"https:\\\/\\\/prodskill.com\\\/wp-content\\\/uploads\\\/2022\\\/09\\\/image-180.png\",\"contentUrl\":\"https:\\\/\\\/prodskill.com\\\/wp-content\\\/uploads\\\/2022\\\/09\\\/image-180.png\",\"width\":977,\"height\":548},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/prodskill.com\\\/excel-vba-coding-pattern-ado-db-connection-string\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/prodskill.com\\\/ko\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"VBA \ucf54\ub529 \ud328\ud134: ADO DB Connection String \uc0dd\uc131\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/prodskill.com\\\/ko\\\/#website\",\"url\":\"https:\\\/\\\/prodskill.com\\\/ko\\\/\",\"name\":\"\uc0dd\uc0b0\uc131 Skill\",\"description\":\"Meta Thinking, Meta Working\",\"publisher\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/ko\\\/#\\\/schema\\\/person\\\/bbad0870c78008c82edbe0960fe768bd\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/prodskill.com\\\/ko\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/prodskill.com\\\/ko\\\/#\\\/schema\\\/person\\\/bbad0870c78008c82edbe0960fe768bd\",\"name\":\"Zerom\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/prodskill.com\\\/wp-content\\\/uploads\\\/2022\\\/09\\\/productivity_clockgear.png\",\"url\":\"https:\\\/\\\/prodskill.com\\\/wp-content\\\/uploads\\\/2022\\\/09\\\/productivity_clockgear.png\",\"contentUrl\":\"https:\\\/\\\/prodskill.com\\\/wp-content\\\/uploads\\\/2022\\\/09\\\/productivity_clockgear.png\",\"width\":512,\"height\":512,\"caption\":\"Zerom\"},\"logo\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/wp-content\\\/uploads\\\/2022\\\/09\\\/productivity_clockgear.png\"},\"url\":\"https:\\\/\\\/prodskill.com\\\/en\\\/author\\\/proda\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"VBA Coding Pattern: Creating an ADO DB Connection String - Productivity Skill","description":"This article explores the implementation of ADO DB Connection String creation and editing in Excel VBA. You&#039;ll learn about ADO concepts, its necessity, the library references required for implementation, and the source code for implementing the functionality.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/prodskill.com\/en\/excel-vba-coding-pattern-ado-db-connection-string\/","og_locale":"en_US","og_type":"article","og_title":"VBA \ucf54\ub529 \ud328\ud134: ADO DB Connection String \uc0dd\uc131 - \uc0dd\uc0b0\uc131 Skill","og_description":"\uc774\ubc88 \uae00\uc740 \uc5d1\uc140 VBA\uc5d0\uc11c ADO DB Connection String \uc0dd\uc131 \uacfc \ud3b8\uc9d1\uc5d0 \ub300\ud55c \uae30\ub2a5 \uad6c\ud604\uc744 \uc0b4\ud3b4\ubcf8\ub2e4. ADO \uac1c\ub150, \ud544\uc694\uc131, \uad6c\ud604\uc2dc \ud544\uc694\ud55c \ub77c\uc774\ube0c\ub7ec\ub9ac \ucc38\uc870 \ucd94\uac00, \uae30\ub2a5\uc744 \uad6c\ud604\ud55c \uc18c\uc2a4\ucf54\ub4dc\ub97c \ud655\uc778\ud560 \uc218 \uc788\ub2e4.","og_url":"https:\/\/prodskill.com\/en\/excel-vba-coding-pattern-ado-db-connection-string\/","og_site_name":"\uc0dd\uc0b0\uc131 Skill","article_published_time":"2022-09-26T09:12:18+00:00","article_modified_time":"2022-10-04T14:10:08+00:00","og_image":[{"width":977,"height":548,"url":"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-180.png","type":"image\/png"}],"author":"Zerom","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Zerom","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/prodskill.com\/excel-vba-coding-pattern-ado-db-connection-string\/#article","isPartOf":{"@id":"https:\/\/prodskill.com\/excel-vba-coding-pattern-ado-db-connection-string\/"},"author":{"name":"Zerom","@id":"https:\/\/prodskill.com\/ko\/#\/schema\/person\/bbad0870c78008c82edbe0960fe768bd"},"headline":"VBA \ucf54\ub529 \ud328\ud134: ADO DB Connection String \uc0dd\uc131","datePublished":"2022-09-26T09:12:18+00:00","dateModified":"2022-10-04T14:10:08+00:00","mainEntityOfPage":{"@id":"https:\/\/prodskill.com\/excel-vba-coding-pattern-ado-db-connection-string\/"},"wordCount":315,"commentCount":0,"publisher":{"@id":"https:\/\/prodskill.com\/ko\/#\/schema\/person\/bbad0870c78008c82edbe0960fe768bd"},"image":{"@id":"https:\/\/prodskill.com\/excel-vba-coding-pattern-ado-db-connection-string\/#primaryimage"},"thumbnailUrl":"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-180.png","articleSection":["\uc5d1\uc140 VBA \ucf54\ub529\ud328\ud134"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/prodskill.com\/excel-vba-coding-pattern-ado-db-connection-string\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/prodskill.com\/excel-vba-coding-pattern-ado-db-connection-string\/","url":"https:\/\/prodskill.com\/excel-vba-coding-pattern-ado-db-connection-string\/","name":"VBA Coding Pattern: Creating an ADO DB Connection String - Productivity Skill","isPartOf":{"@id":"https:\/\/prodskill.com\/ko\/#website"},"primaryImageOfPage":{"@id":"https:\/\/prodskill.com\/excel-vba-coding-pattern-ado-db-connection-string\/#primaryimage"},"image":{"@id":"https:\/\/prodskill.com\/excel-vba-coding-pattern-ado-db-connection-string\/#primaryimage"},"thumbnailUrl":"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-180.png","datePublished":"2022-09-26T09:12:18+00:00","dateModified":"2022-10-04T14:10:08+00:00","description":"This article explores the implementation of ADO DB Connection String creation and editing in Excel VBA. You&#039;ll learn about ADO concepts, its necessity, the library references required for implementation, and the source code for implementing the functionality.","breadcrumb":{"@id":"https:\/\/prodskill.com\/excel-vba-coding-pattern-ado-db-connection-string\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/prodskill.com\/excel-vba-coding-pattern-ado-db-connection-string\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/prodskill.com\/excel-vba-coding-pattern-ado-db-connection-string\/#primaryimage","url":"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-180.png","contentUrl":"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/image-180.png","width":977,"height":548},{"@type":"BreadcrumbList","@id":"https:\/\/prodskill.com\/excel-vba-coding-pattern-ado-db-connection-string\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/prodskill.com\/ko\/"},{"@type":"ListItem","position":2,"name":"VBA \ucf54\ub529 \ud328\ud134: ADO DB Connection String \uc0dd\uc131"}]},{"@type":"WebSite","@id":"https:\/\/prodskill.com\/ko\/#website","url":"https:\/\/prodskill.com\/ko\/","name":"Productivity Skills","description":"Meta Thinking, Meta Working","publisher":{"@id":"https:\/\/prodskill.com\/ko\/#\/schema\/person\/bbad0870c78008c82edbe0960fe768bd"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/prodskill.com\/ko\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/prodskill.com\/ko\/#\/schema\/person\/bbad0870c78008c82edbe0960fe768bd","name":"Zerom","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/productivity_clockgear.png","url":"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/productivity_clockgear.png","contentUrl":"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/productivity_clockgear.png","width":512,"height":512,"caption":"Zerom"},"logo":{"@id":"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/productivity_clockgear.png"},"url":"https:\/\/prodskill.com\/en\/author\/proda\/"}]}},"_links":{"self":[{"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/posts\/11633","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/comments?post=11633"}],"version-history":[{"count":0,"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/posts\/11633\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/media\/11634"}],"wp:attachment":[{"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/media?parent=11633"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/categories?post=11633"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/tags?post=11633"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}