Results 1 to 6 of 6

Thread: MDB to SQL

  1. #1
    Join Date
    Apr 2009
    Posts
    3

    Unanswered: MDB to SQL

    Hello All
    I new bee. I wanna to update MSSQL2000 from one mdb file. Right now i am deleting the database from the sql 2000 and uploading the data everyime.

    Because if i do it on the database, it is appending the data. so there is a duplication of data. how can i avoid duplication and i can do regular update automatically?

    thanks in advance.
    karthik

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Why not move all the data permanently into SQL Server. You can set up linked tables in Access so that your Access code can use the SQL Server tables.

  3. #3
    Join Date
    Apr 2009
    Posts
    3
    i want to update the data every week, the update should be for that particular week. if i use DTS, it is updating from starting. and i ended with duplication.

    can help me for Access code link.

  4. #4
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    58
    Well since you are using access i imagine the data is pretty small the quickest way is to truncate the table in sql and re-insert all data. Or load the table into a staging table in SQL then create an insert update script that can be called from a sheduled package that compares the two tables in sql and either inserts or updates.

    Create a package that takes your data from access to a staging table in your SQL database.

    Then create a step that takes the two table in SQL and runs a insert where not exists and update where the values are not equal to based on your primary keys.

    Tip. Add a beginning step to clear out your staging table from your previous run. I would do this at the beginning rather than at the end this way if you need to verify records you have the data for the whole week to review it.

  5. #5
    Join Date
    Apr 2009
    Posts
    3
    Well, but the access file changes every week. so i cant do the re-insert all data. your second idea is really good, can you help me with example. i am going to update every 8 hours in a day. the mdb files is getting changed every 4th day r 7th day.

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Well, but the access file changes every week. so i cant do the re-insert all data.
    You can, you just do use a
    Code:
    TRUNCATE TABLE myTAble
    command to clear your SQL Server tables right before inserting the Access data from that particular table.

    If the tables are not big, it is a lot easier than by using the NOT EXISTS scenario.

    Reconsider the advice to create your tables in SQL Server and use linked tables from within Access. If it is possible, go for it. It will solve all your synchronisation problems.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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