通用数据链接文件 (*.UDL) 的创建

关注

原创最新推荐文章于 2026-02-26 19:13:07 发布 1.4k 阅读 AI 写同款· GEO 优化 ›

使用UDL创建数据库连接一、创建UDL文件 1、udl是通用数据连接文件. 　使用 UDL 文件存儲数据库连接的方式和使用“ODBC 数据源名称 (DSN)”非常相似。UDL 文件存儲 OLE DB 連接信息，例如提供程序、用戶名、密碼和其他選項。用存儲在該 UDL 文件中的信息可以打開 ADO 連接，从而允许管理員在需要的情況下更改它，並且避免了打開注冊表或者使用 ODBC。 2、先创建TXT 文件，后阅读详情

'引用 Microsoft OLE DB Service Component 1.0 Type Library
Option Explicit
Private Sub Command1_Click()
Dim x As New MSDASC.DataLinks
x.hWnd = Me.hWnd
Dim s As String
On Error GoTo ErrorHandler
s = x.PromptNew
On Error GoTo 0
If VBA.Len(VBA.Trim(s & "")) > 0 Then
  Dim CommonDialog1 As New MSComDlg.CommonDialog
  CommonDialog1.DefaultExt = ".udl"
  CommonDialog1.Filter = "通用数据链接文件 (*.UDL)|*.udl"
  CommonDialog1.DialogTitle = "保存为通用数据链接文件"
  CommonDialog1.Flags = cdlOFNOverwritePrompt
  CommonDialog1.CancelError = True
  On Error GoTo ErrorHandler
  CommonDialog1.ShowSave
  On Error GoTo 0
  s = "[oledb]" & vbCrLf _
   & "; Everything after this line is an OLE DB initstring" & vbCrLf _
   & s & vbCrLf
  Dim BytesBuffer() As Byte
  BytesBuffer = VBA.StrConv(VBA.StrConv(s, vbUnicode), vbFromUnicode)
  Dim i As Long
  ReDim BytesBuffer0(1) As Byte
  BytesBuffer0(0) = 255 '&HFF
  BytesBuffer0(1) = 254 '&HFE
  If VBA.Len(VBA.Trim(VBA.Dir(CommonDialog1.FileName))) > 0 Then
   VBA.Kill CommonDialog1.FileName
  End If
  On Error GoTo ErrorHandler
  i = VBA.FreeFile
  Open CommonDialog1.FileName For Binary Access Write As #i
  Put #i, , BytesBuffer0
  Put #i, , BytesBuffer
  Close #i
  On Error GoTo 0
  If VBA.MsgBox("Test?", vbYesNo) = vbYes Then
   Dim adoConnection As New ADODB.Connection
   adoConnection.Open "File Name=" & CommonDialog1.FileName
   VBA.MsgBox "OK!"
  End If
End If
Exit Sub
ErrorHandler:
If Err.Number <> 91 And Err.Number <> 32755 Then
  VBA.MsgBox Err.Number & ":" & vbCrLf & Err.Description
End If
End Sub

Private Sub Command2_Click()
Dim CommonDialog1 As New MSComDlg.CommonDialog
CommonDialog1.DefaultExt = ".udl"
CommonDialog1.Filter = "通用数据链接文件 (*.UDL)|*.udl"
CommonDialog1.DialogTitle = "打开通用数据链接文件"
'CommonDialog1.Flags = cdlOFNOverwritePrompt
CommonDialog1.CancelError = True
On Error GoTo ErrorHandler
CommonDialog1.ShowOpen
On Error GoTo 0
If VBA.Len(VBA.Trim(VBA.Dir(CommonDialog1.FileName))) > 0 Then
VBA.MsgBox GetConnectionStringFromUDL(CommonDialog1.FileName)
End If
Exit Sub
ErrorHandler:
If Err.Number <> 91 And Err.Number <> 32755 Then
VBA.MsgBox Err.Number & ":" & vbCrLf & Err.Description
End If
End Sub

Public Function GetConnectionStringFromUDL(UDLFileName As String) As String
If VBA.Len(VBA.Trim(VBA.Dir(UDLFileName & ""))) > 0 Then
  Dim BytesBuffer() As Byte
  ReDim BytesBuffer(VBA.FileLen(UDLFileName) - 133) As Byte
  Dim i As Long
  i = VBA.FreeFile
  Open UDLFileName For Binary Access Read As #i
  Get #i, 129, BytesBuffer
  Close #i
  GetConnectionStringFromUDL = VBA.Trim(VBA.StrConv(VBA.StrConv(BytesBuffer, vbFromUnicode), vbUnicode))
End If
End Function