Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2014
    Posts
    22

    Unanswered: Access to SQL Server - How to Auto Update Multiple Tables Daily

    I have got 4 MS Access Database Files, which have got 3 Tables each, means Total 12 Tables which gets updated with new data every evening, by an external application. Means new data gets appended to all these 12 Tables.

    I want to have exact same 4 Databases, which have got 3 Tables each, means Total 12 Tables, but WITHIN MS SQL SERVER. And then update all of these 12 Tables every evening, with the corresponding updates from the respective tables from the MS Access Databases.

    Please suggest me the various options to get this kind of work done in SQL Server. I do not want to Manually Update all these 12 tables every evening into SQL Server. Hopefully there would be some easier method to do this in automatic manner.

    Thanks a lot for any ideas.

    Click image for larger version. 

Name:	Auto Update.png 
Views:	4 
Size:	288.6 KB 
ID:	16311

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    why not just link the SQL tables into the msaccess then run some update query.

    or
    you could do some testing link the sql table into the msaccess and if the names are the same and feild names are the same
    the external appliction MITE just up date the SQL table in one go.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    You could import the database files into SQL Server. Right click on the database and then select task then import. I would create a blank database and important into there. Once you select task you scroll up and select Access files and then point to the access database. It looks like 2008 only recognizes mdb files and not accdb files, so you may have to convert. I would recommend renaming the file and doing it to a copy.

    Once you import you could the tables, the data should come right over with it. You could then use the link feature or go straight to the RDBMS. You can set JOBS in SQL Server to run and append your data to your SQL tables.

    There is a lot too it so without knowing your knowledge level it's hard to know where your gaps are.

  4. #4
    Join Date
    Aug 2014
    Posts
    22
    Quote Originally Posted by myle View Post
    why not just link the SQL tables into the msaccess then run some update query.

    or
    you could do some testing link the sql table into the msaccess and if the names are the same and feild names are the same
    the external appliction MITE just up date the SQL table in one go.
    Thanks for your reply myle.

    No, there is no way to update the SQL Server with that application. The data will get updated only into Access Database and we need to find a way to get that into SQL Server from there.

  5. #5
    Join Date
    Aug 2014
    Posts
    22
    Quote Originally Posted by VLOOKUP View Post
    You could import the database files into SQL Server. Right click on the database and then select task then import. I would create a blank database and important into there. Once you select task you scroll up and select Access files and then point to the access database. It looks like 2008 only recognizes mdb files and not accdb files, so you may have to convert. I would recommend renaming the file and doing it to a copy.

    Once you import you could the tables, the data should come right over with it. You could then use the link feature or go straight to the RDBMS. You can set JOBS in SQL Server to run and append your data to your SQL tables.

    There is a lot too it so without knowing your knowledge level it's hard to know where your gaps are.
    Thank you so much for youre reply VLOOKUP.
    I am a noob in SQL Server, does not have any real practical experience and just learning the basics.

    I do not want to end up MANUALLY Importing the new data from all these multiple tables on a daily basis. Therefor I am looking for some method by which we can AUTOMATICALLY Import all the new data into SQL Server on a daily basis with just a few clicks. I am not aware of the options that are available in this regards for a noob like me. I cannot write any complex scripts etc. for this work. Please suggest the easiest options for such a scenario.

    Thanks for your help.

  6. #6
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Yeah once you get the tables into the MS SQL server you can the set up JOBS to run to append or do whatever you need to the data AUTOMATICALLY. I never suggest manual solutions UNLESS its a one off process.

Posting Permissions

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