Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    Texas, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing Data (Access 2002 SP-2; WinME)

    Got an Access database file from a friend to maintain, refine and update. Don't know the version it was created in. I created a new database table and want to populate the new table from the old. In creating the new table fields, I added data format and value constraints that probably did not exist in the old table. I tried importing into the new table from the old table with no success; so I created an Excel file with column headings matching the database field names and exported the data from the old database file to the .xls file. I manipulated the .xls file to add columns for new fields in the new table and changed the heading row so field names matched. The .xls file has all the data. I then tried to use Get External Data in Access to get data from the .xls file and put it in the new table (which only has the fields defined but contains no records) using the wizard. I keep getting a message that an error occurred and the wizard gives not options. I apologize if this is not clear enough.

    Can I do to get the old data into a new table and put constraints on the fields in the new table so that newly added data will meet the new constraints? What is wrong with my approach?

    All suggestions greatly appreciated and gratefully received.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Importing Data (Access 2002 SP-2; WinME)

    Without details it is impossible to tell. What are you trying to achieve with the constraints and what was your reason for applying them and then importing? Is the wizard creating an import errors table? If so, what are you seeing in that and what message are you getting?
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    Texas, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Data (Access 2002 SP-2; WinME)

    Thank you for the response, Charlotte.

    I get all the way through the Get External Data Wizard; and when I click FINISH, I get the following message from the Import Spreadsheet Wizard: "An error occurred trying to import file "path/filename". The file was not imported." My filename appears where the path/filename is inserted above.

    I built the table with constraints based on conversations with my friend before he sent me the file. The purpose of the constraints is to require dates, numbers, etc. and to limit the length of some fields. I also created some lookup values because the table data comes from an entry form that has a drop down list of choices. The form is on a website coded in html, not in the database.

    I thought that data that didn't conform to lookup values or data validation rules would be truncated or dropped. It's been a long long time since I worked with a dbs and I'm relatively new to Access. I used to use Paragon and dbase years ago; so that might be where I'm getting confused.

    What other information can I provide you?

    Thanks again for the help. It's very kind of you.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Importing Data (Access 2002 SP-2; WinME)

    Access doesn't handle things like xbase databases do, so that might indeed be a part of the problem. It won't just truncate data if it violates the constraints, it will balk at the entire field or the entire record. If you're working in the Access database, look in the tables and see if you have one called something like Import_Errors, which will tell you specific problems with fields. However, if there is never an import, then that table probably doesn't exist. Lookup values in a table don't really serve much purpose if you are developing for anything except Access forms and is of questionable value even then, since data should never be entered directly into tables. Unless you have created a matching constraint, the lookup will not do anything except limit the values that show up in the dropdown. However, if you built some kind of lookup in *Excel*, Access may be choking on it.

    I personally prefer to export an Excel file to tab delimited text and then import from that. Excel doesn't handle some data quite the same way that Access does, so you may encounter conversion issues if you are importing into a table created on the fly. However, if you built a table and are trying to import into it, then it's likely the problem is in the constraints or in the way you have defined the range in the spreadsheet. Can you do a straight import into a new table? If so, that might be the best workaround for now. That would create an import errors table that would tell you about records and fields that cause problems and make it possible to track down the source of the errors.
    Charlotte

Posting Permissions

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