title: Regex VBA in Access - finding text between two strings
body: <p>I am having a heck of a time with a RegEx question in Access VBA.</p>
  
  <p>My goal is to extract the server from a linked database connection string. Basically, the connection string looks like</p>
  
  <blockquote>
    <p>ODBC;DRIVER=SQL Server;SERVER=compName\sqlexpress;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=databaseName</p>
  </blockquote>
  
  <p>I am able to get the first regex to work, but it is returning</p>
  
  <blockquote>
    <p>SERVER=compName\sqlexpress</p>
  </blockquote>
  
  <p>I would like this to only return </p>
  
  <blockquote>
    <p>compName\sqlexpress</p>
  </blockquote>
  
  <p>My understanding is the <code>?&lt;=</code> operator should allow the RegEx to work correctly, but I get the following error "Method 'Execute' of object 'IRegExp2' failed."</p>
  
  <p>The only documentation I can find for any Microsoft RegEx syntax is <a href="http://msdn.microsoft.com/en-us/library/az24scfc%28v=vs.110%29.aspx" rel="nofollow">here</a> which is not the runtime 5.5 VBScript library, but I'm not sure where else to get supported syntax.</p>
  
  <p>Here is the code I'm using to test this. My database has a lot of linked tables.</p>
  
  <pre><code>Sub printServerStringInformation()
      Dim rxPattern As String
  
      rxPattern = "(?=SERVER)(.*)(?=;Trusted)"
      Debug.Print RxMatch(CurrentDb.tableDefs(1).Connect, rxPattern, False)
  
      rxPattern = "(?&lt;=SERVER)(.*)(?=;Trusted)"
      Debug.Print RxMatch(CurrentDb.tableDefs(1).Connect, rxPattern, False)
  
  End Sub
  </code></pre>
  
  <p>Here is the function I am using:</p>
  
  <pre><code>Public Function RxMatch( _
      ByVal SourceString As String, _
      ByVal Pattern As String, _
      Optional ByVal IgnoreCase As Boolean = True, _
      Optional ByVal MultiLine As Boolean = True) As Variant
   'Microsoft VBScript Regular Expressions 5.5
  
      'http://www.zytrax.com/tech/web/regex.htm#more
      'http://bytecomb.com/regular-expressions-in-vba/
  
      'http://xkcd.com/1171/
      On Error GoTo errHandler
  
      Dim oMatches As MatchCollection
      With New RegExp
          .MultiLine = MultiLine
          .IgnoreCase = IgnoreCase
          .Global = False
          .Pattern = Pattern
          Set oMatches = .Execute(SourceString)
          If oMatches.Count &gt; 0 Then
              RxMatch = oMatches(0).value
          Else
              RxMatch = ""
          End If
      End With
  
  errHandler:
      Debug.Print Err.Description
  
  End Function
  </code></pre>