Results 1 to 12 of 12
  1. #1
    masoud_sedighy is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Posts
    78

    After splitting database importing excel files to linked tables gives error 2950

    Before splitting my database I was using a macro for importing excel file to my table and it was okey. Now after splitting database when I use that macro from front-end I got error:

    Code:
    You cannot record your changes because a value you entered violates the 
    settings defined for this table or list (for example, a value is less than
    the minimum or greater than the maximum). Correct the error and try again."
    The error number on the Macro Single Step pop-up is 2950.
    when i use macro from back-end it does not have problem and works.
    please help because i need importing from front-end.
    equivalent VBA code for macro i have used is like below, it works from back-end and gives error from the front-end:


    DoCmd.TransferSpreadsheet acImport, 8, "Rep_Indicator", "E:\Rep_Indicator", True, ""


  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I import all the time from a split db and never get any error. Ive never seen THIS error.
    but normally, you can import excel using
    docmd.transferspreadsheet
    or
    link the excel wb as an external table, then run an append query.


    you dont need the "" at the end of the transferspreadsheet.
    you may need the .xls(x) at the end of the filename...
    DoCmd.TransferSpreadsheet acImport, 8, "Rep_Indicator", "E:\Rep_Indicator.xls", True

  3. #3
    masoud_sedighy is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Posts
    78
    finally i could import excel file in linked table, i defined range and increase step by step range and execute macro until understand which record has problem.

    what i understood ms access defines type of data from first record of excel file, for example if the data type of one field in the first record of excel file is number, if data type one of the other records in the excel for this field is a text it gives error, i do not know is 100% correct or no? but in my excel file data file of one of the field in the first record was number and in the 20000th record this data type was a text, for solving problem i change the number 1 in the first record to text "01" and then i could import all the data. please explain me if i am correct or no?

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    what i understood ms access defines type of data from first record of excel file
    I think it actually looks at something like the first 10 records.

    but in my excel file data file of one of the field in the first record was number and in the 20000th record this data type was a text, for solving problem i change the number 1 in the first record to text "01" and then i could import all the data. please explain me if i am correct or no?
    That is one way of going about it.

    Personally, I prefer to export my Excel files to text files (CSV or Tab-Delimited), then import my data file into a pre-defined table where I have already defined all the data types for all the fields.
    And I have a saved Import Map which tells the data type of each field.

    I like to do it this way, as importing Excel files can be messy and problematic, since you do not have any control over which data types it picks (where you do for text files).

  5. #5
    masoud_sedighy is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Posts
    78
    just one question what is a saved import map? it is more than of data type we define for each table and what actually does?
    how to make a saved Import Map?


  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    When importing a text file, you go through the whole Import Wizard.
    At the very end of your wizard, before clicking "Finish", if you click on the "Advanced" button you can save the Import Specification that you just built.
    This tells the data type, name, and length of each field.
    By saving it, you can re-use it on future imports of files of the same structure, so you don't need to go through the whole Import Wizard every time.

  7. #7
    masoud_sedighy is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Posts
    78
    Another question about importing text files, when we import text file in a table that data type of some fields in the table are number and some of them date , it automatically convert text to appropriate data type in the table? If this is true i will do all the importing from now with csv or tab-delimited files.

  8. #8
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    In setting up the Import Wizard, you get to designate the Data Type of each field (General, Text, Date), and the Date format that it comes in with. Leave numbers as "General", but select "Date" for your date fields, and pick the correct date layout.

  9. #9
    masoud_sedighy is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Posts
    78
    Actually I do importing by macro , so i can not see the wizard steps , i could just define data type of table , for this situation what i have to do when import text file? Defining data type of table is enough?

  10. #10
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I assume that you are using the TransferText action in your Macro?
    The Import Specification is one of the arguments in that action.
    What I typically do is go through the import once manually to set up and save the Import Specification.
    Then you can use it in your Macro for all your following imports.

  11. #11
    masoud_sedighy is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Posts
    78
    Thank you very much, i always had doubt when i was importing excel file to ms access, it works or not works ? with this solution importing text file and using saved map you solved my problem , before i had this question in other sites but i could not find a clear answer

  12. #12
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You are welcome!
    Glad I was able to help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error 2950 when opening database
    By jfkboise in forum Macros
    Replies: 8
    Last Post: 01-13-2023, 12:02 AM
  2. Replies: 4
    Last Post: 07-21-2017, 01:07 PM
  3. Replies: 3
    Last Post: 04-07-2016, 04:36 AM
  4. Replies: 3
    Last Post: 07-20-2015, 12:23 PM
  5. Replies: 10
    Last Post: 12-28-2012, 02:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums