代码
/*
--数据导出Access
导出数据库中的表到Access,可以同时导出指定的N个表
如果文件不存在,将自动创建文件
如果表存在,将覆盖现有的表
--邹建 2004.04-- */
/* --调用示例
--导出所有用户表
p_exporttb @s_tbname='', @path='c:\',@fname='qq.mdb',@s_user=sa,@s_password=ilovecw
--导出指定表
p_exporttb @s_tbname='ExpenseAccount,ApprovedRecord',@path='c:\',@fname='test.mdb'
-- */
create proc p_exporttb
@s_tbname varchar ( 4000 ) = '' , -- 要导出的表名列表,用逗号分隔,如果不指定,导出所有表
@path nvarchar ( 1000 ), -- 文件存放目录
@fname nvarchar ( 250 ), -- 文件名
@password varchar ( 20 ) = '' , -- ACCESS数据库的密码
@s_user sysname = '' , -- 如果提示连接错误,则需要提供登陆sql服务器的用户名及密码
@s_password varchar ( 50 ) = ''
as
set nocount on
declare @err int , @src nvarchar ( 255 ), @desc nvarchar ( 255 ), @out int
declare @obj int , @constr nvarchar ( 1000 ), @sql varchar ( 8000 ), @fdlist varchar ( 8000 )
-- 参数检测
if isnull ( @fname , '' ) = '' set @fname = ' temp.mdb '
-- 检查文件是否已经存在
if right ( @path , 1 ) <> ' \ ' set @path = @path + ' \ '
create table #tb(a bit ,b bit ,c bit )
set @sql = @path + @fname
insert into #tb exec master..xp_fileexist @sql
-- 数据库创建
select @sql = @path + @fname
, @constr = ' Provider=Microsoft.Jet.OLEDB.4.0;Data Source= ' + @sql
+ case isnull ( @password , '' ) when '' then ''
else ' ;Jet OLEDB:Database Password= ' + @password end
if not exists ( select 1 from #tb where a = 1 )
begin
exec @err = sp_oacreate ' ADOX.Catalog ' , @obj out
if @err <> 0 goto lberr
exec @err = sp_oamethod @obj , ' Create ' , null , @constr
if @err <> 0 goto lberr
exec @err = sp_oadestroy @obj
if @err <> 0 goto lberr
end
-- 连接数据库
exec @err = sp_oacreate ' adodb.connection ' , @obj out
if @err <> 0 goto lberr
exec @err = sp_oamethod @obj , ' open ' , null , @constr
if @err <> 0 goto lberr
-- 导出处理
declare @tbname sysname, @s varchar ( 8000 )
set @s = case isnull ( @s_tbname , '' ) when '' then ''
else ' and name in( ''' + replace ( @s_tbname , ' , ' , ''' , ''' ) + ''' ) ' end
exec ( ' declare tb cursor global for select name from sysobjects where xtype= '' U ''' + @s )
open tb
fetch next from tb into @tbname
while @@fetch_status = 0
begin
-- 删除现有的表
set @sql = ' drop table [ ' + @tbname + ' ] '
exec @err = sp_oamethod @obj , ' execute ' , @out out, @sql
-- if @err<>0 goto lberr
-- 导入表
if isnull ( @s_user , '' ) = ''
set @sql = ' SELECT * into [ ' + @tbname
+ ' ] FROM [ODBC;Driver=SQL Server;Server= ' + @@servername
+ ' ;database= ' + db_name () + ' ].[ ' + @tbname + ' ] '
else
set @sql = ' SELECT * into [ ' + @tbname
+ ' ] FROM [ODBC;Driver=SQL Server;Server= ' + @@servername
+ ' ;Uid= ' + @s_user + ' ;Pwd= ' + isnull ( @s_password , '' )
+ ' ;database= ' + db_name () + ' ].[ ' + @tbname + ' ] '
exec @err = sp_oamethod @obj , ' execute ' , @out out, @sql
if @err <> 0 goto lberr
fetch next from tb into @tbname
end
close tb
deallocate tb
-- 释放对象
exec @err = sp_oadestroy @obj
return
lberr:
exec sp_oageterrorinfo 0 , @src out, @desc out
if @obj <> 0 exec @err = sp_oadestroy @obj
lbexit:
select cast ( @err as varbinary ( 4 )) as 错误号
, @src as 错误源, @desc as 错误描述
select @sql , @constr , @fdlist
go
导出数据库中的表到Access,可以同时导出指定的N个表
如果文件不存在,将自动创建文件
如果表存在,将覆盖现有的表
--邹建 2004.04-- */
/* --调用示例
--导出所有用户表
p_exporttb @s_tbname='', @path='c:\',@fname='qq.mdb',@s_user=sa,@s_password=ilovecw
--导出指定表
p_exporttb @s_tbname='ExpenseAccount,ApprovedRecord',@path='c:\',@fname='test.mdb'
-- */
create proc p_exporttb
@s_tbname varchar ( 4000 ) = '' , -- 要导出的表名列表,用逗号分隔,如果不指定,导出所有表
@path nvarchar ( 1000 ), -- 文件存放目录
@fname nvarchar ( 250 ), -- 文件名
@password varchar ( 20 ) = '' , -- ACCESS数据库的密码
@s_user sysname = '' , -- 如果提示连接错误,则需要提供登陆sql服务器的用户名及密码
@s_password varchar ( 50 ) = ''
as
set nocount on
declare @err int , @src nvarchar ( 255 ), @desc nvarchar ( 255 ), @out int
declare @obj int , @constr nvarchar ( 1000 ), @sql varchar ( 8000 ), @fdlist varchar ( 8000 )
-- 参数检测
if isnull ( @fname , '' ) = '' set @fname = ' temp.mdb '
-- 检查文件是否已经存在
if right ( @path , 1 ) <> ' \ ' set @path = @path + ' \ '
create table #tb(a bit ,b bit ,c bit )
set @sql = @path + @fname
insert into #tb exec master..xp_fileexist @sql
-- 数据库创建
select @sql = @path + @fname
, @constr = ' Provider=Microsoft.Jet.OLEDB.4.0;Data Source= ' + @sql
+ case isnull ( @password , '' ) when '' then ''
else ' ;Jet OLEDB:Database Password= ' + @password end
if not exists ( select 1 from #tb where a = 1 )
begin
exec @err = sp_oacreate ' ADOX.Catalog ' , @obj out
if @err <> 0 goto lberr
exec @err = sp_oamethod @obj , ' Create ' , null , @constr
if @err <> 0 goto lberr
exec @err = sp_oadestroy @obj
if @err <> 0 goto lberr
end
-- 连接数据库
exec @err = sp_oacreate ' adodb.connection ' , @obj out
if @err <> 0 goto lberr
exec @err = sp_oamethod @obj , ' open ' , null , @constr
if @err <> 0 goto lberr
-- 导出处理
declare @tbname sysname, @s varchar ( 8000 )
set @s = case isnull ( @s_tbname , '' ) when '' then ''
else ' and name in( ''' + replace ( @s_tbname , ' , ' , ''' , ''' ) + ''' ) ' end
exec ( ' declare tb cursor global for select name from sysobjects where xtype= '' U ''' + @s )
open tb
fetch next from tb into @tbname
while @@fetch_status = 0
begin
-- 删除现有的表
set @sql = ' drop table [ ' + @tbname + ' ] '
exec @err = sp_oamethod @obj , ' execute ' , @out out, @sql
-- if @err<>0 goto lberr
-- 导入表
if isnull ( @s_user , '' ) = ''
set @sql = ' SELECT * into [ ' + @tbname
+ ' ] FROM [ODBC;Driver=SQL Server;Server= ' + @@servername
+ ' ;database= ' + db_name () + ' ].[ ' + @tbname + ' ] '
else
set @sql = ' SELECT * into [ ' + @tbname
+ ' ] FROM [ODBC;Driver=SQL Server;Server= ' + @@servername
+ ' ;Uid= ' + @s_user + ' ;Pwd= ' + isnull ( @s_password , '' )
+ ' ;database= ' + db_name () + ' ].[ ' + @tbname + ' ] '
exec @err = sp_oamethod @obj , ' execute ' , @out out, @sql
if @err <> 0 goto lberr
fetch next from tb into @tbname
end
close tb
deallocate tb
-- 释放对象
exec @err = sp_oadestroy @obj
return
lberr:
exec sp_oageterrorinfo 0 , @src out, @desc out
if @obj <> 0 exec @err = sp_oadestroy @obj
lbexit:
select cast ( @err as varbinary ( 4 )) as 错误号
, @src as 错误源, @desc as 错误描述
select @sql , @constr , @fdlist
go
If can't run correctly, please try below code or find other help
代码
EXEC
sp_configure ' show advanced options ' , 1 ;
GO
RECONFIGURE
;
GO
EXEC
sp_configure ' Ad Hoc Distributed Queries ' , 1
GO
RECONFIGURE
;
GO
insert into OpenDataSource ( ' Microsoft.Jet.OLEDB.4.0 ' ,
' Data Source="c:\db.mdb";User ID=Admin;Password= ' )...testqq
(ExpenseID,Amount)
select * from ExpenseAccount
sp_configure ' show advanced options ' , 1
GO
RECONFIGURE
GO
sp_configure ' xp_cmdshell ' , 1
GO
RECONFIGURE
GO
sp_configure ' show advanced options ' , 1 ;
GO
RECONFIGURE
;
GO
EXEC
sp_configure ' Ad Hoc Distributed Queries ' , 1
GO
RECONFIGURE
;
GO
insert into OpenDataSource ( ' Microsoft.Jet.OLEDB.4.0 ' ,
' Data Source="c:\db.mdb";User ID=Admin;Password= ' )...testqq
(ExpenseID,Amount)
select * from ExpenseAccount
sp_configure ' show advanced options ' , 1
GO
RECONFIGURE
GO
sp_configure ' xp_cmdshell ' , 1
GO
RECONFIGURE
GO