suggestion to work with a million of recrds

  • I need to import at every day approx 4.500.000 from a sql table into access table, wath is the best and fast way to do tath?
    Note:
    - I have only a reading permission on sql table.
    - need to have every day a refreshed data from sql table
    - is possible to synchronize the two table?

  • Hi Sal,

    some first things to check:
    - Access-database is not unlimited, you can only store until apr. 2GB (access 2003).
    4.500.000 records is a lot of data.
    - What are you going to do with this data? Access is slower than a sql-server...

    If you update every day such amount of data, perhaps it's better to only update the records that are changed, added, ... if possible of course.
    If I do such large updates I use a programming tool visual basic, create an application that I run every day at a certain time.

    regards



  • Yes, in effect i cannot determine if a records is change respect the old data, because the Admin delete all records into the sql table and insert a new data from a DB2 database.


    In other case have a tested code to copy fasted sql atble into access table in vba for excel or vb6 classic.


    note for tthat:


    "If you update every day such amount of data, perhaps it's better to only update the records that are changed, added, ... if possible of course."


    i can compare old with newbut not heve a index to tell me wath is changed respect the old data.
    I need to compare all records "access recor-dset by sql recordset" one to one and in sql table are present 43 fileds.
    In this case i need to compare fildnewsql_1 with the oldfiledacce_1, fildnewsql_2 with the oldfiledacce_2.... i think this opeartion take a big time in query, or not?
    Please help me!!!!!!!!!!!

  • I think 4.5 million records and 43 fields is a bit beyond Access but maybe you should try to create a Access linked table to the SQL database and only create real tables with summary data in the actual Access db. That way you will keep the bulk of the data out of Access and use the SQL engine to do the summarisation.


    When manipulating large amounts of data I'd use SQL where at all possible rather than a programming language.


    What are you going to do with the data when you get it ?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!