Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142

    Database Append Problem - Error Message

    I have a database with quite a few large tables.

    Occasionally, I will delete all the records in a couple of tables and reload them with an Append Query using a smaller specific date range I want to work with. Lately, when I run the append and try to load all the tables with all the records I am getting the following message:

    Cannot open database "XXXXXXXX". It may not be a database that you application recognizes, or the file may be corrupt".



    I have tried compacting and repairing the database but that did not help. When I reduce the volume of records, it starts working again. So it appears that the volume of records is what triggers the problem.

    The entire database is about 2 gig in size, but these individual tables I am talking about, while quite large, are not the lion's share of the data.

    Anyone have any insights into what might being going on.

    Wayne

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    It sounds like you re reaching the max size limit for a 32 bit Access file. Also, you may be trying to append before the delete process is complete.

  3. #3
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142
    It appears I have reached the max database size.

    I will test that to make sure.

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You need to do a compact after the delete and before the append - deleting records does not change the size of the database.

  5. #5
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142
    Quote Originally Posted by aytee111 View Post
    You need to do a compact after the delete and before the append - deleting records does not change the size of the database.
    Even after running a compact and repair I still had the same problem.

    I may have to split it into multiple databases, one for each year and work form there.

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Are you using a split database, with a back-end? If the other tables are "the lion's share" then move those to a BE.

  7. #7
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142
    I have 1 very large database with about 20 tables and at least 100 queries, macros, reports etc...

    There is 1 table that is a monster and a few other large ones. Other tables and queries selectively need data from that monster.

    Right now the data covers 2014-present. My thinking was that since I am typically only interested in recent data I could keep one with 2016 data and then have the older data in a second database that would otherwise be the same.

    What is BE?

  8. #8
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    BE is back-end. That is what the database is called when you split the tables out. I would recommend you read up about it, it is the accepted method. Huge advantages to having everything in one database file.

  9. #9
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142
    So I should make that one monster table it's own database and keep the rest as is?

    I'm not expert at this. Can I then write queries that will be able to access that monster table across databases?

  10. #10
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You really should read up about this, it is standard Access behavior. Usually all tables are in the back-end, except maybe some temporary tables, and they are linked through the Import method. To the front-end it makes no difference where they are located, they are treated the same as now, you won't have to change anything once you have linked them.

  11. #11
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142
    I already do a lot of macro imports of text and Excel files, but I haven't worked with linked databases.

    So I can have multiple databases linked that way?

  12. #12
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Like I say, nothing will need to be changed.

    Create a new database, import all the tables and then delete them from the original database (will now be known as the front-end). In the front-end, import (link) all the tables.

  13. #13
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You might think about moving to SQL Server Express (free version) soon. The max size is 10GB.

  14. #14
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142
    Quote Originally Posted by aytee111 View Post
    Like I say, nothing will need to be changed.

    Create a new database, import all the tables and then delete them from the original database (will now be known as the front-end). In the front-end, import (link) all the tables.
    How is that going to help me?

    I'm still going to run out of room in the new database unless I split tables up among multiple BE databases

  15. #15
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142
    Is it easy to convert all my data and queries to SQL Server Express?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Microsoft Jet database engine ERROR message.
    By jparker1954 in forum Access
    Replies: 6
    Last Post: 06-11-2015, 08:17 AM
  2. Error Message when opening Database
    By lowesthertz in forum Security
    Replies: 5
    Last Post: 03-28-2015, 07:01 AM
  3. Replies: 2
    Last Post: 03-28-2014, 07:30 AM
  4. Custom error message problem
    By thekruser in forum Programming
    Replies: 10
    Last Post: 10-06-2010, 05:14 PM
  5. error message when opening the database
    By dollygg in forum Access
    Replies: 1
    Last Post: 10-04-2010, 08:34 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