Create a data link file (UDL) using code

Have you ever wondered how create a data link file (UDL) using code? Here is a sample using VB.NET:

Private Sub CreateUDL()
  Dim sStr As String
  Dim bStr() As Byte

  FileOpen(1, "c:\temp.udl", OpenMode.Binary, OpenAccess.Write)

  FilePut(1, CByte(&HFFS), 1)
  FilePut(1, CByte(&HFES), 2)

  sStr = "[oledb]" & vbCrLf
  bStr = System.Text.UnicodeEncoding.Unicode.GetBytes(sStr)
  FilePut(1, bStr)

  sStr = "; Everything after this line is an OLE DB initstring" & vbCrLf
  bStr = System.Text.UnicodeEncoding.Unicode.GetBytes(sStr)
  FilePut(1, bStr)

  ' Access Database
  ' sStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
  '        "C:\accessDB.mdb;Persist Security Info=False"

  ' SQL Server connection
  ' sStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
           "Persist Security Info=False;" & _
           "Initial Catalog=GB1114;Data Source=EMTNT15"

  ' ODBC data
  sStr = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=GB1114"
  bStr = System.Text.UnicodeEncoding.Unicode.GetBytes(sStr)
  FilePut(1, bStr)

  FileClose(1)
End Sub

Here is one using VB or VBA:

' Sample that shows how to create a data link file (UDL) using VB or VBA.
Sub Main()
    Dim bStr() As Byte

    Open "c:\temp.udl" For Binary Access Write As 1

    Put #1, 1, CByte(&HFF)
    Put #1, 2, CByte(&HFE)

    bStr = "[oledb]" & vbCrLf
    Put #1, , bStr

    bStr = "; Everything after this line is an OLE DB initstring" & vbCrLf
    Put #1, , bStr

    ' Access Database
    ' bStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\accessDB.mdb;" & _
             "Persist Security Info=False"

    ' SQL Server connection
    ' bStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
             "Persist Security Info=False;" & _
             "Initial Catalog=GB1114;Data Source=EMTNT15"

    ' ODBC data
    bStr = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=GB1114"
    Put #1, , bStr

    Close #1
End Sub

Technorati: VB.NET, VB, VBA