如何在ACCESS数据库表中创建各种数据类型的字段

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Private Sub Command1_Click()
'长整型
On Error GoTo dataErr
Dim ConnStr As String
Dim SQL As String
    ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & App.Path & "\Db_data.mdb;" & _
               "Persist Security Info=False;" & _
               "Jet OLEDB:Database Password=lee1012"
    conn.Open ConnStr
    rs.CursorLocation = adUseClient
    SQL = "ALTER TABLE 用户信息 ADD 新字段数字 INT NULL"
    rs.Open SQL, conn, adOpenKeyset, adLockPessimistic

'''''''''''''''''''''

 Set rs = Nothing
 Set conn = Nothing

MsgBox "创建成功"

dataErr:
If Err.Number <> 0 Then
  MsgBox Err.Number & Err.Description
  Set rs = Nothing
  Set conn = Nothing
  Exit Sub
End If

End Sub

Private Sub Command2_Click()
'字符串
On Error GoTo dataErr
Dim ConnStr As String
Dim SQL As String
    ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & App.Path & "\Db_data.mdb;" & _
               "Persist Security Info=False;" & _
               "Jet OLEDB:Database Password=lee1012"
    conn.Open ConnStr
    rs.CursorLocation = adUseClient
    SQL = "ALTER TABLE 用户信息 ADD 新字段字符 VARCHAR(40) NULL"
    rs.Open SQL, conn, adOpenKeyset, adLockPessimistic

'''''''''''''''''''''

 Set rs = Nothing
 Set conn = Nothing

MsgBox "创建成功"

dataErr:
If Err.Number <> 0 Then
  MsgBox Err.Number & Err.Description
  Set rs = Nothing
  Set conn = Nothing
  Exit Sub
End If
End Sub

Private Sub Command3_Click()
'boolean是/否
On Error GoTo dataErr
Dim ConnStr As String
Dim SQL As String
    ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & App.Path & "\Db_data.mdb;" & _
               "Persist Security Info=False;" & _
               "Jet OLEDB:Database Password=lee1012"
    conn.Open ConnStr
    rs.CursorLocation = adUseClient
    SQL = "ALTER TABLE 用户信息 ADD 新字段是否 BIT"
    rs.Open SQL, conn, adOpenKeyset, adLockPessimistic

'''''''''''''''''''''

 Set rs = Nothing
 Set conn = Nothing

MsgBox "创建成功"

dataErr:
If Err.Number <> 0 Then
  MsgBox Err.Number & Err.Description
  Set rs = Nothing
  Set conn = Nothing
  Exit Sub
End If
End Sub

Private Sub Command4_Click()
'货币型
On Error GoTo dataErr
Dim ConnStr As String
Dim SQL As String
    ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & App.Path & "\Db_data.mdb;" & _
               "Persist Security Info=False;" & _
               "Jet OLEDB:Database Password=lee1012"
    conn.Open ConnStr
    rs.CursorLocation = adUseClient
    SQL = "ALTER TABLE 用户信息 ADD 新字段货币 MONEY"
    rs.Open SQL, conn, adOpenKeyset, adLockPessimistic

'''''''''''''''''''''

 Set rs = Nothing
 Set conn = Nothing

MsgBox "创建成功"

dataErr:
If Err.Number <> 0 Then
  MsgBox Err.Number & Err.Description
  Set rs = Nothing
  Set conn = Nothing
  Exit Sub
End If
End Sub

Private Sub Command5_Click()
'创建多个字段
On Error GoTo dataErr
Dim ConnStr As String
Dim SQL As String
    ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & App.Path & "\Db_data.mdb;" & _
               "Persist Security Info=False;" & _
               "Jet OLEDB:Database Password=lee1012"
    conn.Open ConnStr
    rs.CursorLocation = adUseClient
    SQL = "ALTER TABLE 用户信息 ADD 新字段数字 INT NULL, 新字段字符 VARCHAR(40) NULL, 新字段是否 BIT, 新字段货币 MONEY"
    rs.Open SQL, conn, adOpenKeyset, adLockPessimistic

'''''''''''''''''''''

 Set rs = Nothing
 Set conn = Nothing

MsgBox "创建成功"

dataErr:
If Err.Number <> 0 Then
  MsgBox Err.Number & Err.Description
  Set rs = Nothing
  Set conn = Nothing
  Exit Sub
End If
End Sub

Private Sub Command6_Click()
'日期/时间
On Error GoTo dataErr
Dim ConnStr As String
Dim SQL As String
    ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & App.Path & "\Db_data.mdb;" & _
               "Persist Security Info=False;" & _
               "Jet OLEDB:Database Password=lee1012"
    conn.Open ConnStr
    rs.CursorLocation = adUseClient
    SQL = "ALTER TABLE 用户信息 ADD 新字段日期 DATE NULL"
    rs.Open SQL, conn, adOpenKeyset, adLockPessimistic

'''''''''''''''''''''

 Set rs = Nothing
 Set conn = Nothing

MsgBox "创建成功"

dataErr:
If Err.Number <> 0 Then
  MsgBox Err.Number & Err.Description
  Set rs = Nothing
  Set conn = Nothing
  Exit Sub
End If
End Sub

 

                                   作者：段利庆 （Lee）  QQ：14035344

                                    一卡通水电暖专家    

                                    http://www.duanliqing.kudu.cn