【Access示例】向后台表新增字段

时　间:2017-01-21 14:02:11
作　者:缪炜   ID：24010  城市：江阴
摘　要:当我们在开发完成后，可能需要在现有的表中增加一个字段，但又不想动手工修改后台表，特别是当有多个后台的时候，那该如何来操作呢？
正　文:

AccessDateSource Access地址与名称，比如：CurrentProject.Path & "\Data.accdb;"

 AccessPWD Access密码，比如：”123”

SQLAdd SQL Server服务器地址，比如:”192.168.1.1”

SQLUser SQL Server用户名,比如 ：”SA”

SQLPWD SQL Server 密码，比如 :”123”

StrName SQL　Server　数据库名称　，比如：”data”

TableName　表名

FieldName　要增加的字段名，比如增加一个字段：“Operator  nvarchar(50)”

FieldName　要增加的字段名，比如：”Operator  nvarchar(50), OperatingTime datetime”

Private Function AddCloumn(ByVal AccessDateSource As String, AccessPWD As String, _
ByVal SQLAdd As String, ByVal SQLUser As String, ByVal SQLPWD As String, ByVal StrName As String, _
ByVal TableName As String, ByVal FieldName As String)

    On Error GoTo ErrorHandler
    Dim rstL As ADODB.Recordset    '本地记录集
    Dim cnnL As ADODB.Connection     '本地连接
    Dim rstS As ADODB.Recordset      '服务器记录集
    Dim cnnS As ADODB.Connection     '服务器连接
    Dim strSQL As String
    Dim cnnStrL As String
    Dim cnnStrS As String
    Set cnnL = New ADODB.Connection
    Set cnnS = New ADODB.Connection
    Set rstL = New ADODB.Recordset
    Set rstS = New ADODB.Recordset
    cnnStrL = "Provider=Microsoft.ace.OLEDB.12.0;Data Source=" & AccessDateSource _
            & " Persist Security Info=false;" _
            & "Jet OLEDB:Database password=" & AccessPWD
    cnnL.ConnectionString = cnnStrL
    cnnL.Open
    If cnnL.State <> 1 Then
        MsgBox "本地数据库连接不成功,请检查服务器配置", vbExclamation
        GoTo ExitHere
    End If
    cnnStrS = "provider=SQLOLEDB;Data Source=" & SQLAdd & ";" _
            & "user id=" & SQLUser _
            & ";password=" & SQLPWD _
            & ";Initial Catalog=" & StrName & ";"
    cnnS.ConnectionString = cnnStrS
    cnnS.Open
    If cnnS.State <> 1 Then
        MsgBox "服务器连接不成功,请检查服务器配置", vbExclamation
        GoTo ExitHere
    End If
    strSQL = "Alter TABLE " & TableName & " ADD " & FieldName
    cnnL.Execute strSQL
    cnnS.Execute strSQL
    MsgBox "新增字段名添加成功。" & vbCrLf & "", vbInformation

ExitHere:
    Set rstL = Nothing
    Set rstS = Nothing
    Set cnnL = Nothing
    Set cnnS = Nothing
    Exit Function

ErrorHandler:
    If Err.Number = -2147467259 Then
        MsgBox "服务器连接不成功,请检查服务器配置", vbExclamation
    Else
        MsgBox Err.Description, vbCritical
    End If
    Resume ExitHere


End Function