引用 Microsoft OLE DB Service Component 1.0 TYPE library 和ADO然后添加如下代码Option Explicit Dim adoConn As ADODB.Connection Private Function BuildAdoConnection(ByVal ConnectionString As String) As String ' display the ADO Connection Window (ADO DB Designer) Dim dlViewConnection As MSDASC.DataLinks On Error GoTo Err_BuildAdoConnection
If Not (Trim$(ConnectionString) = "") Then Set adoConn = New ADODB.Connection adoConn.ConnectionString = ConnectionString Set dlViewConnection = New MSDASC.DataLinks dlViewConnection.hWnd = Me.hWnd If dlViewConnection.PromptEdit(adoConn) Then BuildAdoConnection = adoConn.ConnectionString Else BuildAdoConnection = ConnectionString End If Set dlViewConnection = Nothing Set adoConn = Nothing Else Set dlViewConnection = New MSDASC.DataLinks dlViewConnection.hWnd = Me.hWnd Set adoConn = dlViewConnection.PromptNew BuildAdoConnection = adoConn.ConnectionString Set dlViewConnection = Nothing Set adoConn = Nothing End IfExit_BuildAdoConnection: On Error Resume Next If Not (adoConn Is Nothing) Then Set adoConn = Nothing End If If Not (dlViewConnection Is Nothing) Then Set dlViewConnection = Nothing End If On Error GoTo 0 Exit FunctionErr_BuildAdoConnection: Select Case Err Case 0 Resume Next Case -2147217805 adoConn.ConnectionString = "" Resume Case 91 Resume Exit_BuildAdoConnection Case Else MsgBox "Error: " & Err.Number & vbCrLf & vbCrLf & Err.Description, vbInformation, App.Title & " - Advisory" Resume Exit_BuildAdoConnection End Select
End Function Private Sub Command1_Click() Set adoConn = New ADODB.Connection Dim strAdoConn As String strAdoConn = BuildAdoConnection("") 'strAdoConn 为连接字符串 MsgBox strAdoConn End Sub
Sub RegisterDataSource()
Dim strAttribs As String
’建造关键字字符串。
’C:\myfile\myexample.l.mdb数据库文件名(包含路径)
strAttribs =“DBQ=” _
& “C:\myfile\myexample.mdb” _
& Chr$(13) & “OemToAnsi=No” _
& Chr$(13) & “SERVER=SEQUEL” _
& Chr$(13) & “Network=DBNMPNTW” _
& Chr$(13) & “Database=WorkDB”_
& Chr$(13) &“Address=\\SEQUEL\PIPE\SQL\QUERY”
’建立新的注册的 DSN。
rdoEngine.rdoRegisterDataSource “mydatasource”,
“Microsoft Access Driver (*.mdb)”, True,
strAttribs
End Sub
Private Sub Form_Load()
Call rdoRegisterDataSource
End Sub
使用rdo对象的rdoRegisterDataSource方法在程序内动态注册,这样就不存在“odbc打包”的问题了。具体方法如下:
rdoRegisterDataSource 方法示例
下面示例说明使用 rdoRegisterDataSource 方法建立新的 ODBC 数据源项。
Private Sub RegisterDataSource()
Dim en As rdoEnvironment
Dim cnTest As rdoConnection
Dim strAttribs As String
'建造关键字字符串。
strAttribs = "Description=" _
& "SQL Server on server SEQUEL" _
& Chr$(13) & "OemToAnsi=No" _
& Chr$(13) & "SERVER=SEQUEL" _
& Chr$(13) & "Network=DBNMPNTW" _
& Chr$(13) & "Database=WorkDB" _
& Chr$(13) & "Address=\\SEQUEL\PIPE\SQL\QUERY"
'建立新的注册的 DSN。
rdoEngine.rdoRegisterDataSource "示例", _
"SQL Server", True, strAttribs
'打开数据库。
Set en = rdoEngine.rdoEnvironments(0)
Set cnTest = en.OpenConnection( _
dsname:="示例", _
Prompt:=rdDriverNoPrompt, _
Connect:="UID=;PWD=;")
End Sub
难道只能这样做,请继续关注.
谢谢!!
Microsoft OLE DB Service Component 1.0 TYPE library
和ADO然后添加如下代码Option Explicit
Dim adoConn As ADODB.Connection
Private Function BuildAdoConnection(ByVal ConnectionString As String) As String ' display the ADO Connection Window (ADO DB Designer) Dim dlViewConnection As MSDASC.DataLinks On Error GoTo Err_BuildAdoConnection
If Not (Trim$(ConnectionString) = "") Then
Set adoConn = New ADODB.Connection
adoConn.ConnectionString = ConnectionString
Set dlViewConnection = New MSDASC.DataLinks
dlViewConnection.hWnd = Me.hWnd
If dlViewConnection.PromptEdit(adoConn) Then
BuildAdoConnection = adoConn.ConnectionString
Else
BuildAdoConnection = ConnectionString
End If
Set dlViewConnection = Nothing
Set adoConn = Nothing
Else
Set dlViewConnection = New MSDASC.DataLinks
dlViewConnection.hWnd = Me.hWnd
Set adoConn = dlViewConnection.PromptNew
BuildAdoConnection = adoConn.ConnectionString
Set dlViewConnection = Nothing
Set adoConn = Nothing
End IfExit_BuildAdoConnection: On Error Resume Next
If Not (adoConn Is Nothing) Then
Set adoConn = Nothing
End If
If Not (dlViewConnection Is Nothing) Then
Set dlViewConnection = Nothing
End If
On Error GoTo 0
Exit FunctionErr_BuildAdoConnection: Select Case Err
Case 0
Resume Next
Case -2147217805
adoConn.ConnectionString = ""
Resume
Case 91
Resume Exit_BuildAdoConnection
Case Else
MsgBox "Error: " & Err.Number & vbCrLf & vbCrLf & Err.Description, vbInformation, App.Title & " - Advisory"
Resume Exit_BuildAdoConnection
End Select
End Function
Private Sub Command1_Click()
Set adoConn = New ADODB.Connection
Dim strAdoConn As String
strAdoConn = BuildAdoConnection("") 'strAdoConn 为连接字符串
MsgBox strAdoConn
End Sub