VB连接各种数据库方法

C。 · 发表于 2012-4-15 19:45:34

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