如何在visual studio的VB语言下连接access数据库

Question

要用visual studio的VB语言做一个ASP的网站   要把Name和ID的数据写入并保存在access数据库

Answer 1

Public Function FunConCnn(ByRef dmCnn As ADODB.Connection, _
                          Optional ByVal dmServer As String = "", _
                          Optional ByVal dmDataSource As String = "", _
                          Optional ByVal dmUser As String = "", _
                          Optional ByVal dmPWD As String = "", _
                          Optional ByVal dmDBType As DBType = 0, _
                          Optional ByVal dmCursorLocation As CursorLocationEnum = adUseClient, _
                          Optional ByVal dmMode As ConnectModeEnum = adModeUnknown, _
                          Optional ByVal dmDataShapePrivider As Long = 0) As Boolean
                          'Provider=MSDataShape.1显示层次结构记录集
  On Error GoTo Trip
  Dim strConnect As String
  If dmCnn.State = adStateOpen Then
    dmCnn.Close
  End If
  If dmDBType = DBType.Custom Or dmDBType = -1 Then
   dmServer = DB_Server
   dmDataSource = DB_DataSource
   dmUser = DB_User
   dmPWD = DB_Password
   dmDBType = DB_DBType
  End If
  Select Case dmDBType
    Case DBType.SQLServer '连接sql server 数据库, Sql Server OLEDB For ODBC
      If dmDataShapePrivider = 0 Then
        strConnect = "Provider=MSDASQL.1"
      ElseIf dmDataShapePrivider = 1 Then
        strConnect = "Provider=MSDataShape.1;Data Provider=MSDASQL.1"
      End If
      strConnect = strConnect & _
                   ";Driver={SQL Server}" & _
                   ";Server=" & dmServer & _
                   ";database=" & dmDataSource & _
                   ";uid=" & dmUser & _
                   ";pwd=" & dmPWD
    Case DBType.ORACLE '连接Oracle 数据库,Oracle OLEDB For ODBC
      If dmDataShapePrivider = 0 Then
        strConnect = "Provider=MSDASQL.1"
      ElseIf dmDataShapePrivider = 1 Then
        strConnect = "Provider=MSDataShape.1;Data Provider=MSDASQL.1"
      End If
      strConnect = strConnect & _
                   ";Driver={Oracle}" & _
                   ";Server=" & dmServer & _
                   ";database=" & dmDataSource & _
                   ";uid=" & dmUser & _
                   ";pwd=" & dmPWD
    Case DBType.ACCESS '
      If dmDataShapePrivider = 0 Then
        strConnect = "Provider=MSDASQL.1"
      ElseIf dmDataShapePrivider = 1 Then
        strConnect = "Provider=MSDataShape.1;Data Provider=MSDASQL.1"
      End If
      strConnect = strConnect & _
                   ";Driver={Microsoft Access Driver (*.mdb)}" & _
                   ";DBQ=" & dmDataSource & _
                   ";uid=" & dmUser & _
                   ";pwd=" & dmPWD
    Case DBType.ODBC 'odbc连接数据库
      If dmDataShapePrivider = 0 Then
        strConnect = "Provider=MSDASQL.1"
      ElseIf dmDataShapePrivider = 1 Then
        strConnect = "Provider=MSDataShape.1;Data Provider=MSDASQL.1"
      End If
      strConnect = strConnect & _
                   ";DSN=" & dmDataSource & _
                   ";UID=" & dmUser & _
                   ";PWD=" & dmPWD
    Case DBType.SQLServer_OLEDB 'Sql Server OLEDB
      If dmDataShapePrivider = 0 Then
        strConnect = "Provider=SQLOLEDB.1"
      ElseIf dmDataShapePrivider = 1 Then
        strConnect = "Provider=MSDataShape.1;Data Provider=SQLOLEDB.1"
      End If
      strConnect = strConnect & _
                   ";User ID=" & dmUser & _
                   ";PassWord=" & dmPWD & _
                   ";Data Source=" & dmServer & _
                   ";Initial Catalog=" & dmDataSource & _
                   ";Network=DBMSSOCN" & _
                   ";Address=" & dmServer & ",1433"
    Case DBType.ORACLE_OLEDB 'Oracle OLEDB
      If dmDataShapePrivider = 0 Then
        strConnect = "Provider=MSDAORA.1"
      ElseIf dmDataShapePrivider = 1 Then
        strConnect = "Provider=MSDataShape.1;Data Provider=MSDAORA.1"
      End If
      strConnect = strConnect & _
                   ";User ID=" & dmUser & _
                   ";PassWord=" & dmPWD & _
                   ";Data Source=" & dmServer
    Case DBType.ACCESS_OLEDB
      If dmDataShapePrivider = 0 Then
        strConnect = "Provider=Microsoft.Jet.OLEDB.4.0"
      ElseIf dmDataShapePrivider = 1 Then
        strConnect = "Provider=MSDataShape.1;Data Provider=Microsoft.Jet.OLEDB.4.0"
      End If
      strConnect = strConnect & _
                   ";User ID=" & dmUser & _
                   ";Password=" & dmPWD & _
                   ";Data Source=" & dmDataSource & _
                   ";Mode=Share Deny None"
     '没有DNS
     'strConnect = "Provider=MSDASQL;Driver={SQL Server};Server=" & dmServer & ";database=" & dmDataSource & ";uid=" & dmUser & ";pwd=" & dmPWD
'    Case 4 'VFoxPro有数据库容器的访问形式（DBC）
'      'strConnect = "Driver=Microsoft Visual FoxPro Driver;SourceType=DBC;SourceDB=" & dmDataSource & ";uid=" & dmUser & ";pwd=" & dmPWD
'      '表的访问形式
'      strConnect = "Provider=MSDASQL.1;Driver={Microsoft FoxPro VFP Driver (*.dbf)};Collate=Machine;BackgroundFetch=Yes;Exclusive=No;SourceType=DBf;SourceDB=" & dmDataSource & ";UID=" & dmUser & ";PWD=" & dmPWD
'      'Set RS = Conn.Execute("SELECT * FROM Orders.dbf")
'      '数据库的访问形式
'    Case 5 'VFoxPro没有数据库容器的访问形式（DBF）
'      strConnect = "Provider=MSDASQL.1;Driver={Microsoft FoxPro VFP Driver (*.dbf)};Collate=Machine;BackgroundFetch=Yes;Exclusive=No;SourceType=DBF;SourceDB=" & dmDataSource & ";UID=" & dmUser & ";PWD=" & dmPWD
'    Case 6 'FoxPro
'      strConnect = "Provider=MSDASQL.1;Driver={Microsoft FoxPro Driver (*.dbf)};Collate=Machine;BackgroundFetch=Yes;Exclusive=No;SourceType=DBF;SourceDB=" & dmDataSource & ";UID=" & dmUser & ";PWD=" & dmPWD
'    Case 7 'dBase
'      strConnect = "Provider=MSDASQL.1;Driver={Microsoft dBase Driver (*.dbf)};Collate=Machine;BackgroundFetch=Yes;Exclusive=No;SourceType=DBF;SourceDB=" & dmDataSource & ";UID=" & dmUser & ";PWD=" & dmPWD
'    Case 8 'dBase VFP有数据库容器的访问形式（DBC）
'      strConnect = "Provider=MSDASQL.1;Driver={Microsoft dBase VFP Driver (*.dbf)};Collate=Machine;BackgroundFetch=Yes;Exclusive=No;SourceType=DBC;SourceDB=" & dmDataSource & ";UID=" & dmUser & ";PWD=" & dmPWD
'    Case 9 'dBase VFP没有有数据库容器的访问形式（DBF）
'      strConnect = "Provider=MSDASQL.1;Driver={Microsoft dBase VFP Driver (*.dbf)};Collate=Machine;BackgroundFetch=Yes;Exclusive=No;SourceType=DBF;SourceDB=" & dmDataSource & ";UID=" & dmUser & ";PWD=" & dmPWD
'    Case 10
'      strConnect = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & dmDataSource & ";uid=" & dmUser & ";pwd=" & dmPWD  End Select
  dmCnn.CommandTimeout = 45
  dmCnn.CursorLocation = dmCursorLocation
  dmCnn.Mode = dmMode
  dmCnn.Open strConnect
  FunConCnn = True
  Exit Function
Trip:
  MsgBox Err.Description, vbExclamation, App.Title
  FunConCnn = False
End Function