Results 1 to 12 of 12

Thread: I need to fetch the data from MS-Access to SQL-Server

  1. 11-09-2006, 04:13 AM #1
    vvenkatesh
    vvenkatesh is offline Registered User
    Join Date
    Oct 2006
    Posts
    19

    Smile I need to fetch the data from MS-Access to SQL-Server

    I want to fetch the data from MS-Access to SQL-Server 2000 and for that i got the solution here
    http://forums.databasejournal.com/sh...ad.php?t=43915 using DTS package in scheduled job. This is works good in my machine (i created one sample MS-Access file and i fetching data from SQL-Server). When i try to connect the MS-Access file which is from my collegue machine it is scheduled (I created DSN for this) but if that MS-Access file is get updated then that time the job scheduled get failed.

    Could anybody help me to solve this issue?
    Thanks in advance...
    Venkatesh
    imtesh@gmail.com
    Reply With Quote Reply With Quote
  2. 11-09-2006, 04:35 AM #2
    vvenkatesh
    vvenkatesh is offline Registered User
    Join Date
    Oct 2006
    Posts
    19

    any other idea...

    Let me know if anybody nows some other way to fetch the data from access to sql server.

    Thanks
    Venkatesh
    imtesh@gmail.com
    Reply With Quote Reply With Quote
  3. 11-09-2006, 10:14 AM #3
    MAK's Avatar
    MAK
    MAK is offline Registered User
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    How about using Linked server
    My Site:http://www.mssqlengineering.com
    My Book:http://www.wiley.com/WileyCDA/WileyT...470477288.html
    Reply With Quote Reply With Quote
  4. 11-09-2006, 11:56 PM #4
    vvenkatesh
    vvenkatesh is offline Registered User
    Join Date
    Oct 2006
    Posts
    19

    I am not able to fix

    In that link they didnt mentioned abt how to connect linked server to MS-Access database. So pls give me some other solution.

    Thanks in advance
    venkateshv
    imtesh@gmail.com
    Reply With Quote Reply With Quote
  5. 11-10-2006, 09:51 AM #5
    rmiao
    rmiao is offline Registered User
    Join Date
    Sep 2002
    Posts
    5,938
    EXEC sp_addlinkedserver
    @server = 'SEATTLE Mktg',
    @provider = 'Microsoft.Jet.OLEDB.4.0',
    @srvproduct = 'OLE DB Provider for Jet',
    @datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'
    GO


    You can find details in sql books online.
    Reply With Quote Reply With Quote
  6. 11-11-2006, 05:38 AM #6
    vvenkatesh
    vvenkatesh is offline Registered User
    Join Date
    Oct 2006
    Posts
    19

    pls come again..

    @server = 'SEATTLE Mktg' here 'SEATTLE Mktg' means?
    wat i need to mention for@server=?


    venkatesh
    Reply With Quote Reply With Quote
  7. 11-11-2006, 07:29 PM #7
    rmiao
    rmiao is offline Registered User
    Join Date
    Sep 2002
    Posts
    5,938
    That's linked server name, can be any name you like to use.
    Reply With Quote Reply With Quote
  8. 11-13-2006, 03:54 AM #8
    vvenkatesh
    vvenkatesh is offline Registered User
    Join Date
    Oct 2006
    Posts
    19

    thanks

    Its working thanks... but i need to fetch the data from ms-access to sql server for every 5 minutes. if any updation taken place in ms-access that need to reflect in sql server..
    is there is any timer function is the sql server to perform this function.

    Thanks in advance
    venkatesh
    Reply With Quote Reply With Quote
  9. 11-13-2006, 07:52 AM #9
    vvenkatesh
    vvenkatesh is offline Registered User
    Join Date
    Oct 2006
    Posts
    19

    update me...

    Is there is any timer function in sql server to call the stored procedure. if so pls post here...
    thanks
    venkatesh
    Reply With Quote Reply With Quote
  10. 11-13-2006, 11:17 AM #10
    rmiao
    rmiao is offline Registered User
    Join Date
    Sep 2002
    Posts
    5,938
    You can schedule a sql job for that.
    Reply With Quote Reply With Quote
  11. 11-15-2006, 03:56 AM #11
    vvenkatesh
    vvenkatesh is offline Registered User
    Join Date
    Oct 2006
    Posts
    19

    could you please help me for that?

    I am new to sql server. So could you please help me how to schedule that linked server. please...thanks...
    venkatesh
    Reply With Quote Reply With Quote
  12. 11-15-2006, 09:46 AM #12
    rmiao
    rmiao is offline Registered User
    Join Date
    Sep 2002
    Posts
    5,938
    Create a sql job, run that sp as command under correct db. Check books online for details.
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules