论坛广场 › 源码分享 › VB连接各种数据库方法

阿杰 发表于 2012-3-11 10:17:19

VB连接各种数据库方法

Option Explicit
Public mCnnDB As New ADODB.Connection

'设置ODBC
Public Const ODBC_ADD_DSN = 1 ' Add data source
Public Const ODBC_CONFIG_DSN = 2 ' Configure (edit) data source
Public Const ODBC_REMOVE_DSN = 3 ' Remove data source
Public Const vbAPINull As Long = 0& ' NULL Pointer
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

Public Sub CreateAccess(ByVal AccessDBName As String)
    '创建Microsoft Access数据库
   Dim cat As New ADOX.Catalog
    cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AccessDBName
End Sub

Public Sub CompactAccess2(ByVal OldDBName As String, ByVal NewDBName As String, ByVal UserPSW As String)
    '需要用户名称和密码压缩修复Microsoft Access数据库
   Dim myJRO As JRO.JetEngine
    Set myJRO = New JRO.JetEngine
    If Trim(OldDBName) = "" Then
      MsgBox "源数据库名称非法", vbCritical
      Exit Sub
    End If
    If Trim(NewDBName) = "" Then
      MsgBox "目标数据库名称非法", vbCritical
      Exit Sub
    End If
    myJRO.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0" & "data source='" & OldDBName & "'", "Provider=Microsoft.Jet.OLEDB.4.0" & "data source='" & NewDBName & "'" & "pwd=" & UserPSW
End Sub


Public Sub ConnectAccess(ByVal DBName As String, ByVal UserID As String, ByVal UserPSW As String)
    '链接Microsoft Access数据库
   'DBName指Microsoft Access数据库名称
   Dim strDB As String
    With mCnnDB
      .CursorLocation = adUseClient
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .ConnectionString = "userid='" & UserID & "';" & "password='" & UserPSW & "';" & "datasource='" & DBName & "'"
      .Open DBName, "admin"
    End With
End Sub

Public Sub ConnectODBC1(ByVal DSName As String)
    '不需要用户和密码链接ODBC数据库
   'DSName指ODBC数据库名称
   With mCnnDB
      .Provider = "MSDASQL"
      .ConnectionString = "data source'" & DSName & "'"
      .Open
    End With
End Sub

Public Sub ConnectODBC2(ByVal DSName As String, ByVal UserID As String, ByVal UserPSW As String)
    '需要用户和密码链接ODBC数据库
   'DSName指ODBC数据库名称
   With mCnnDB
      .Provider = "MSDASQL"
      .ConnectionString = "data source'" & DSName & "'User ID='" & UserID & "';" & "Password='" & UserPSW & "'"
      .Open
    End With
End Sub

Public Sub ConnectSQLServer1(ByVal ServerName As String, ByVal DBName As String)
    '不需要用户和密码链接Microsoft SQL Server数据库
   'ServerName服务器名称，DBName数据库名称
   With mCnnDB
      .ConnectionString = "Uid=;pwd=;driver={SQL SERVER};" & "Server=" & ServerName & ";DataBase=" & DBName
      .Open
    End With
End Sub

Public Sub ConnectSQLServer2(ByVal ServerName As String, ByVal DBName As String, ByVal UserID As String, ByVal UserPSW As String)
    '需要用户和密码链接Microsoft SQL Server数据库
   'ServerName服务器名称，DBName数据库名称
   With mCnnDB
      .ConnectionString = "Uid='" & UserID & "';pwd='" & UserPSW & " ';driver={SQL SERVER};" & "Server=" & ServerName & ";DataBase=" & DBName
      .Open
    End With
End Sub

Public Sub ConnectOracle(ByVal ServerName As String, ByVal UserID As String, ByVal UserPSW As String)
    '链接Oracle数据库
   With mCnnDB
      .Provider = "MSOAORA"
      .ConnectionString = "User ID='" & UserID & "';Password='" & UserPSW & "';DataSource='" & ServerName & "'"
      .Open
    End With
End Sub

luorl 发表于 2012-8-13 21:04:38

谢谢分享，非常不错！

查看完整版本: VB连接各种数据库方法