下面的源码示例同时向是
Access和
SQL Server的后台增加字段
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