Friday, November 5, 2004

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: , ,

1 comment:

  1. Restored comments

    Anonymous said...

    very clever!!! :)
    April 04, 2006
    Anonymous said...

    I've been searching for days for code like this. All I've been able to find is how to create a UDL file by renaming New Text Document.txt Not what I wanted!! I had already tried FileOpen, but I had not opened the file as Binary. Thanks!!!!
    April 07, 2006
    Adam said...

    I found writing it as text did not work. Your solution using binary worked perfectly. Thanks!!!
    December 29, 2006
    Max said...

    Very clever solution
    Thank you very much
    November 04, 2007

    ReplyDelete

Some of the latest blog posts

Subscribe to RSS headline updates from:
Powered by FeedBurner