|
楼主 |
发表于 2010-6-5 20:28
|
显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
四、excel与sql server服务器的连接:
在建立excel与sql server服务器的连接确保:工程--〉引用--〉选择“Microsoft ActiveX Data Objects 2.0(或者2.1-2.8)Library”
Public Sub 服务器连接测试()
Dim cnn As New ADODB.Connection
Dim cnnStr As String
'建立与SQL Server数据库服务器的连接
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=SQLOLEDB; User ID=sa;Password =密码;Data Source=IP地址"
cnn.Open
On Error GoTo 0
'判断数据库服务器连接是否成功
If cnn.State = adStateOpen Then
MsgBox "数据库服务器连接成功!", vbInformation, "连接服务器"
Else
MsgBox "数据库服务器连接失败!", vbInformation, "连接服务器"
End If
Set cnn = Nothing
End Sub
五、在服务器上建立数据库用表:
Public Sub 创建新的数据库及表()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String, mydata As String, mytable As String
mydata = "数据库名称" '指定数据库名称
'建立与SQL Server数据库服务器的连接
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=SQLOLEDB; User ID=sa;Password =123;password=密码;Data Source=IP地址"
cnn.Open
'判断数据库是否已经存在
sql = "select name from sysdatabases where name='" & mydata & "'"
Set rs = cnn.Execute(sql)
If rs.BOF = False Or rs.EOF = False Then
MsgBox "数据库<" & mydata & ">已经存在!请重新命名数据库!", vbCritical
Exit Sub
End If
'执行SQL语句创建数据库
sql = "create database " & mydata
cnn.Execute sql
MsgBox "数据库创建成功!", vbInformation, "创建数据库"
'关闭与SQL Server数据库服务器的连接
cnn.Close
'建立与刚刚创建的SQL Server数据库的连接
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=SQLOLEDB;" _
& "User ID=sa;" _
& "Password =密码;" _
& "Data Source=IP地址;" _
& "Initial Catalog=" & mydata
cnn.Open
'执行SQL语句创建数据表
sql = "create table 供货商信息" _
& "(供货商编码 varchar(10) not null,供货商名称 varchar(40) not null," _
& "通讯地址 varchar(30) not null,邮政编码 varchar(6) not null," _
& "联系电话 varchar(14) not null,传真号码 varchar(14) not null," _
& "联系人 varchar(10) not null,联系人电话 varchar(14) not null," _
& "联系人Email varchar(50) not null,备注 varchar(50))"
cnn.Execute sql
sql = "create table 物资信息" _
& "(物资类别 varchar(10) not null,物资编码 varchar(10) not null,物资名称 varchar(20) not null," _
& "规格型号 varchar(10) not null,单位 varchar(10) not null)"
cnn.Execute sql
MsgBox "数据表创建成功!", vbInformation, "创建数据表"
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub
[ 本帖最后由 EH2003 于 2010-6-5 20:57 编辑 ] |
|