Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2003
    Location
    USA
    Posts
    21

    Lightbulb Unanswered: Access Automation - The Dreaded Link

    To Anyone/Everyone:

    I am new tot he Access 2k Game and I need to automate the importing of data from a local server to a database I created. The Database already has several forms and reports and a nice little table that is the source of all that ill's me. I need to update all existing data in the table on a daily basis and i am not sure how to procede.

    Any help would be great!

    Thx
    Lakota

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    What format is the data in that you want to import into your access DB?

    S-

  3. #3
    Join Date
    Dec 2003
    Location
    USA
    Posts
    21
    The data is passed to an interna ftp server as a csv file. I have added several other fields to the table once it is ported into access.
    Originally posted by sbaxter
    What format is the data in that you want to import into your access DB?

    S-

  4. #4
    Join Date
    Dec 2003
    Location
    USA
    Posts
    21
    Originally posted by lakota2k
    The data is passed to an interna ftp server as a csv file. I have added several other fields to the table once it is ported into access.
    I guess this questions was not worth answering after all... Can anyone lend assistance?
    thx
    Lakota

  5. #5
    Join Date
    Dec 2003
    Posts
    172
    Lakota2k,

    Import.zip will extract two files, Import.csv and Import.mdb to the C:\ folder.

    Import.csv is a sample comma delimited text file with a some customer names in it. Import.mdb is an Access database containing a customer table, customer query (sorted by last and first name) and a macro called Import.

    The Import macro uses the Transfer-Text method to import data from a CSV/text file into an Access table.

    I have the path of the import file set to C:\ but you should change this property in the Import macro to wherever the real text file is located.

    Note that no prior checking for duplicate customer ids is done here. This example also assumes all the column names in Import.csv match the column names in tblCustomers.

    If you wanted information on how to do more complex imports you will have to tell me.

    Joe G
    Attached Files Attached Files

  6. #6
    Join Date
    Dec 2003
    Location
    USA
    Posts
    21

    Talking

    Originally posted by JoeG
    Lakota2k,

    Import.zip will extract two files, Import.csv and Import.mdb to the C:\ folder.

    Import.csv is a sample comma delimited text file with a some customer names in it. Import.mdb is an Access database containing a customer table, customer query (sorted by last and first name) and a macro called Import.

    The Import macro uses the Transfer-Text method to import data from a CSV/text file into an Access table.

    I have the path of the import file set to C:\ but you should change this property in the Import macro to wherever the real text file is located.

    Note that no prior checking for duplicate customer ids is done here. This example also assumes all the column names in Import.csv match the column names in tblCustomers.

    If you wanted information on how to do more complex imports you will have to tell me.

    Joe G
    Joe G:
    Thanks for the update. I will work on this, but I think I already need a ltting more detail. I am adding columns to the table that is being imported and altering some of the format on col(date) to be general date

    A few things like that. I will add full detail after i work with this last post.

    Thank you again for getting back to me on this.
    Lakota

  7. #7
    Join Date
    Dec 2003
    Posts
    172
    Lakota,

    When you work up more detail, keep in mind these questions:

    How do I control or manage differences between the source table, import.csv and the target table?

    How do I control differences in field types (such as date or text) and field formatting (such as short date or medium date)?

    How do I verify that data currently in the target table is not a duplicate of some or all of my source import data?

    If there are duplicates, am I going to delete those older records completely (and then append the imported records) or will I use an update query to update current records?

    Should I consider importing this new data into a separate Access table first, and then use replication to manage changes, additions or deletions between tables?

    Lakota, many times what you do will be based on how critical the data is, how many records you have to work with (100 or 10,000) and what your budget and time will allow.

    Post here when you have your detail ready. Good luck.

    Joe G

  8. #8
    Join Date
    Dec 2003
    Location
    USA
    Posts
    21
    JoeG:

    Your Import.csv solution wokred great. Thank you again. He is the enw challenge to this never ending story:

    The data is now more critical than it was. before. We are at appox 35k records and getting bigger. I have a single csv file that is being pulled from the same FTP server and we are doing more with it now. The data in the report i am working with at the moment is just being used to display data. No changes to the data are being implemented at this level.

    The new challenge is working on the report. The csv file took care of the import. I have scheduled it to run when I launch access and it seems to be holding its own very nicely.
    I am having issues with splitting the single table and building a query that functions from this set of tables.

    I would love to send you a sample of the data and see what you think.
    Please let me know.

    Thanks again

    Lakota

  9. #9
    Join Date
    Dec 2003
    Posts
    172
    ok, let's see what you've got

    joeg

  10. #10
    Join Date
    Dec 2003
    Location
    USA
    Posts
    21
    Originally posted by JoeG
    ok, let's see what you've got

    joeg
    JoeG: I have not forgotten about you. Just got a better idea on the data. Will be sending in the next day or so.

    Thanks again for your help.

    Lakota

  11. #11
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7

    Question

    Why use the import Function

    all i do is Link the .csv then when
    then use the Autoexec Macro to make the Tables
    Which is scheduled to run once a day

    there is more than one to skin a cat.
    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.

  12. #12
    Join Date
    Dec 2003
    Posts
    172
    lakota2k, sounds good.

    joeg

Posting Permissions

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