如何把从mssql查询到的数据插入到access中

[simon505]

我连接了两个数据库，mssql，access

如何在同一个按钮下操作两个数据库的表

比如，我通过复杂的sql语句从mssql数据库多个表取得了数据

然后插入access数据库某一个表中

[dawugui]

怎样在一个程序中连接两个数据库 
 
　　问：怎样在一个程序中连接两个数据库？数据库在两台机器中，一个是SQL6.5，一个是SQL7.0。

　　答：

　　Transaction mytrans1,mytrans2
　　mytrans1=Create Transaction
　　mytrans2=Create Transaction
　　mytrans1.DBMS="驱动程序类型"
　　mytrans1.DBParm="这里填连接Sql6.5的字串"
　　mytrans2.DBMS="驱动程序类型"
　　mytrans1.DBParm="这里填连接Sql7.0的字串"
　　connect using mytrans1;
　　connect using mytrans2; 
 

[dawugui]

SQL SERVER 和EXCEL的数据导入导出
1、在SQL SERVER里查询Excel数据:
-- ======================================================
SELECT * 
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
下面是个查询的示例，它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。
SELECT * 
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
-------------------------------------------------------------------------------------------------

2、将Excel的数据导入SQL server :
-- ======================================================
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
实例:
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
-------------------------------------------------------------------------------------------------

3、将SQL SERVER中查询到的数据导成一个Excel文件
-- ======================================================
T-SQL代码：
EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'
参数：S 是SQL服务器名；U是用户；P是密码
说明：还可以导出文本文件等多种格式
实例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'
在VB6中应用ADO导出EXCEL文件代码： 
Dim cn  As New ADODB.Connection
cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"
cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"
------------------------------------------------------------------------------------------------

4、在SQL SERVER里往Excel插入数据:
-- ======================================================
insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3)

T-SQL代码：
INSERT INTO  
OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',  
'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]  
(bestand, produkt) VALUES (20, 'Test')  

[dawugui]

SQL SERVER 和ACCESS的数据导入导出

常规的数据导入导出：

使用DTS向导迁移你的Access数据到SQL Server，你可以使用这些步骤: 

1在SQL SERVER企业管理器中的Tools（工具）菜单上，选择Data Transformation 
2Services（数据转换服务），然后选择  czdImport Data（导入数据）。 
3在Choose a Data Source（选择数据源）对话框中选择Microsoft Access as the Source，然后键入你的.mdb数据库(.mdb文件扩展名)的文件名或通过浏览寻找该文件。 
4在Choose a Destination（选择目标）对话框中，选择Microsoft OLE　DB Prov ider for SQL　Server，选择数据库服务器，然后单击必要的验证方式。 
5在Specify Table Copy（指定表格复制）或Query（查询）对话框中，单击Copy tables（复制表格）。 
6在Select Source Tables（选择源表格）对话框中，单击Select All（全部选定）。下一步，完成。

Transact-SQL语句进行导入导出：

1.在SQL SERVER里查询access数据:
-- ======================================================
SELECT * 
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\DB.mdb";User ID=Admin;Password=')...表名
-------------------------------------------------------------------------------------------------
2.将access导入SQL server 
-- ======================================================
在SQL SERVER 里运行:
SELECT *
INTO newtable
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 
      'Data Source="c:\DB.mdb";User ID=Admin;Password=' )...表名
-------------------------------------------------------------------------------------------------
3.将SQL SERVER表里的数据插入到Access表中
-- ======================================================
在SQL SERVER 里运行：
insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source=" c:\DB.mdb";User ID=Admin;Password=')...表名 
(列名1,列名2)
select 列名1,列名2  from  sql表
实例：
insert into  OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'C:\db.mdb';'admin';'', Test) 
select id,name from Test
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\trade.mdb'; 'admin'; '', 表名)
SELECT *
FROM sqltablename

[simon505]

我是这样写的，但是只插入了一条数据，共有30条
string ls_mc,ls_fldm
dec d_zfje,d_je



connect using sqlca;
//
SELECT fpfldmb.mc,
//jzje = sum(isnull(fljsb.jzje, 0)),
zfje = sum(fljsb.je - isnull(fljsb.jzje, 0)),
je = sum(fljsb.je),
fpfldmb.fldm
into :ls_mc,:d_zfje,:d_je,:ls_fldm
FROM fljsb,
jc_fpb,
fpfldmb,
jsb
WHERE ( jc_fpb.jcfldm = fljsb.fldm ) and
( fpfldmb.fldm = jc_fpb.fpfldm ) and
( fljsb.fph = jsb.fph ) and
( ( jsb.czydm = '081' ) and
( jsb.blrq between '2009.10.04 17:13:57' and '2009.10.08 16:40:42' ) and
( jsb.zfbz = 'F' )
)
GROUP BY fpfldmb.fldm,
fpfldmb.mc;

connect using isrc_netupp2;
insert into jkbb (mc, zfje,je,fldm)
values(:ls_mc,:d_zfje,:d_je,:ls_fldm) using isrc_netupp2;

[dawugui]

SELECT fpfldmb.mc,
//jzje = sum(isnull(fljsb.jzje, 0)),
zfje = sum(fljsb.je - isnull(fljsb.jzje, 0)),
je = sum(fljsb.je),
fpfldmb.fldm
FROM fljsb,
jc_fpb,
fpfldmb,
jsb
WHERE ( jc_fpb.jcfldm = fljsb.fldm ) and
( fpfldmb.fldm = jc_fpb.fpfldm ) and
( fljsb.fph = jsb.fph ) and
( ( jsb.czydm = '081' ) and
( jsb.blrq between '2009.10.04 17:13:57' and '2009.10.08 16:40:42' ) and
( jsb.zfbz = 'F' )
)
把这个做成动态生成的DW,然后用循环从DW中获取数据,逐一插入你的access库中去.

动态创建数据窗口 
 
　　在实际应用中，经常需要根据用户需求来动态创建数据窗，一般方法是这样的。
　　在一个window中加入一个数据窗控件，如dw_new,但是该数据窗没有data object,(空白的)就可以用以下语法来创建：
　　dw_new.create(ls_syntax,ls_error) // 创建语法，错误信息
　　ls_syntax可以用以下三种方法来形成：

　　一、动态由sql语法创建：
　　// 连接到pb的example数据库
　　string ls_sql,ls_syntax,ls_error
　　ls_syntax = 'select * from department'
　　ls_syntax = sqlca.SyntaxFromSQL(ls_sql,'style(type=grid)',ls_error)
　　if len(ls_error) >0 then
　　　messagebox('Error','SyntaxFromSQL Error:~r'+ls_error)
　　else
　　　dw_new.create(ls_syntax,ls_error)
　　　if len(ls_error) >0 then
　　　　MessageBox("Error", "Create have these errors: ~r" + ls_error)
　　　else
　　　　dw_new.settransobject(sqlca)
　　　　dw_new.retrieve()
　　　end if
　　end if

　　二、由另一个数据窗的syntax来创建
　　string ls_syntax,ls_error
　　ls_syntax = dw_test.describe('datawindow.syntax')
　　dw_new.create(ls_syntax,ls_error)
　　if ls_error <> '' then
　　　messagebox('Create Error',ls_error)
　　else
　　　dw_new.settransobject(sqlca)
　　　dw_new.retrieve()
　　end if

　　三、读取psr文件来创建
　　string ls_syntax,ls_error,ls_ret
　　ls_ret = char(13)+char(10) //回车键
　　int li_fileNum
　　long li_length
　　li_FileNum = FileOpen("efef.psr",Streammode!, read!, shared!, Replace!)

　　if li_filenum >0 then
　　　FileSeek(li_FileNum, 158, FromBeginning!)
　　　li_length = fileRead(li_filenum,ls_syntax)
　　end if
　　fileclose(li_filenum)
　　if li_length = 0 then return
　　ls_syntax = "release 5;"+ls_ret+ls_syntax 

　　//截掉ls_syntax中的数据部分，5.0以"sparse(names="dept_name?) "作为参考位置
　　//6.0以html(作为参考位置
　　long pos1,pos2
　　pos1 = pos(ls_syntax,'sparse(names="',1)
　　pos2 = pos(ls_syntax,'"',pos1 +16)
　　ls_syntax = left(ls_syntax,pos1) + mid(ls_syntax,pos1 +1,pos2 - pos1 +1)
　　dw_New.create(ls_syntax,ls_error)
　　if ls_error <> '' then
　　　messagebox('Create Error',ls_error)
　　else
　　　dw_new.settransobject(sqlca)
　　　dw_new.retrieve()
　　end if
　　//pb6,pb7的代码可以参照pb5自己写,只是文件头和数据窗结束标记不同而已。 
 
　　PowerBuilder用Create()函数创建动态数据窗口，其语法格式为：dw.Create(Syntax[,ErrString]) 
　　其中：dw为需创建的动态数据窗口名；Syntax为创建动态数据窗口的语法字符串；ErrString为可选参数，用来存放发生错误时的错误信息，若忽
略，发生错误时系统自动显示消息框，一般不符我们需要，所以需定义该参数。

　　显然重点在Syntax, PowerBuilder提供LibraryExport()与SyntaxFromSQL()二个函数来达到这个目的：

　　一、 LibraryExport()函数
　　功能：从PowerBuilder库中输出一个对象，返回该对象的语法。
　　语法格式：LibraryExport(LibName,ObjName,ObjType) 
　　其中：LibName 为带路径的PowerBuilder库名，若未指定路径，则按系统标准搜索路径搜索；ObjName为导出对象名，现为LibName中的数据窗口
对象名；ObjType为该对象的类型，现为数据窗口，值为ExportDataWindow!。示例如下：
　　String ls_DwSyntax,ls_Error
　　ls_DwSyntax=LibraryExport("C:\PBExam\dy_dw.pbl","d_tbl1", ExportDataWindow!)
　　//数据窗口dw_1的产生下面将详细讨论，现暂略
　　dw_1.Create(ls_DwSyntax,ls_Error)
　　//以下语句与下面示例中的相同，故此处略。 
　　LibraryExport()函数是利用已有的数据窗口对象创建动态数据窗口，有一定的使用价值，但不多见。 

　　二、 SyntaxFromSQL()函数
　　功能：基于SQL的SELECT 语句产生创建数据窗口的语法。
　　语法格式：Transaction.SyntaxFromSQL(SqlString,StyleString,ErrorString)
　　其中：Transaction.为已连接的事务对象，一般即为SQLCA；SqlString为SQL--SELECT 语句；Stylestring为数据窗口的显示风格字符串，比较复
杂，一般常用"Style(Type=Grid)"；ErrorString用来存放发生错误时的错误信息。
　　一般来说，SyntaxFromSQL()函数灵活性高、功能强，因此创建动态数据窗口大都使用该函数,下面的示例也是使用该函数。

　　三、创建动态数据窗口的一般步骤
　　创建动态数据窗口的一般步骤如下：
　　1、在某窗口（如w_main）上用鼠标点建一个数据窗口控件（如dw_1），其DataObject为空。
　　2、构造SyntaxFromSQL()函数的语法字符串。这是PowerBuilder动态数据窗口的关键，稍为复杂一些，具体做法请见下面实例。
　　3、用Create()函数创建动态数据窗口dw_1,并用SetTransObject()函数为其分配事务对象，具体做法请见下面实例。
　　这种方法的主要缺点是必须在设计阶段先建数据窗口控件，运行时无法增减，这对于一些较为特殊的应用（如设计阶段尚不知需几个数据窗口）
就不太适合了。那如何解决这个问题呢？经过一番摸索并查阅了一些资料，终于找到了二种解决方法，现分别介绍如下： 　　1、创建一个标准可视数据窗口用户对象u_d_sample
　　PowerBuilder6.0/6.5中步骤为：点击工具栏上的UserObject图标，在弹出的Select User Object窗口中点击New按钮，出现New User Object窗口,双击其中Visual下的Standard图标, 在弹出的Select Standard Visual Type窗口中双击datawindow选项，即出现User Object（Untitled）窗口,点击工具栏上的Save图标，弹出的Save User Object窗口,在User Objects: 中输入u_d_sample回车即进入User Object---u_d_sample窗口,关闭该窗口，标准可视数据窗口用户对象u_d_sample即告建成。

　　PowerBuilder7.0中步骤为：点击工具栏上的New图标，在弹出的New窗口中选择Object页面，双击其中的Standard Visual图标, 在弹出的Select Standard Visual Type窗口中双击datawindow选项，出现User Object（Untitled）inherited from datawindow窗口,将其右边的Title栏中的none删除，再点击左边空白区，然后点击工具栏上的Save图标，以后的操作步骤与PowerBuilder6.0/6.5大致相同。至于PowerBuilder8.0则与PowerBuilder7.0大体相同，不再赘述。

　　2、直接定义DataWindow型变量dw_1如下：
　　DataWindow dw_1
　　dw_1=Create DataWindow 
　　其实第1种方法还须定义dw_1，形式如下：
　　u_d_sample dw_1
　　dw_1=Create u_d_ sample //此句可省略 
　　这二种方法的关键都是使用OpenUserObject()函数,其功能即为打开一个用户对象，语法格式：w_name.OpenUserObject(ObjName[,x,y]) 其中ObjName为需打开的用户对象名；x、y为用户对象的打开位置，省略时值均为0。
　　下面请见具体实例, 该实例在Win98、PowerBuilder8.0/PowerBuilder7.0/PowerBuilder6.5下通过,ODBC数据源已配置，为FoxPro25(可根据需要使用其他数据库),数据表为bb.dbf。请先建窗口w_main,在其Open事件中写入以下代码：(注意：PowerBuilder8.0中先须创建WorkSpace(*.pbw),其他操作基本同PowerBuilder7.0)

　　//动态数据窗口dw_1创建实例
　　string lserr,lsSQLstr,lsDWsyntax,lserrC
　　SQLCA.DBMS="ODBC"
　　SQLCA.DBParm="ConnectString='DSN=FoxPro25'" //本例使用FoxPro25数据库,可根据需要用其他数据库
　　Connect;
　　//下面3条语句即为关键，若dw_1为鼠标点建请删除这3条语句，否则会出错。
　　DataWindow dw_1 //若采用数据窗口用户对象u_d_sample，则可改为：u_d_sample dw_1
　　dw_1=Create DataWindow //若采用数据窗口用户对象u_d_sample，此句应去除。
　　OpenUserObject(dw_1) 
　　//Select…As…的As可将列标题显示为As之后的字符，较为灵活方便。
　　//可根据实际情况设计生成Select语句及Where子句的可视化界面
　　lsSQLstr="Select A12 As 主管部门,A01 As 法人代码,mc As 企业名称,A06 From bb Where A12<'1'"
　　lsDwsyntax=SQLCA.SyntaxFromSQL(lsSQLstr,"style(type=Grid)",lserr) //构造SyntaxFromSQL()函数
　　If Len(lserr)>0 Then
　　　//如果构造SyntaxFromSQL()函数失败，则显示错误信息并退出
　　　messagebox("错误信息!",lserr)
　　　Return
　　end if
　　dw_1.Create(lsDwsyntax,lserrC) //创建动态数据窗口dw_1
　　If Len(lserrC)>0 Then
　　　//如果创建动态数据窗口dw_1失败，则显示错误信息并退出
　　　messagebox("错误信息!",lserrC)
　　　Return
　　end if
　　//以下设置dw_1的一些属性，可根据实际需要设置。
　　dw_1.X=5
　　dw_1.Y=5
　　dw_1.width=1500
　　dw_1.height=650
　　dw_1.Visible=True
　　dw_1.Enabled=True
　　dw_1.HScrollBar=True
　　dw_1.VScrollBar=True
　　//为dw_1分配事务对象SQLCA
　　dw_1.SetTransObject(SQLCA)
　　//提取数据
　　dw_1.Retrieve() 

　　第2种方法的优点大家一看即知，不必费劲去创建一个标准可视数据窗口用户对象，但存在不足之处：不能在窗口w_main的其他事件或控件中引用dw_1。解决方法很简单：将dw_1定义为实例变量（Instance Variable）。PowerBuilder6.0/6.5中：在窗口画笔(w_main)的菜单上点击Declareà Instance Variables…,在弹出窗口Declare Instance Variables中输入datawindow dw_1, 点击Ok按钮即告完成。PowerBuilder7.0/8.0中：打开窗口画笔，在其下半部的窗口Declare Instance Variables中输入datawindow dw_1, 点击工具栏上的Save图标即告完成。 
 

[dawugui]

大致做法是:

STRING Is_Sql , Is_Syntax , Is_Error

Is_sql = "" + &
"SELECT fpfldmb.mc, " + &
" jzje = sum(isnull(fljsb.jzje, 0)), " + &
" zfje = sum(fljsb.je - isnull(fljsb.jzje, 0)), " + &
" je = sum(fljsb.je), " + &
" fpfldmb.fldm " + &
" FROM fljsb, " + &
" jc_fpb, " + &
" fpfldmb, " + &
" jsb " + &
" WHERE ( jc_fpb.jcfldm = fljsb.fldm ) and " + &
" ( fpfldmb.fldm = jc_fpb.fpfldm ) and " + &
" ( fljsb.fph = jsb.fph ) and " + &
" ( ( jsb.czydm = :czydm ) and " + & --这里的变量需要转换，你自己加入。
" ( jsb.blrq between :kssj and :jssj ) and " + & --这里的变量需要转换，你自己加入。
" ( jsb.zfbz = 'F' ) " + &
" ) " + &
"GROUP BY fpfldmb.fldm, " + &
" fpfldmb.mc " + &
"ORDER BY fpfldmb.fldm ASC "

Is_Syntax = SQLCA.SyntaxFromSql(Is_Sql , "Style(Type=Grid)" , Is_Error)
IF LEN(TRIM(Is_Error)) > 0 THEN
MessageBox("系统提示！" , "创建数据窗口语法有错！")
RETURN -1
END IF

dw_1.Create(Is_Syntax , Is_Error)
IF LEN(TRIM(Is_Error)) > 0 THEN
MessageBox("系统提示！" , "创建数据窗口出错！")
RETURN -1
END IF

dw_1.SetTransObject(sqlca)
dw_1.Retrieve()

long i = 0
for i = 1 to dw_1.rowcount()
ls_mc = dw_1.getitemstring(动态DW中的对应的字段的获取)
其他变量的获取
insert into jkbb (mc, zfje,je,fldm) values(:ls_mc,:d_zfje,:d_je,:ls_fldm) ;
end for

[simon505]

for i = 1 to dw_1.rowcount()
ls_mc = dw_1.getitemstring(动态DW中的对应的字段的获取)
其他变量的获取
insert into jkbb (mc, zfje,je,fldm) values(:ls_mc,:d_zfje,:d_je,:ls_fldm) ;
end for

这个地方获取要出错？

[dawugui]

[Quote=引用 7 楼 simon505 的回复:]
for i = 1 to dw_1.rowcount()
    ls_mc = dw_1.getitemstring(动态DW中的对应的字段的获取)
    其他变量的获取
    insert into jkbb (mc, zfje,je,fldm) values(:ls_mc,:d_zfje,:d_je,:ls_fldm) ;
end for

这个地方获取要出错？
[/Quote]
数据窗口中数据的获取你不会?

[simon505]

安你这样操作了，数据库没有一条数据

我是要把mssql查询得到的数据插入access一个表中

[simon505]

提示创建数据窗口出错

STRING Is_Sql , Is_Syntax , Is_Error
string ls_mc
Is_sql = "" + &
"SELECT fpfldmb.mc, " + &
" jzje = sum(isnull(fljsb.jzje, 0)), " + &
" zfje = sum(fljsb.je - isnull(fljsb.jzje, 0)), " + &
" je = sum(fljsb.je), " + &
" fpfldmb.fldm " + &
" FROM fljsb, " + &
" jc_fpb, " + &
" fpfldmb, " + &
" jsb " + &
" WHERE ( jc_fpb.jcfldm = fljsb.fldm ) and " + &
" ( fpfldmb.fldm = jc_fpb.fpfldm ) and " + &
" ( fljsb.fph = jsb.fph ) and " + &
" ( ( jsb.czydm = :czydm ) and " + &
" ( jsb.blrq between :kssj and :jssj ) and " + &
" ( jsb.zfbz = 'F' ) " + &
" ) " + &
"GROUP BY fpfldmb.fldm, " + &
" fpfldmb.mc " + &
"ORDER BY fpfldmb.fldm ASC "

Is_Syntax = SQLCA.SyntaxFromSql(Is_Sql , "Style(Type=Grid)" , Is_Error)
IF LEN(TRIM(Is_Error)) > 0 THEN
MessageBox("系统提示！" , "创建数据窗口语法有错！")
RETURN -1
END IF

dw_1.Create(Is_Syntax , Is_Error)
IF LEN(TRIM(Is_Error)) > 0 THEN
MessageBox("系统提示！" , "创建数据窗口出错！")
RETURN -1
END IF

dw_1.SetTransObject(sqlca)
dw_1.Retrieve()

long i = 0
dec d_zfje,d_je
string ls_fldm
for i = 1 to dw_1.rowcount()
ls_mc = dw_1.getitemstring(i,'fpfldmb_mc')
// 其他变量的获取
insert into jkbb (mc, zfje,je,fldm) values(:ls_mc,:d_zfje,:d_je,:ls_fldm) ;
end for

[simon505]

在创建数据窗口的时候出错

数据的获取肯定会

[dawugui]

[Quote=引用 11 楼 simon505 的回复:]
在创建数据窗口的时候出错

数据的获取肯定会
[/Quote]
创建数据窗口的时候出错,是你的SQL语句不对,你先在查询分析器中去运行SQL语句.

而且已经告诉你了,变量的地方需要转换.
例如:

jsb.czydm = :czydm

要改为: jsb.czydm = '" + czydm + "'"

[wag_enu]

数据管道。

[lss580711]

先建立二個transaction，連接二個資料庫，
然後設定好pipeline通道，
去執行它就可以了