CODEWITHSUNDEEP
ms-accessvba
Coding Enthusiast
Coding Enthusiast

Reputation: 3933

How to escape a character in vba

How do I escape a character in vba? I am trying to escape a ; since I have a string that the compiler cuts at ;.
I tried the \ but it doesn't seem to work. Any other special escape in vba? 

Set mySqlCon = wrkODBC.OpenConnection("connection1", , , "DRIVER={MYSQL ODBC 5.1 DRIVER};" _
   & "SERVER=testserver.com;port=3306;" _
   & "DATABASE=test;" _
   & "USER={coding;Enthusiast};" _ 
   & "Password=pwd;")

Upvotes: 2

Views: 7224

Answers (2)

HansUp
HansUp

Reputation: 97101

Examine your string-building piece in the Immediate window:

? "DRIVER={MYSQL ODBC 5.1 DRIVER;}" _
   & "SERVER=testserver.com;port=3306;" _
   & "DATABASE=test;" _
   & "USER={coding;Enthusiast};" _
   & "Password=pwd;"
DRIVER={MYSQL ODBC 5.1 DRIVER;}SERVER=testserver.com;port=3306;DATABASE=test;USER={coding;Enthusiast};Password=pwd;

It produced a valid VBA string for me using Access 2010.

If the issue is that ODBC does not like that connection string, move the first semicolon outside the } bracket:

? "DRIVER={MYSQL ODBC 5.1 DRIVER};" _
   & "SERVER=testserver.com;port=3306;" _
   & "DATABASE=test;" _
   & "USER={coding;Enthusiast};" _
   & "Password=pwd;"
DRIVER={MYSQL ODBC 5.1 DRIVER};SERVER=testserver.com;port=3306;DATABASE=test;USER={coding;Enthusiast};Password=pwd;

If you're still having a problem with a semicolon inside your string, try adding it based on its ASCII value instead of as a literal ; character:

? "a" & Chr(59) & "b"
a;b

Upvotes: 1

David W
David W

Reputation: 10184

There's nothing special about a semicolon in VBA, and thus requires no special escaping or other treatment. The following snippet echoes your post and works just fine:

Sub foo()
  Dim a As String
  a = "foo" _
     & "; bar"

     MsgBox a

End Sub

Upvotes: 0

Related Questions