Consulting

Page 3 of 3 FirstFirst 1 2 3
Results 41 to 57 of 57

Thread: need to import merged workbook into access

  1. #41
    VBAX Master CreganTur's Avatar
    Joined
    Jan 2008
    Location
    Greensboro, NC
    Posts
    1,676
    Location
    The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.
    Each field data type has its own constraints on how many bytes of data it can hold. It sounds like some of your Excel cells may be violating these constraints.
    -Randy Shea
    I'm a programmer, but I'm also pro-grammar!
    If your issue is resolved, please use Thread Tools to mark your thread as Solved!

    PODA (Professional Office Developers Association) | Certifiable | MOS: Access 2003


  2. #42
    Quote Originally Posted by CreganTur
    Each field data type has its own constraints on how many bytes of data it can hold. It sounds like some of your Excel cells may be violating these constraints.
    Data within the cells in excel is small probably under 50 characters so a text data type should be fine, right? anyway for testing i changed the data type to memo and that seemed to work. but somehow blank rows get inserted as either the first or last record which was throwing an error due to a primary key constraint violation.

    basically i need a primary key so i can relate the tables on account number so i can run the join query and find the duplicate account numbers in DecoOpen and DecoOpen (2) for example and then put the results of that query into a report.

  3. #43
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    447
    Location
    Quote Originally Posted by mpearce
    with regard to the mpearce() routine i get an error saying:

    The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

    I just need to get something up and running for management to use. So I guees I want to proceed with something that can be coded quickly and easily.

    I would like to get either the mpearce() routine working or if not that one than the one before it.

    Thanks again guys.
    OK. Let's expedite this project!

    Please make copies of your Access database and Excel workbook. Discard any database objects which don't pertain to this project. Delete any records from those tables which are used to hold the imported Excel data. Compact the database (for Access 2003, you would choose Tools -> Database Utilities -> Compact and Repair Database ...).

    Delete all but a few data rows from your Excel sheets. In the remaining data rows, replace any PHI or other confidential/proprietary information with make-believe values.

    Then add the database file and the workbook file to a Zip file. Attach the Zip file to a message back here.

    Hans

  4. #44
    VBAX Master CreganTur's Avatar
    Joined
    Jan 2008
    Location
    Greensboro, NC
    Posts
    1,676
    Location
    It's stange that it's pulling in blank records, but you can easily counter this by adding in a WHERE clause to the SQL that creates your recordset- use something like WHERE PrimaryField <> Null or PrimaryField <> ""
    -Randy Shea
    I'm a programmer, but I'm also pro-grammar!
    If your issue is resolved, please use Thread Tools to mark your thread as Solved!

    PODA (Professional Office Developers Association) | Certifiable | MOS: Access 2003


  5. #45
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    447
    Location
    Quote Originally Posted by mpearce
    Data within the cells in excel is small probably under 50 characters so a text data type should be fine, right? anyway for testing i changed the data type to memo and that seemed to work. but somehow blank rows get inserted as either the first or last record which was throwing an error due to a primary key constraint violation.

    basically i need a primary key so i can relate the tables on account number so i can run the join query and find the duplicate account numbers in DecoOpen and DecoOpen (2) for example and then put the results of that query into a report.
    No. This query returns all account_num values which exist in both DecoOpen and DecoOpen (2). I don't have any relationships defined, and account_num is not a primary key for either table:

    SELECT a.account_num
    FROM DecoClosed AS a INNER JOIN [DecoClosed (2)] AS b ON a.account_num = b.account_num;

    Depending on the specifics of your project it might make more sense to drop relationship, primary key, or other constraints before importing from Excel. Then do any needed cleansing on the imported data and programatically re-create necessary constraints.

    It will be a whole lot easier to figure all this stuff out if we can see samples of the database and spreadsheets you're working with!

    Hans

  6. #46
    I forgot to clear out some of the tables. So i cleared them and reposted the file.
    Last edited by mpearce; 02-27-2009 at 09:48 AM. Reason: modified attachment

  7. #47
    the only thing with dropping the primary key is that with each click of the button more records are appended to the end of the table.

    so my understanding is that the primary key would stop that from happening. So that if an import is already done and the import is run again access will realize that these records already exist and wont import tham again.

  8. #48
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    447
    Location
    Quote Originally Posted by mpearce
    Alright here is a copy of the access database and the excel file
    Thanks. That was quick. I'll get on it today.

    Hans

  9. #49
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    447
    Location
    Quote Originally Posted by mpearce
    the only thing with dropping the primary key is that with each click of the button more records are appended to the end of the table.

    so my understanding is that the primary key would stop that from happening. So that if an import is already done and the import is run again access will realize that these records already exist and wont import them again.
    I wondered about that. Sounds like we should throw away all records from those 10 Access tables before importing from Excel. That's easy to automate. Let me know if that's not what you want.

    Hans

  10. #50
    originally i had the import as taking each workbook and making each sheet a separate file and then importing the 10 sheets into the table. but there was a bit of overhead with that so i wanted to dumb it down into less steps and make that as automated as possible.

    In the other project the transferspreadsheet method is used for the import. There are primary keys defined in each table and relationships based on those keys. Then there are join queries that run to generate the duplicates. There are reports based on those queries that display the results of the query. Then there is a delete button to clear all tables that is protected by a password.

    So really all i need to redo is the import step, everything else is already in place.

  11. #51
    Quote Originally Posted by mpearce
    originally i had the import as taking each workbook and making each sheet a separate file and then importing the 10 sheets into the table.
    meaning that the user would have to manually parse the sheets from the two workbooks into 10 different excel files.

  12. #52
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    447
    Location
    Quote Originally Posted by mpearce
    In the other project the transferspreadsheet method is used for the import. There are primary keys defined in each table and relationships based on those keys. Then there are join queries that run to generate the duplicates. There are reports based on those queries that display the results of the query. Then there is a delete button to clear all tables that is protected by a password.

    So really all i need to redo is the import step, everything else is already in place.
    I'm not sure if I did the right thing. I thought each table should include only the rows from the current Excel import. So I coded it to empty out the tables immediately before importing from Excel. If that's wrong, we can easily disable the "empty out" step.

    The good news is the attached version of the database imports successfully from the spreadsheets. And it ignores blank rows in the spreadsheets. I didn't get that error about "field too small ..." that bit you. I'm curious to see what happens when you run it with your full scale spreadsheets.

    This thing probably needs refinement, but I thought I'd let you take a look before I put more effort into it.

    Hans

  13. #53
    i ran the updated version against the full spreadsheets and some of the tables have data. However i still get the 'field too small' error with eligibilitynotinhospital.

  14. #54
    sorry that was an easy one. I had to change the field size on some of the text fields to something greater than 50.

  15. #55
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    447
    Location
    Quote Originally Posted by mpearce
    sorry that was an easy one. I had to change the field size on some of the text fields to something greater than 50.
    Cool. I was working on a routine to examine the max width for each of the fields in that table. Don't need it now.

    So is the rest of the import operation working?

    Hans

  16. #56
    The rest of the import seems to be working fine i am excited about that. However there have been a few time where I will get, i think its a runtime error saying "the database has been placed in a locked state" (or something like that).

    There is one small thing i need to do. Originally there is a summary sheet as the 1st sheet in the workbook. Since that sheet isn't needed for analysis I have code that opens each workbook and deletes that sheet before the merge is done and saves the workbook without the summary sheet. That part works fine. But if i try to merge two workbooks and the summary sheet is already deleted the code breaks because it is looking for a sheet that doesnt exist. So I am thinking this is just a simple if...then statement. Something like:

    [VBA]If not object.worksheets("Summary") is nothing then
    object.worksheets("Summary").delete
    else
    msgbox "Summary sheet deleted"
    end if[/VBA]

    i tried to implement this and i get 'subscript out of range'. so that makes me think its something with one of the elements in the array. Could someone confirm this and give a little more detail on the logic here?

    Thanks again for all the help.

  17. #57
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    447
    Location
    Quote Originally Posted by mpearce
    The rest of the import seems to be working fine i am excited about that. However there have been a few time where I will get, i think its a runtime error saying "the database has been placed in a locked state" (or something like that).
    When developing with ADO, I get this error occasionally:

    Error -2147467259 The database has been placed in a state by user 'Admin' on machine 'VM2003' that prevents it from being opened or locked.

    I'm not sure about the cause; it may be code changes not saved/compiled. If I compact the database, the error goes away ... until I make more changes.

    So, I think this may be an error your users won't see. If you find a situation where it does show up for your regular (non-developer) users, or if you were talking about a different error, let me know.

    Meanwhile I added minimal error-handling to Command1_Click. However, I really don't know what more to do. Everything we've done so far is basically the onclick event for a command button on a form in a larger application. (Right?) I think any error handling for spreadsheet import should be integrated with the error handling strategy in the parent application; I have no idea what you're doing there.

    Also, from your users' perspective, what should happen if a spreadsheet import fails? And what about the Access tables: some might have newly imported data; others old data or empty?

    Quote Originally Posted by mpearce
    There is one small thing i need to do. Originally there is a summary sheet as the 1st sheet in the workbook. Since that sheet isn't needed for analysis I have code that opens each workbook and deletes that sheet before the merge is done and saves the workbook without the summary sheet. That part works fine. But if i try to merge two workbooks and the summary sheet is already deleted the code breaks because it is looking for a sheet that doesnt exist.
    What business value do you add by deleting the Summary sheets? I'm thinking "do the simplest thing which could possibly work". The import code is happy if it finds the sheets it's looking for; it doesn't care whether any additional sheets are present in the workbook.

    In other words, your problem figuring out how to delete a sheet which may not be there ... that problem goes away if you don't try to delete the sheet.

    If you really need the sheet gone, you could just attempt the delete and ignore the error you get when the sheet isn't there:

    [vba]On Error Resume Next
    oBook.Worksheets("Summary").Delete
    If Err.Number = 0 Then
    MsgBox "Summary sheet deleted"
    Else
    MsgBox "Summary not sheet deleted"
    End If
    Err.Clear
    On Error GoTo 0 'or your subroutine's error-handler[/vba]
    Why tell anyone whether or not the Summary sheet is deleted?

    I attached a Zip of the database with my latest changes. Beware, I don't know which fields you changed to cure the "The field is too small to accept the amount of data you attempted to add" problem.

    Hans

Posting Permissions

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