locked
SQL Statement be cut off in the Macro when mdb file run in Access 2016 RRS feed

  • Question

  • Hi,

    Anyone has same issue like I get:

    SQL Statement be cut off in the Macro when the old mdb file open in Access 2016

    Thanks!

    Wednesday, August 30, 2017 1:59 PM

Answers

  • Or you could have someone else do it, someone with the correct version already installed on his/her machine. For example, you could pass the file around as it gets converted from a lower to a higher version.

    Just a thought...

    • Marked as answer by Daphne1212 Wednesday, September 6, 2017 8:15 PM
    Thursday, August 31, 2017 9:51 PM
  • Hi Daphne1212,

    you can try to visit link below will give you some information about what kind of issues can be occur when you upgrade your database from 97 to newer version of Access.

    Converting from Access 97

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Daphne1212 Wednesday, September 6, 2017 8:15 PM
    Friday, September 1, 2017 1:05 AM

All replies

  • Hi,

    Since this is ms-access specific I'm moving your question to the "Access for developer's forum"


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, August 30, 2017 2:16 PM
  • Hi Daphne,

    Are you opening the MDB in Design mode using Access 2016? If so, are you trying to upgrade/convert it to ACCDB?

    Wednesday, August 30, 2017 2:43 PM
  • Yes. I save it in accdb and realize all macro stop working because it takes first row of the SQL statement.  I did try retrieve all the sql codes from the original mdb file. it's not there but the macro works.
    Wednesday, August 30, 2017 5:18 PM
  • Can you post what the macro looks like? Thanks.
    Wednesday, August 30, 2017 5:45 PM
  • INSERT INTO OwnerTemp     cut off after here

    FROM

    SELECT ......  from  table / quey where ....

    Wednesday, August 30, 2017 6:02 PM
  • No, you said the macro works in the MDB. I just wanted to see what the macro looks like. Thanks.
    Wednesday, August 30, 2017 6:04 PM
  • Yes, can't see entire SQL but it run. Now, I convert it to accdb, stop works. I have to rewrite the SQL to make it work. I just want know any other having better solution.  Thanks!
    Wednesday, August 30, 2017 6:18 PM
  • Hi,

    I understand what you said is happening, but I can't help you fix it without knowing what it looked like before.

    How exactly did you convert the MDB into a ACCDB? Have you tried using a stored query rather than a SQL statement?

    Just a thought...

    Wednesday, August 30, 2017 6:23 PM
  • Thanks! I using save as accdb. That's the problem?

    I have recreated all the SQL statement. it took much time to do it. I surprised Microsoft didn't mention it as a BIG warning.  

    Wednesday, August 30, 2017 6:46 PM
  • Maybe. You could try the following next time:

    1. Create a blank ACCDB using 2016

    2. Go to External Data tab on the Ribbon and click on Access in the Import & Link group

    3. Click on the Browse button and navigate to your MDB file, select it, then click Open

    4. Click OK

    5. Go through each tab (Tables, Queries, Forms, etc.) and click on Select All for each one

    6. Click OK to import all objects from the old MDB to the new ACCDB

    7. Click Close when the import process is done

    Hope it helps...

    Wednesday, August 30, 2017 7:26 PM
  • Hi Daphne1212,

    can you try to post your old database with dummy data in it.

    so that we can try to make a test with it and try to run a macro after converting it to accdb.

    I have some confusion, you had mentioned that you cannot see the macro but it is working.

    so when you try to convert the database you can able to see the macro?

    but it is not working.

    if you post the database then we can try to find the cause of the issue.

    does it happen with all the macros or with any particular macro?

    it is also possible that some corruption happen in database when you convert it.

    did you try to compact and repair?

    if not you can also try that and let us know about the result.

    so that we can try to provide further suggestions if needed.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 31, 2017 2:07 AM
  • I guess the old mdb in Access 97 allows multiple lines to complete the SQL statement. But new doesn’t.  When we run the old database in Access 2016 version. Macro still works.  However, I can't see the rest of the lines in the SQL statement since the new version's SQL statement window allow single line.  If we convert mdb to accdb even using import all the objects as theDBguy mentioned.     I still lost all the lines in the SQL statement except the first line.

    I think the only way I can get back I need to install Access 97 till Access 2003 to open the mdb file. Get to see entire statement. I will give it try next time.

    Thursday, August 31, 2017 12:41 PM
  • Hi Daphne,

    I didn't realize this was an Access 97 MDB. If so, then yes, the upgrade path should go through 2000 or 2003 first and then maybe even 2007 or 2010 next before 2016.

    Good luck!

    • Marked as answer by Daphne1212 Thursday, August 31, 2017 8:15 PM
    • Unmarked as answer by Daphne1212 Thursday, August 31, 2017 8:16 PM
    Thursday, August 31, 2017 3:33 PM
  • Thanks! you means I need to convert it to 2000, then 2007 then 2016. So, I need to install Access 2000 and Access 2007. I am not sure it mess up my computer Access system file?
    Thursday, August 31, 2017 9:43 PM
  • Or you could have someone else do it, someone with the correct version already installed on his/her machine. For example, you could pass the file around as it gets converted from a lower to a higher version.

    Just a thought...

    • Marked as answer by Daphne1212 Wednesday, September 6, 2017 8:15 PM
    Thursday, August 31, 2017 9:51 PM
  • Hi Daphne1212,

    you can try to visit link below will give you some information about what kind of issues can be occur when you upgrade your database from 97 to newer version of Access.

    Converting from Access 97

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Daphne1212 Wednesday, September 6, 2017 8:15 PM
    Friday, September 1, 2017 1:05 AM