再讲ＡＤＯ

hyefeifei · 发表于 2016-4-1 21:35

本帖最后由 hyefeifei 于 2016-5-16 19:27 编辑

　　关于ado这个话题，曾试讲了一次，因效果不甚理想，致半途而废，自己水平有限固是主因，但ADO本身难讲，且用sql数据库的朋友相对不多，不能不说也是原因，此次再讲，不知结果如何，且讲且看吧，如果反响多些，或许多讲些。当然讲的过程，也是与网友交流学习的过程，希望大家多批评指正。

　　此次采取以问题为驱动的方式来讲，闲话少叙，进入正题。

　　一、如何把excel的数据写入数据库（1）

　　例表：（取自本版网友提问贴）

　　

　　程序代码：

Sub InsertFltab()
Dim cmd As Object
Dim arr
Dim i As Long
Connect "Test"
Conn.Open
arr = ThisWorkbook.Worksheets("Fltab").Range("a2:e29")
Set cmd = CreateObject("adodb.command")
With cmd
      .CommandText = "INSERT INTO fltab values(?,?,?,?,?)"
      Set .activeconnection = Conn
      For i = 1 To UBound(arr)
         cmd.Execute Parameters:=Array(arr(i, 1), arr(i, 2), arr(i, 3), arr(i, 4), arr(i, 5)), _
                     Options:=adCmdText + adExecuteNoRecords
      Next
End With
End Sub

　　Connect "Test" → 为了不妨碍主要代码，把固定的连接数据库代码做成了单独的程序。附件中会有完整代码。它的意思是连接Test数据库，此前在此数据库上已经建了Fltab表，表的结构如图：
　　

　　Conn.Open→因为在Connect程序中有Conn.close，所以这里要把它打开，另外，Conn在模块顶部声明为：Public Conn as object,各位打开附件一看就明白了。

　　arr = ThisWorkbook.Worksheets("Fltab").Range("a2:e29")→这句就不用讲了吧。

　　Set cmd = CreateObject("adodb.command")→可以说，在ado中，从来不用command对象，也能完成所有任务，但有时候使用它会简化程序。这里我使用了后期引用，纯属习惯。

　　CommandText = "INSERT INTO fltab values(?,?,?,?,?)"→command的commandtext属性设置命令文本，这个属性是必须的，没命令内容，也就无所谓执行命令了。

　　INSERT INTO fltab values(?,?,?,?,?)一句，完整应为：
　　INSERT INTO fltab (ZY编号,序号,部件,零件,工序) values(?,?,?,?,?)

　　这里把字段名省略了，如果你不是插入所有字段的值，比如你只想插入序号，工序这两个字段的值，其他字段都是空值，就不能省了，你必须告诉ado你插入的值对应哪个字段。

　　Set .activeconnection = Conn→这个属性设置一个活动连接对象，以便与数据库通信
　　cmd.Execute Parameters:=Array(arr(i, 1), arr(i, 2), arr(i, 3), arr(i, 4), arr(i, 5)),Options:=adCmdText + adExecuteNoRecords
　　Execute方法执行commandtext属性设置的命令，它有三个参数，咱们一个一个来讲：

　　①RecordsAffected，顾名思义，它返回的是你执行命令，影响了多少条记录。在这里，如果我们使用它，须先声明一个长整形的变量 dim affs as long,代码如下：

cmd.Execute RecordsAffected:=affs,Parameters:=Array(arr(i, 1), arr(i, 2),arr(i, 3), arr(i, 4), arr(i, 5)),Options:=adCmdText + adExecuteNoRecords

在这里，它返回的始终是1,因为我们每次插入了一条记录。

　　②Parameters，它接受一个数组，这个数组每个值，对应前面的问号。

　　③Options，这个参数是一个位掩码形的参数，各位只须记住adCmdText +
adExecuteNoRecords 这个组合就可以了，adCmdText是说.CommandText属性的内容是sql语句，adExecuteNoRecords是说Execute方法不要返回记录。其他组合基本不必使用。

　　位掩码：这里提到位掩码参数，精确解说各位可百度，这里举例说明一下：

　　比如说一个对象有abcd四个类型，可用1，2，3，4 表示；又有三种颜色rgb(红绿蓝)，可用1，2，3表示，那么我们可以用2个参数参数甲=2,参数乙=3,表示b型蓝色，但是我们有没有可能使用一个参数来表示b型蓝色呢？当然可以，我们可用参数丙=2+3来表示，但这里有一个问题，比如，我们要表示a型红色，a型为1，红色为1，我们用参数丙=1+1 表示，但1+1为2,这里2表示的是b型，也表示绿色，这怎么办？答案在于如何设计参数的值，这里，我们这样设计参数 abcd四个类型用 2的0次方，2的1次方，2的2次方，2的3次方表示abcd，rgb三种颜色，用2的4次方，2的5次方，2的6次方表示，这样 abcd对应1，2,4，8，rgb对应16，32，64，当我们如此设置了参数值，我们无论如何组合参数，都不会有重复，各位可以试试看。现在，我们要表示a型红色，就可以用参数丙=1+16来表示，如果我们要表示b型黄色呢？稍懂颜色的人都知道，黄色为红+绿，所以我们可以用参数丙=2+16+32来表示，2为b型，16为红色，32为绿色。

　　说回Options,它可以是下面常量的组合：

adCmdText	1	用SQL语句给CommandText赋值
adCmdTable	2	用表名给CommandText赋值
adCmdStoredProc	4	用存储过程名CommandText赋值
adCmdUnknown	8	指出在CommandText中命令的类型未知
adAsyncExecute	16	异步地执行操作
adAsyncFetch	32	异步地获得记录
adAsyncFetchNonBlocking	64	异步地获得记录，不阻碍随后的操作
adExecuteNoRecords	128	指出CommandText不返回记录

　　也许有的朋友注意到，我用的是后期引用，使用的却是adcmdtext这样的常量，实际上，我是把程序中使用的常量，都用 public const adCmdText as long=1这种方式做了声明，毕竟adCmdText这种常量用的久了可以记住，而1234这些数字，再怎么用也难以记住。但如果比如你在asp中用ado的话，那就只能用数字了，记得我在坛里发过ado常量列表，可位可以找找看。

　　好了，各位运行以上程序，向数据库插入数据后，进到数据库，查看fltab表，会发现表如图所示：

　　

　　大家看“ZY编号”一列，很显然有错误，为什么会发生错误，如何解决呢？加一条语句就可了，答案在下一讲，有兴趣可先百度一下，我们下次继续讲如何把excel的数据写入数据库。

　　本讲附件：

再讲ADO1.rar (27.66 KB, 下载次数: 960)

学不完用不尽 · 发表于 2016-4-1 23:03

支持一下！

gbtt520 · 发表于 2016-4-1 23:23

正准备学习ADO，顶顶楼主，可不要太监

1578278106 · 发表于 2016-4-2 00:03

支持一下。

microyip · 发表于 2016-4-2 07:54

领教，大师，能解决我之前提出来的问题吗？《求教ODBC的UPDATE问题》http://club.excelhome.net/thread-1262666-1-1.html

xd3210 · 发表于 2016-4-2 09:14

期待下一讲！

ydlpc · 发表于 2016-4-3 06:13

支持一下正用得着

五百次的回首 · 发表于 2016-4-3 08:32

学习一下经验

hyefeifei · 发表于 2016-4-3 10:06

本帖最后由 hyefeifei 于 2016-5-16 14:49 编辑

　　上一讲虽然应者寥寥，还是继续一讲再看吧。

　　在回答上讲未了提出的问题前，先讲点题外话：

　　1、虽然是以提出问题的方式讲解，但目的不是为了解决问题，而是为了讲ado一些重要的方法属性参数，问题只是引子，对问题给出的答案并不一定是最佳答案。

　　2、ado虽然可连excel表，但它的出现却是为了连各种数据库的，所以建议大家最好以excel+sql server的方法学习。

　　3、学习之前，应该搭建环境，强烈推荐vmware workstation 这款软件，它甚至可以搭建vsphere环境，强大之处各位可百度一下。有了它，建一个虚拟机，装上sql server2008,学ado就方便多了，如果没有它，也没有局域网环境，这个ado还真没学。

　　4、我之前说过，学vba，最重要的就是ado，为什么？因为通过ado+sql数据库可以开发多用户程序，二是它可以大大简化vba程序，原先复杂的代码，仅用简单的sql语句代替就可以了，且它的效率一般来说比我们自己写的vba代码要高。其他的好处就不多言了。

　　5、现在电脑成本这么低，在单位弄一台机器做服务器不是很难的事情，如果实在挪腾不出一台电脑做sql服务器，也可以买一台nas，nas上装mysql。

　　题外话到这里。上讲在向数据库插入数据时，出现的问题如截图所示：

　　

　　之所以数据库表的记录的值会被截掉，是因为ado在向数据库插入数据时，并不知道数据库表各字段的数据类型，所以它只好根据第一个插入的记录猜测，第一个记录 zy编号字段的插入值是“ZY9902-1010”,所以ado判断zy编号字段的数据类型是11位的字符串，后面再插入记录，超过11位的，都被截掉了。

　　那么，如何解决这个问题呢？解决方法之一是在ado插入记录前，先让ado到数据库上查一下各字段的数据类型，然后就可以正确插入了。

　　如何让ado到数据库上查字段的数据类型呢？使用Command的Parameters集合的Refresh方法就可以了，新程序如下：

Sub Insert1()
Dim cmd As Object
Dim arr
Dim i As Long
Connect "Test"
Conn.Open
arr = ThisWorkbook.Worksheets("Fltab").Range("a2:e29")
Set cmd = CreateObject("adodb.command")
With cmd
      .CommandText = "INSERT INTO fltab values(?,?,?,?,?)"
      Set .activeconnection = Conn
   .Parameters.Refresh                                              ‘此一句为新增
   For i = 1 To UBound(arr)
         cmd.Execute Parameters:=Array(arr(i, 1), arr(i, 2), arr(i, 3), arr(i, 4), arr(i, 5)), _
                     Options:=adCmdText + adExecuteNoRecords
      Next
End With
End Sub

　　.Parameters.Refresh 会向服务器查询各字段的数据类型，当程序执行到这一句时，ado会向sql服务器提交下面的sql语句：
SET FMTONLY ON select * from fltab WHERE 1=2 SET FMTONLY OFF

　　这个sql语句只向客户端返回元数据,也就是我们所说的字段信息。有了各字段的信息了，再插入记录就不会出错了。

　　但是，这样做，需要我们在插入记录时，先访问服务器取回字段信息，而这是不必要的，因为做为程序开发者，我们自己是知道各字段的数据类型的，所以不必向数据库服务器取，由我们自己告诉ado各字段数据类型就可以了，这样做无疑会减少服务器的压力。
　　

　　那么，我们自己如何告诉ado各字段的数据类型呢？下一讲接着讲。

hyefeifei · 发表于 2016-4-3 16:09

本帖最后由 hyefeifei 于 2016-4-3 16:11 编辑

microyip 发表于 2016-4-2 07:54
领教，大师，能解决我之前提出来的问题吗？《求教ODBC的UPDATE问题》http://club.excelhome.net/thread-126 ...

你的这个看了一下，没试你的程序，一般没这样做的，非从excel表更新数据库表的话，可用openrowset函数，
程序如下：

sSQL_Server =服务器IP
sSQL_User = 登录名
sSQL_Password =密码
Set Conn = CreateObject("adodb.connection")
strConn = "Provider=SQLOLEDB;" & _
"Data Source=" & sSQL_Server & ";" & _
"User ID=" & sSQL_User & ";Password=" & sSQL_Password
Conn.Open strConn
Conn.DefaultDatabase =数据库名
strSQL = "UPDATE a " & _
"SET a.字段2 = 'ABCC' " & _
"FROM SQLDB AS a " & _
" ,OPENROWSET( " & _
" 'Microsoft.ACE.OLEDB.12.0' " & _
" ,'Excel 12.0;HDR=Yes;IMEX=1;Database=excel文件的全路径' " & _
" ,'select * from [测试数据表$k1:n]') AS b " & _
"WHERE a.字段1 = b.字段1"
Conn.Execute strSQL

复制代码

		自动登录	找回密码
密码			免费注册

[原创] 再讲ＡＤＯ

评分