Results 1 to 8 of 8
  1. #1
    caper is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    8

    Needing Assistance: Access crashing when importing Large Excel Database. Partial Linking???


    Hey everyone...so I have a huge, growing single source Excel database where new jobs are added. Every time I try to link or add the table to MS Access, it starts crashing. Sometimes I can get the table to save but when I try to do a Dlookup or pull data from that Table into a form, MS Access Locks up. Therefore, I am wondering if I can pull only a few columns from the Excel Database since I only use 5% of the total data.

    For example, Project vs Project Manager. In the Access Form, I want the user to type in the "Job Number"(or drop down list of active "Job Numbers" via the Excel Database). Then the form should automatically display Project Manager, Customer Name, etc.

    Secondary option:
    My assumption is I need to link the Excel, then query the linked table. Is there a better way? In the Form, could I query and Dlookup directly to the excel network link?

    thank you!

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Did try a simple link to the excel as an external table. (in old excel format)
    then run an append query from xl to your table?

    there may be a problem w your access. I had 1 pc that kept crashing ,but after moving the db to another PC it worked fine.

    if that don't work, You can try creating a fresh empty db, then import all objects from the crashing db. ALL objects.

    if that dont work,it could be a conflict in any references you may be using and that 1 PC.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Is there a better way?
    using excel as a source table is generally a bad idea. Import the data you need to a properly indexed table or tables. Excel files are not indexed and can have more than 255 columns (an access limit), so for large datasets you can expect performance issues at best. I certainly wouldn't use a domain function on a linked excel file.

    Further excel does not type it's data, databases (and access is no exception) need typed data. If everything is text, not a problem, but have any text in what should be a numeric or date column and you will get problems - that may be why you are getting crashes.

  4. #4
    caper is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    8
    thanks Ranman, trying that now.
    Ajax: i agree....im an trying to move a fabrication shop to more automated systems....but inorder to do so, I need to prove out this NCR system showing the microsoft access capabilities. (ive architecturally laid it out but never programmed one till this week). SO...their excel spreadsheet is basically a single source location for Job Information, drawing dates, fab dates, labor, etc which is all manually entered...although we do have Solidworks PDM and Encompix EPR systems that we take the manual dates in excel then transfer into all the other systems. In this case, what do you recommend I research for moving that single source job database to that can work with an ERP system and Solidworks and/or solidwork's dump into sequel databases? We get jobs maybe once a week, but we edit the data within that table multiple times a day(shop and office changes). It definitely needs to be a live source.

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I would research 'data normalisation'. Trying to use an excel type table as a single database table will lead to all sorts of complications, not least if it is 'live' data constantly being updated. Once you have the tables and relationships created properly it is relatively easy to create an excel type view if required.

  6. #6
    caper is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    8
    Hey everyone...i am needing to revisit this to see if there is a possible solution or if I need to abort this feature....so this excel is just a job database with job number, customer data, drawing due dates, POs, etc....the file size is 5,792KB and we enter new jobs everyday to it. At some point, I will convince the company to move this database out of excel. Until then...I need my MS access form to pull up 2-3 pieces of information when the user enters the job number. I've already built the code to do this when I manually make a table with the info but what else can i try to import that document....is there a way to have it import the excel table at nighttime once a week? Is there a way I can only import certain data fields or link them?

    could I make another excel query for only the limited data that automatically runs a background query for just the fields i want?

    thanks!!!

  7. #7
    caper is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    8
    FIXED It! the excel import was pulling in the million lines of empty cells....did a filter for "Null" and went down to 400 lines...muchhhhh faster!

  8. #8
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    A question: you say you have an ERP system. Normally all data input and maintenance is done in the ERP system, and exports to excel are only made for reporting purposes. The main goal of every company is having one single point of master data and all entry/maintenance should be done there. Otherwise it's cry havoc and let slip the dogs of war.

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

Similar Threads

  1. Replies: 6
    Last Post: 08-17-2018, 12:04 PM
  2. Replies: 3
    Last Post: 06-20-2018, 05:19 PM
  3. Replies: 12
    Last Post: 10-21-2017, 04:05 PM
  4. Importing large excel file into multiple access files
    By Ghost in forum Import/Export Data
    Replies: 10
    Last Post: 11-05-2013, 11:19 AM
  5. Replies: 6
    Last Post: 12-03-2012, 08:08 AM

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