SQL Server Performance

transfer data from SQL Server to Access.

Discussion in 'SQL Server DTS-Related Questions' started by martin@captivasystems.com, Jul 20, 2005.

  1. I#%92m trying to write a script that transfer tables from sql server to access.<br />The script will have a list of sql server tables and will create these tables in access and then copy all data in the tables. The script I have only copies 1 column to access.<br /><br />Any ideas how I can achieve this?<br /><br />INSERT INTO OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0','Data Source="C:Target.mdb"; User ID=Admin<img src='/community/emoticons/emotion-4.gif' alt=';P' />assword=' )... Table1(viewid)<br />SELECT viewid FROM Table1<br /><br />Thanks,<br />Martin<br />
  2. Madhivanan Moderator

  3. I#%92m getting closer to what I need. I know there is a way to do this, but I forgot. I need to create tables in ms access on the fly with the same table properties as the ones in sql server. I will have few tables in sql server that the data needs to go to those tables in ms access.

    The solution here:http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=7850 is not so good when I have over 100 tables that need to be transfer.

    Thanks,
    Martin
  4. ranjitjain New Member

    I think you have overlooked madhivanans posts...[B)]<br />As you are selecting and inserting only one column value so it is copying 1 column to access so use this query....<br /><br />INSERT INTO OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0','Data Source="C:Target.mdb"; User ID=Admin<img src='/community/emoticons/emotion-4.gif' alt=';P' />assword=' )... Table1(viewid,rowid,spid,objid)<br />SELECT viewid,rowid,spid,objid FROM Table1<br /><br />the above query will select 4 columns and copies them[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by martin@captivasystems.com</i><br /><br />I?m trying to write a script that transfer tables from sql server to access.<br />The script will have a list of sql server tables and will create these tables in access and then copy all data in the tables. The script I have only copies 1 column to access.<br />Any ideas how I can achieve this?<br />INSERT INTO OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0','Data Source="C:Target.mdb"; User ID=Admin<img src='/community/emoticons/emotion-4.gif' alt=';P' />assword=' )... Table1(viewid)<br />SELECT viewid FROM Table1<br /><br />Thanks,<br />Martin<br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
  5. No, I didnt overlook madhivanans post. This query inserts all the columns listed in the query IF AND ONLY IF Table1 already exists in access.

    My question is: how can the query create the tables in access with the same properties as the one in sql server?

    Once the tables are created in access then YES, I can use this query to transfer the data.

    Thanks,
    Martin
  6. Madhivanan Moderator

    I think it is not possible to create table in Access from OpenRowSet
    Generate Script and modify it according to Access database and run it as Access Queries and trnasfer the data using OpenRowSet


    Madhivanan

    Failing to plan is Planning to fail

Share This Page