MS Outlook VB Programming

Hi im getting a incorrect syntax error when I run the following code in outlook 2010 VB Editor it appears to be happening where ive concatenated & myNamespace.CurrentUser & “

Following is the code:

Sub ConnectSqlServer()
  Dim myNamespace As Outlook.NameSpace
  Dim conn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim sConnString As String

  ' Create the connection string.
  sConnString = "Provider=SQLOLEDB;Data Source=WIN-NBST3PHVFV4ECLIPSE;" & _
                "Initial Catalog=OBlive;" & _
                "User ID=outlook;Password=password123;"

  ' Create the Connection and Recordset objects.
 Set conn = New ADODB.Connection
 Set rs = New ADODB.Recordset
Set myNamespace = Application.GetNamespace("MAPI")

 ' Open the connection and execute.
 conn.Open sConnString
 Set rs = conn.Execute("INSERT INTO dbo.Submissions (CV, Consultant, Timestamp) VALUES ('1', " & myNamespace.CurrentUser & ", CURRENT_TIMESTAMP )")
 ' Clean up
 If CBool(conn.State And adStateOpen) Then conn.Close
 Set conn = Nothing
 Set rs = Nothing
End Sub

Any Help would be very much appreciated

Answer

You need to quote the value:

...VALUES ('1', '" & myNamespace.CurrentUser & "', CURRENT_TIMESTAMP...

Advertisement

Answer