12ADD连接外部数据

常用SQL语言

查询数据

select * from [data$]

查询某几个字段

select 姓名,年龄 from [data$]

带条件的查询

select * from [data$] where 性别 = "男“

合并两个表的数据

select * from [data$] union all select *

from [data2$]

插入新纪录

insert into [data$] (姓名,性别,年龄) values

('AA','男',33)

修改一条数据

update [data$] set 性别=‘男’,年龄=16 where 姓名=‘张三‘

删除一条数据

delete from [data$]  where姓名='张三'

使用LEFT JOIN …ON…  (类似于VLOOKUP）

select [data3$].姓名,性别,年龄,月薪 from [data$]

left join [data3$] on [data$].姓名=[data3$].姓名

先UNION ALL 再LEFT

JOIN

select * from (select * from [data$] union

all select * from [data2$])a left join [data3$] on a.姓名=[data3$].姓名

将查询结果赋值到数组

arr=Application.WorksheetFunction.Transpose(conn.Execute("select

* from [data$]").GetRows)

连接代码

Dim conn As New ADODB.Connection

conn.Open "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=D:\data\Edata.xlsx;extended properties=""excel 12.0;HDR=YES"""

conn.Close

ACCESS文件

conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\data\Adata.accdb"

Mysql数据库

conn.Open  "Provider=SQLOLEDB;DataSource=" & Path & ";Initial Catolog=" & strDataName

MSSQL数据库

conn.Open  "Provider=MSDASQL;Driver={SQL Server};Server=" & Path & ";Database=" & strDataName

Oracle数据库

conn.Open  "Provider=madaora;Data Source=MyOracleDB; User Id=UserID; Password=Password"