Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139

    Unanswered: File import/export issues

    Hello;
    I have a Access Db that pulls in a source (Excel) spreadsheet, creates a table, runs a query against that table, then spits out a .dbf file for use by another application. When I pull this file in with the source file closed, I get 468 records in the output file. If I have the source file actively open with Excel when pulling it in, I get 516 records in the output file (the correct number of records). The location of the source file has no effect on the process result (desktop or server). I am able to duplicate this situation over and over at will. Can anyone think why this would be happening?

    Thanks…

    Larry

  2. #2
    Join Date
    Dec 2009
    Location
    Lake County, IL
    Posts
    8
    What happens if you link the excel file instead of importing it. How many records do you get in your output.
    Are you doing a manual import or is it automated with VBA? If automated, have you double checked your code for consistency?

    Alan

  3. #3
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    "What happens if you link the excel file instead of importing it."... Don't know, but will try that. Its just that I have been importing it for over a year now and it was working fine. Now that lends itself to the question of what changed since then... I have added more records to the import file, and more records to the table it joins with... that's it. No code changes, no location changes.

    "Are you doing a manual import or is it automated with VBA?"... VBA, but there have been no code changes, and as stated above, the code has been working for over a year. Same import file, same table inside the DB.

    Thanks...

    Larry

  4. #4
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    I would troubleshoot this problem in a test environment. Make a copy of your database file for testing.

    If you can duplicate your problem with the test copy, the obvious first step is to make sure the program is importing the 516 Excel rows that you expect to get. Put a debug break point in the VBA code just after the point where the importing occurs, then run the program with the source file closed, which is the scenario where you have the problem. When the program goes into debug, check the table where the Excel rows were imported. If you have 516 records, then you know that the problem needs to be resolved by troubleshooting the code that is beyond the import stage. Then, using the debug tool, you can check table contents by opening effected table(s) as the code progresses and see where the records are are getting lost.

    Assuming the VBA code includes statements that turn warning messages off and on, another way of checking would be to go into your test copy code and turn off any suppression of error messages. Look for statements that contains "Set Warnings..." and set them all to True. Run the procedure with the Excel file closed and see if you get a warning about a violation. If you get this warning, hopefully it will tell what table or process caused it, and you can then work on the area of the code that needs revision.

    And finally, but do this first because it will only take seconds to try, I would review how data is added to spreadsheet. If new data is added manually, and it is the new data that is causing the problem, then check to see if the new records are of a different format than the old ones.
    Select an individual "old record" Excel cell for each column that is imported, and choose Format, Cells, then note what the format is for each column. Do this for "new record" cells and see if there are differences. That could be a clue to your workaround for fixing the problem: be sure all Excel formats are consistent.
    I hope this helps.

  5. #5
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Thanks JerryDal;

    I will work on your suggestions. Another suggestion offered was to "link" the source spreadsheet within the DB, however I don't think that's possible because the file name changes each week (weekly file update with the date in the file name).

    Thanks...

    Larry

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    I do the same thing have a msaccess database runs at 4pm and upload CSV (base on today date) if not there it send a message to the user telling her that that job is not done yet.

    changing link is easy

    Drop the table then relink it with the


    DoCmd.TransferText acLinkDelim, [Secification Nam], tablename, filename, True


    remeber to use the right "Secification Name"

    what I did was a manual link save the secification name then us that
    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.

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Quote Originally Posted by Larryg View Post
    I get 468 records in the output file. If I have the source file actively open with Excel when pulling it in, I get 516 records in the output file (the correct number of records).
    when I get missing records it mainly because of wrong data type

    ie date feild have a text value in it.
    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.

Posting Permissions

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