Results 1 to 14 of 14
  1. #1
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98

    Having to split database every time to avoid Access crashing on export/appending.

    I have a single table linked via ODBC. I've always had this issue, but have never taken the time to ask questions, but every time I try to export, append, create table-basically trying to transfer the data somewhere other than its own table-Access always crashes. But every time I re-split my DB and compact and repair the DB as well, then it works. Any hints? There are no results on google with this specific issue.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    This doesn't make a lot of sense but is likely to be corruption. The question is why.

    So you have a frontend and one linked table in a backend database.
    Does the linked table have a PK field? Do you have evidence of data corruption e.g strange characters in some records?
    Are you trying to export (etc) from the front end or backend?

    Do you have multiple users and if so, does each have their own copy of the FE on their own computer?
    What exactly do you mean by re-split the database?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98
    The database is setup fairly simply. Just imported tables from the ODBC connection. DB access is given to just 1 user so no multi-user setup. Not sure about specifics in data corruption because I couldn't find any good answers on the debug process, but what I do know is it was a blank database that had all the tables imported via ODBC. After the import, I would split and repair the database just in case. What is odd too though is if I choose to import that specific table that crashes rather than link the table, Access will still crash. It's like I cannot convert or transfer the information. The only time I've had 100% success exporting that specific table was with iSQL. Excel freezes and encounters an error if I try to import the data via ODBC connection as well. As far as re-splitting goes, I have to split the database again anytime I want to export that table.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Why do you import the tables and then split the database? Why not just use linked tables?
    Also the standard approach is to import (or link) from the destination application.
    Exporting is more likely to cause issues as you have no control over the datatypes in Access

    You haven't stated what the source application is. That may be relevant

    As for your problem table...
    Access crashes if you import it or link to it. Excel also crashes if you import it.
    So it's almost definitely corrupted.
    Look at the data in the source application and check for unusual records with 'Chinese' characters.
    If you find any you will need to fix the problem before relinking from Access.
    Also look at the datatypes in the source table. Does it have any 'unusual' datatypes that Access may not be able to work with?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98
    Not sure on the source application as there is only an FDB file present which is used for ODBC. I always link tables instead of importing them, but I was trying to import the table that was crashing because then it won't be linked to any ODBC connection which would then let me export it without crashing. The table crashes on export/append if it is linked at all. I check the data structure and it looks fine. If I open a new Access DB and link all the tables from the FDB file, the specific table still crashes upon export or append.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I'm still confused by your explanation. You go from talking about import/link to export/append

    Never heard of FDB but this link https://file.org/extension/fdb suggests some possible source apps including MS Dynamics.
    Can you open the FDB file itself using Access?
    Can you find a way to open the table in a text editor or a hex editor?

    Somehow you need to view the data to solve your issue.
    You say the structure looks fine so you must be viewing it somehow.

    I don't have any more ideas to assist on what you've described so far.
    Perhaps someone else will chip in and assist.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98
    Quote Originally Posted by ridders52 View Post
    I'm still confused by your explanation. You go from talking about import/link to export/append

    Never heard of FDB but this link https://file.org/extension/fdb suggests some possible source apps including MS Dynamics.
    Can you open the FDB file itself using Access?
    Can you find a way to open the table in a text editor or a hex editor?

    Somehow you need to view the data to solve your issue.
    You say the structure looks fine so you must be viewing it somehow.

    I don't have any more ideas to assist on what you've described so far.
    Perhaps someone else will chip in and assist.
    Apologies, but I'll do my best on the explanation. I have a single FDB (Firebird Database File) which is then used via ODBC to link the data from the FDB file into MS Access. I can view all the data with no problems. The specific table I continue to have trouble with is 'CUSTOMERS'. If I create an append query with 'CUSTOMERS', export the 'CUSTOMERS' table, or try to convert 'CUSTOMERS' to a local table, that is when Access will crash.

    I appreciate you taking the time to reply and give any sort of opinion. Thanks.

  8. #8
    John_G is offline VIP
    Windows 10 Access 2016
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    You keep saying that Access "crashes". What do you mean by that? Does the Access application itself just quit, and do do you get an error message of any kind?

  9. #9
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Do you have write permissions to the table via the ODBC link?

    If you open the linked table directly in access , can you edit any of the information?
    What ODBC Driver are you using?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  10. #10
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98
    Quote Originally Posted by John_G View Post
    You keep saying that Access "crashes". What do you mean by that? Does the Access application itself just quit, and do do you get an error message of any kind?
    Access closes immediately with no error messages and restarts. All within a couple of seconds.

    Quote Originally Posted by Minty View Post
    Do you have write permissions to the table via the ODBC link?

    If you open the linked table directly in access , can you edit any of the information?
    What ODBC Driver are you using?
    I have no write access to the Database, only read. I can edit the information, but it won't save. With regard to the driver:
    Firebird/InterBase(r) driver -- Version 2.00.05.156 -- ODBCFB.DLL.

  11. #11
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Is the ODBC driver the correct bit level for your machine (e.g.32 or 64 bit?)
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  12. #12
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98
    This driver is currently 32bit running on 64bit Windows 10. The software provider of the driver (not Firebird, this is a custom driver) has only a 32bit version of the driver available. Now that you mention it, I may be running a 64bit version of Office and that could be causing the crashing.

  13. #13
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    That's why I asked.

    I have read that it won't work properly, and that would explain that although the table is viewable as soon as you issue a slightly more complex SQL command/query against the ODBC driver the translation engine fails, hence the "Silent" nature of the failure.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  14. #14
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98
    I'm gunna go ahead and mark this as solved.

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

Similar Threads

  1. Database keeps crashing
    By CQCDave in forum Access
    Replies: 4
    Last Post: 08-02-2018, 01:35 PM
  2. ODBC connections to avoid bandwidth bottleneck on split database
    By notableaudio in forum Database Design
    Replies: 2
    Last Post: 12-18-2015, 10:09 AM
  3. POS Terminals not updating Split Database in Real Time
    By Nod_Swerdna in forum Programming
    Replies: 5
    Last Post: 03-05-2015, 10:01 PM
  4. Replies: 3
    Last Post: 06-22-2014, 10:30 AM
  5. Replies: 2
    Last Post: 09-13-2011, 04:31 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