Results 1 to 10 of 10
  1. #1
    jcc285 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    70

    Split Database with different file types

    I have split my Database with tables now all in a _be file of type .accdb.

    When I attempt to compile the front end into a .accde file for user distribution I get the following error:-



    'Microsoft Access was unable to create the .accde, mde, or ade file'
    'This error is usually associated with compiling a large database into an MDE file. Because of the method used to compile the database, a considerable number of TableID references are created for each table. The Access database engine can only create a maximum of 2048 open TableIDs at one time. Exporting a database as an MDE potentially can exceed this limit if the database has a large number of objects (table, macro, form, report, etc).
    There is no accurate method to estimate the number of TableIDs the Access database engine uses during the process of compiling a database as an MDE. However, each VBA module and each form uses one TableID, as a result, if the database has 500 forms, and each form's HasModule property is set to Yes, as many as 1,000 TableIDs are used.'

    My database has nothing like this number of tables or forms/reports. Is it possible that I can't have a .accdb back end and a .accde front end ?

    Can anyone offer any advice please. I cannot password protect the front end because the encrypted data won't export to Word or Excel which I need to perform. Mail merges etc.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I've never distributed accde frontend so not sure what's going on but did you run Compact & Repair first?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jcc285 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    70
    Quote Originally Posted by June7 View Post
    I've never distributed accde frontend so not sure what's going on but did you run Compact & Repair first?
    I did Compact and Repair both dbs after splitting, however, I was just following this article

    https://support.office.com/en-gb/art...b1d73498cc#bm4

    I particular the section 'Restrict changes to the design of the front-end database'
    The method of creating the accde file is different in Access 2016 from that described in the article, however, I don't know what I am doing wrong.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Here are instructions for Access 2016. https://support.office.com/en-GB/art...9-fd450ba49372
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    jcc285 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    70

    Thumbs down

    Quote Originally Posted by June7 View Post
    Here are instructions for Access 2016. https://support.office.com/en-GB/art...9-fd450ba49372
    Thank you, that document has the following:

    Restrict changes to the design of the front-end databaseTo restrict changes to the front-end database that you distribute, consider saving it as a compiled binary file (an .accde file). A compiled binary file is a database application file that has been saved with all the Visual Basic Access (VBA) code compiled. No VBA source code remains in an Access compiled binary file. Users cannot change the design of objects in an .accde file.
    Open the front-end database file (.accdb) that you want to save as a compiled binary file (.accde).

    Click File > Save As > Make ACCDE > Save As.
    In the Save As box, browse to the folder where you want to save the file, type a name for the file in the File Name box, and then click
    Save

    Yes, this is what I am doing and that generates the error message as before !

  6. #6
    jcc285 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    70
    Quote Originally Posted by June7 View Post
    Here are instructions for Access 2016. https://support.office.com/en-GB/art...9-fd450ba49372
    I've just found this article
    https://support.microsoft.com/en-us/...cannot-compile

    Halfway down it say's:
    • There is a reference to an Access database (.mdb/.accdb) or an Access project (.adp).

    Clearly, there is a reference to an .accdb file because that was what was created when I split the database and, following the split, I believe my front end does indeed hold references to that file.

    The resolution suggested doesn't seem sensible. I don't believe I want to compile my data tables ?

    I'm confused these two articles we have been looking at seem contradictory to me.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The backend would not be converted to accde, only the frontend.

    I think that paragraph is referring to references to other Access files in the VBA Tools>References. An Access file can have code that can be utilized by treating that file as a Library reference. I very much doubt you have this situation.

    Have you run VBA Debug>Compile on the frontend?

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Pardon me for jumping in ...

    First, I ALWAYS deploy my front end compiled as a ACDE/MDE. It is independent of the backend format. I have back ends that are mdb, accdb, mySQL, Excel, and Microsft SQL Server (express and up).

    Compiling a database removes all the source VBA code. There only has compiled VBA code left in the accde/mde.

    That mean that all the VBA code must coplie without errors.

    The most common cause for a front end to not compile into a ACCDE or MDE is VBA code errors.

    To find yurVBA code errors do this:

    1) Open the VBA Editor (alt-F11)
    2) On the menu go to Debug
    3) click Compile (normally the first option on the debug menu)

    I would expect you get a VBA code line highlighted with an error message.

    Fix the error and compile again. Repeat until it compiles without any errors.

    After you can compile without errors, then try making the the ACCDE/MDE.

    TIP: You should create an ACCDE regularly as part of your testing process. This helps find VBA code errors.

    TIP 2: Acess handles un-trapped errors better in a compiled front end (ACCDE/MDE) than with a accdb/mdb.

  9. #9
    jcc285 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    70

    Smile

    Quote Originally Posted by June7 View Post
    The backend would not be converted to accde, only the frontend.

    I think that paragraph is referring to references to other Access files in the VBA Tools>References. An Access file can have code that can be utilized by treating that file as a Library reference. I very much doubt you have this situation.

    Have you run VBA Debug>Compile on the frontend?

    If you want to provide db for analysis, follow instructions at bottom of my post.
    Thank you so much for your help. VBA Debug>Compile found two undeclared variables!!
    Fixed those then I have re-run the 'Save As' .accde and all is well!! - Hooray !!

  10. #10
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Quote Originally Posted by jcc285 View Post
    Thank you so much for your help. VBA Debug>Compile found two undeclared variables!!
    Fixed those then I have re-run the 'Save As' .accde and all is well!! - Hooray !!
    Great job!

    Thanks for the update.

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

Similar Threads

  1. Replies: 1
    Last Post: 08-08-2016, 04:42 PM
  2. Change Data Types in an ODBC Database Table
    By brianwoodburn in forum Database Design
    Replies: 1
    Last Post: 03-19-2015, 12:39 PM
  3. Preventing changes in a split database file
    By kcollop in forum Database Design
    Replies: 2
    Last Post: 07-23-2012, 02:17 PM
  4. Replies: 7
    Last Post: 10-25-2011, 08:32 PM
  5. could not find file due split pasted file
    By alex_raju in forum Access
    Replies: 1
    Last Post: 07-18-2011, 11:27 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