VB连接各种数据库方法

阿杰

1. Option Explicit
   
2. Public mCnnDB As New ADODB.Connection
   
3. 
   
4. '设置ODBC
   
5. Public Const ODBC_ADD_DSN = 1 ' Add data source
   
6. Public Const ODBC_CONFIG_DSN = 2 ' Configure (edit) data source
   
7. Public Const ODBC_REMOVE_DSN = 3 ' Remove data source
   
8. Public Const vbAPINull As Long = 0& ' NULL Pointer
   
9. Public Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal hwndParent As Long, ByVal fRequest As Long, ByVal lpszDriver As String, ByVal lpszAttributes As String) As Long
   
10. 
    
11. Public Sub CreateAccess(ByVal AccessDBName As String)
    
12.     '创建Microsoft Access数据库
    
13.    Dim cat As New ADOX.Catalog
    
14.     cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AccessDBName
    
15. End Sub
    
16. 
    
17. Public Sub CompactAccess2(ByVal OldDBName As String, ByVal NewDBName As String, ByVal UserPSW As String)
    
18.     '需要用户名称和密码压缩修复Microsoft Access数据库
    
19.    Dim myJRO As JRO.JetEngine
    
20.     Set myJRO = New JRO.JetEngine
    
21.     If Trim(OldDBName) = "" Then
    
22.         MsgBox "源数据库名称非法", vbCritical
    
23.         Exit Sub
    
24.     End If
    
25.     If Trim(NewDBName) = "" Then
    
26.         MsgBox "目标数据库名称非法", vbCritical
    
27.         Exit Sub
    
28.     End If
    
29.     myJRO.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0" & "data source='" & OldDBName & "'", "Provider=Microsoft.Jet.OLEDB.4.0" & "data source='" & NewDBName & "'" & "pwd=" & UserPSW
    
30. End Sub
    
31. 
    
32. 
    
33. Public Sub ConnectAccess(ByVal DBName As String, ByVal UserID As String, ByVal UserPSW As String)
    
34.     '链接Microsoft Access数据库
    
35.    'DBName指Microsoft Access数据库名称
    
36.    Dim strDB As String
    
37.     With mCnnDB
    
38.         .CursorLocation = adUseClient
    
39.         .Provider = "Microsoft.Jet.OLEDB.4.0"
    
40.         .ConnectionString = "userid='" & UserID & "';" & "password='" & UserPSW & "';" & "datasource='" & DBName & "'"
    
41.         .Open DBName, "admin"
    
42.     End With
    
43. End Sub
    
44. 
    
45. Public Sub ConnectODBC1(ByVal DSName As String)
    
46.     '不需要用户和密码链接ODBC数据库
    
47.    'DSName指ODBC数据库名称
    
48.    With mCnnDB
    
49.         .Provider = "MSDASQL"
    
50.         .ConnectionString = "data source'" & DSName & "'"
    
51.         .Open
    
52.     End With
    
53. End Sub
    
54. 
    
55. Public Sub ConnectODBC2(ByVal DSName As String, ByVal UserID As String, ByVal UserPSW As String)
    
56.     '需要用户和密码链接ODBC数据库
    
57.    'DSName指ODBC数据库名称
    
58.    With mCnnDB
    
59.         .Provider = "MSDASQL"
    
60.         .ConnectionString = "data source'" & DSName & "'User ID='" & UserID & "';" & "Password='" & UserPSW & "'"
    
61.         .Open
    
62.     End With
    
63. End Sub
    
64. 
    
65. Public Sub ConnectSQLServer1(ByVal ServerName As String, ByVal DBName As String)
    
66.     '不需要用户和密码链接Microsoft SQL Server数据库
    
67.    'ServerName服务器名称，DBName数据库名称
    
68.    With mCnnDB
    
69.         .ConnectionString = "Uid=;pwd=;driver={SQL SERVER};" & "Server=" & ServerName & ";DataBase=" & DBName
    
70.         .Open
    
71.     End With
    
72. End Sub
    
73. 
    
74. Public Sub ConnectSQLServer2(ByVal ServerName As String, ByVal DBName As String, ByVal UserID As String, ByVal UserPSW As String)
    
75.     '需要用户和密码链接Microsoft SQL Server数据库
    
76.    'ServerName服务器名称，DBName数据库名称
    
77.    With mCnnDB
    
78.         .ConnectionString = "Uid='" & UserID & "';pwd='" & UserPSW & " ';driver={SQL SERVER};" & "Server=" & ServerName & ";DataBase=" & DBName
    
79.         .Open
    
80.     End With
    
81. End Sub
    
82. 
    
83. Public Sub ConnectOracle(ByVal ServerName As String, ByVal UserID As String, ByVal UserPSW As String)
    
84.     '链接Oracle数据库
    
85.    With mCnnDB
    
86.         .Provider = "MSOAORA"
    
87.         .ConnectionString = "User ID='" & UserID & "';Password='" & UserPSW & "';DataSource='" & ServerName & "'"
    
88.         .Open
    
89.     End With
    
90. End Sub

复制代码

luorl

谢谢分享，非常不错！