can i transfer data from sql server into excel

[veer]

Hi
i want to transfer data from sql server to access .Is it possible if yes then
give some idea how i will transfer

i did this by connecting with sql server and access data in sql recordset and also open msaccess database but after that i am getting confused
am i going right

please give some idea

[CyberSoftHari]

Yes, you have to use store procedure in Sqlserver database and call it from frontend.
Below Query will explain about insert to MS Access database

INSERT INTO OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 
'Data Source="C:\DB1.MDB ";User ID=Admin;Password=;')...MSAccessTableName (field1, field2, field3)
SELECT field1, field2, field3 
FROM SqlServerTableName 

(I think this should move to Sql Server!)

[veer]

Hi
i use the following query

sQuery = "insert INTO OpenDataSource( 'Microsoft.Jet. OLEDB.4.0;Data Source='D:\Upda tion\YpUpDate_P erformace.mdb'; ')Ypupdate(PgNm br,Yp1EOp,Yp1VO p,Yp1EStartTime ,Yp1EEndTime,Yp 1VStartTime,Yp1 VEndTime,Oks,Cr opImgType)selec t * from Ypdata"

Ypupdate is mdb table name and ypudate is sql table name
but this is showing incorrect systax near D:\

please check and correct this code

Yes, you have to use store procedure in Sqlserver database and call it from frontend.
Below Query will explain about insert to MS Access database

INSERT INTO OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 
'Data Source="C:\DB1.MDB ";User ID=Admin;Password=;')...MSAccessTableName (field1, field2, field3)
SELECT field1, field2, field3 
FROM SqlServerTableName 

(I think this should move to Sql Server!)

[debasisdas]

you need to open two connection string one each for the database.

fetch record from sql server and insert the same into msaccess.

[CyberSoftHari]

You have to try your self (do not let me to check.)
Syntax:
Insert Into OpenDataSource(
'Microsoft.Jet. OLEDB.4.0' (-> Oledb Jet connector to MS Access) ,
'Data Source=D:\Updat ion\YpUpDate_Pe rformace.mdb;Us er ID=Admin;Passwo rd=;')(-> Open database with username and password)
...Ypupdate (-> triple dot tablename to open table)
(f1, f2,f3,f4…) are fields
Usual select query to insert in the access table.

Change your query and debug it yourself.

insert INTO OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=D:\Updation\YpUpDate_Performace.mdb;User ID=Admin;Password=;')...Ypupdate
(PgNmbr,Yp1EOp,Yp1VOp,Yp1EStartTime,Yp1EEndTime,Yp1VStartTime,Yp1VEndTime,Oks,CropImgType)
select * from Ypdata

All the best.

[veer]

Hi
thanks for reply it works and transfer data but when i executes the program by putting break point all the data transfer which i want yes, and filter the data according to my conditions in other tables it all works fine during break points and
when i execute the program without break point it is not transfering all data and not filtering the data and insert into other tables which has worked fine during breakpoints
can you tell me what is wrong if you want my coding the please write me

thanks in advance

varinder

You have to try your self (do not let me to check.)
Syntax:
Insert Into OpenDataSource(
'Microsoft.Jet. OLEDB.4.0' (-> Oledb Jet connector to MS Access) ,
'Data Source=D:\Updat ion\YpUpDate_Pe rformace.mdb;Us er ID=Admin;Passwo rd=;')(-> Open database with username and password)
...Ypupdate (-> triple dot tablename to open table)
(f1, f2,f3,f4…) are fields
Usual select query to insert in the access table.

Change your query and debug it yourself.

insert INTO OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=D:\Updation\YpUpDate_Performace.mdb;User ID=Admin;Password=;')...Ypupdate
(PgNmbr,Yp1EOp,Yp1VOp,Yp1EStartTime,Yp1EEndTime,Yp1VStartTime,Yp1VEndTime,Oks,CropImgType)
select * from Ypdata

All the best.

[CyberSoftHari]

there i said, better use store procedure to do all the data updates and call from front-end.

[veer]

Hi
Thanks For reply
but can you give me some example

pleeeeeeeeeeeee eez

there i said, better use store procedure to do all the data updates and call from front-end.

[CyberSoftHari]

In sql server, select new storeprocedure

Create Procdure ProcedureName
Begin Tran
insert INTO OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=D:\Updation\YpUpDate_Performace.mdb;User ID=Admin;Password=;')...Ypupdate
(PgNmbr,Yp1EOp,Yp1VOp,Yp1EStartTime,Yp1EEndTime,Yp  1VStartTime,Yp1VEndTime,Oks,CropImgType)
select * from Ypdata
 
IF(@@Error != 0)
Begin
Rollback
End
commet
 

and call it from front end..like

connection.Execute ("ProcedureName()")