By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,805 Members | 1,270 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,805 IT Pros & Developers. It's quick & easy.

can i transfer data from sql server into excel

100+
P: 198
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
May 24 '08 #1
Share this Question
Share on Google+
8 Replies


Expert 100+
P: 487
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
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 
  2. 'Data Source="C:\DB1.MDB ";User ID=Admin;Password=;')...MSAccessTableName (field1, field2, field3)
  3. SELECT field1, field2, field3 
  4. FROM SqlServerTableName 
(I think this should move to Sql Server!)
May 24 '08 #2

100+
P: 198
Hi
i use the following query

sQuery = "insert INTO OpenDataSource('Microsoft.Jet.OLEDB.4.0;Data Source='D:\Updation\YpUpDate_Performace.mdb';')Ypu pdate(PgNmbr,Yp1EOp,Yp1VOp,Yp1EStartTime,Yp1EEndTi me,Yp1VStartTime,Yp1VEndTime,Oks,CropImgType)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
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 
  2. 'Data Source="C:\DB1.MDB ";User ID=Admin;Password=;')...MSAccessTableName (field1, field2, field3)
  3. SELECT field1, field2, field3 
  4. FROM SqlServerTableName 
(I think this should move to Sql Server!)
May 26 '08 #3

debasisdas
Expert 5K+
P: 8,127
you need to open two connection string one each for the database.

fetch record from sql server and insert the same into msaccess.
May 26 '08 #4

Expert 100+
P: 487
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:\Updation\YpUpDate_Performace.mdb;User ID=Admin;Password=;')
(-> 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.
Expand|Select|Wrap|Line Numbers
  1. insert INTO OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=D:\Updation\YpUpDate_Performace.mdb;User ID=Admin;Password=;')...Ypupdate
  2. (PgNmbr,Yp1EOp,Yp1VOp,Yp1EStartTime,Yp1EEndTime,Yp1VStartTime,Yp1VEndTime,Oks,CropImgType)
  3. select * from Ypdata
All the best.
May 26 '08 #5

100+
P: 198
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:\Updation\YpUpDate_Performace.mdb;User ID=Admin;Password=;')
(-> 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.
Expand|Select|Wrap|Line Numbers
  1. insert INTO OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=D:\Updation\YpUpDate_Performace.mdb;User ID=Admin;Password=;')...Ypupdate
  2. (PgNmbr,Yp1EOp,Yp1VOp,Yp1EStartTime,Yp1EEndTime,Yp1VStartTime,Yp1VEndTime,Oks,CropImgType)
  3. select * from Ypdata
All the best.
May 28 '08 #6

Expert 100+
P: 487
there i said, better use store procedure to do all the data updates and call from front-end.
May 28 '08 #7

100+
P: 198
Hi
Thanks For reply
but can you give me some example

pleeeeeeeeeeeeeeez




there i said, better use store procedure to do all the data updates and call from front-end.
May 28 '08 #8

Expert 100+
P: 487
In sql server, select new storeprocedure
Expand|Select|Wrap|Line Numbers
  1. Create Procdure ProcedureName
  2. Begin Tran
  3. insert INTO OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=D:\Updation\YpUpDate_Performace.mdb;User ID=Admin;Password=;')...Ypupdate
  4. (PgNmbr,Yp1EOp,Yp1VOp,Yp1EStartTime,Yp1EEndTime,Yp  1VStartTime,Yp1VEndTime,Oks,CropImgType)
  5. select * from Ypdata
  6.  
  7. IF(@@Error != 0)
  8. Begin
  9. Rollback
  10. End
  11. commet
  12.  
and call it from front end..like

Expand|Select|Wrap|Line Numbers
  1. connection.Execute ("ProcedureName()")
May 28 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.