Results 1 to 9 of 9
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    2003 to 2007 Conversion Issue (2007)

    I am in the process of converting several databases from Access 2003 to Access 2007. In the current converted database, all append queries and insert into code to append data does not work - i.e., nothing happens. The code compiles in 2007 without error and I validated that all of the code runs. Just to make sure I am not losing my sanity, I closed all forms and created a simple table with one field and data. I then created another simple table with one field and no data, created an append query and ran it. The data did not append to the new table in the database converted from 2003 to 2007.

    To see if the converted database is the problem, I closed the database and created a new database in 2007. Then created two simple tables, one with data and one without and tried the append query - of course it worked.

    Anyone come across why an append query or insert into code may be a problem in a database converted from Access 2003 to 2007. BTW if I change the query to a select query I do get data of the same data type as the table I am trying to append to. In addition, if using VB code, the code compiles without any errors. I even tried to get the sql from the query and put it into code and it still did not work. Any ideas?
    Regards,

    Gary
    (It's been a while!)

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: 2003 to 2007 Conversion Issue (2007)

    Have you actually converted the files to accdb format, or are you just trying to run them under 2007?

    I have run a few 2000 format databases under 2007 without having this problem (as far as I know). I will fire up my other computer and double check though.
    Regards
    John



  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: 2003 to 2007 Conversion Issue (2007)

    Yes, the file that is not working properly has been converted to the accdb format.

    Also note that if I open the file in 2007 (not converted) it is also not working. Of course, 2003 works fine. (I am running multiple computers with 2003 and 2007 loaded separately)

    I am puzzled. I think I will try starting with a blank database and import all tables, forms, etc., into it to see if this fixes the problem.

    - On a side note, I have noticed that several other databases that are being converted and in the accdb format are a bit "buggy" such that combo boxes and other controls are doing some odd things such as not populating or suddenly showing no data as if the rowsource was set to nothing. I wonder if the references have anything to do with this.

    Any insight you may have would be welcome. I cant find anything on the knowledgebase and tried some other searches.
    Regards,

    Gary
    (It's been a while!)

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: 2003 to 2007 Conversion Issue (2007)

    John,

    I went ahead and created a blank database in 2007 and then imported all forms, queries, etc., from the 2003 database and everything is now working. I am not sure why the conversion process or saving to 2007 does not work properly.

    One item to note however, when the 2003 database is saved to the accdb format, the references included both the microsoft office 12.0 access database engine object library and the library for DAO - I wonder if they are conflicting. When starting a blank db in 2007 and importing all objects, only the microsoft office 12.0 access database engine object library is used.

    On an aside, the following webpage provides a good review of bugs in 2007 (some of which I have already encountered and sheds some light on some of my issues) http://allenbrowne.com/Access2007.html#Bugs

    Thanks for the reply.
    Regards,

    Gary
    (It's been a while!)

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: 2003 to 2007 Conversion Issue (2007)

    OK - I now give up.

    On the accdb database that I imported all objects, the feature that was broken worked once properly. Basically, what happens, is the user presses a button that runs an insert into sql string to append data to a table. After working properly the first time (i.e., the append sql statement runs without issue) it then just does nothing. No errors, no debug issues, nothing, it just doesnt work. I know the code fires as I put in msgboxes so I can see what is happening. It's only 9:38 and I am ready for a <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,

    Gary
    (It's been a while!)

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: 2003 to 2007 Conversion Issue (2007)

    OK, Here is another odd thing.

    Whenever I open the accdb, the append query or insert into sql statement does not work. However, If I compact and repair the database, it then works fine time and time again. Now if I then close the db and then reopen it, it wont work until the db is compacted and repaired.

    Beats me! <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>
    Regards,

    Gary
    (It's been a while!)

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: 2003 to 2007 Conversion Issue (2007)

    John,

    Here is a work around that works (not sure why but it does) On the accdb that is having problems and the insert into sql statement does not work upon opening, I set the database to compact on close. After closing the db with it set to compact on close and then re-opening it, the code works as it should.

    If I turn off the compact on close and then close and reopen the database, the code does not work. As long as the db is set to compact on close the code appears to be fine. I cant say I understand why this is happening but hopefully it will continue to work without issue.
    Regards,

    Gary
    (It's been a while!)

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: 2003 to 2007 Conversion Issue (2007)

    Gary

    I can't recreate your problem I am afraid.

    I have converted of my dbs to 2007 format, and found some "Insert " sql and it runs without problems.

    I have also created a little test db also works OK. I attach. does this work for you?

    John
    Attached Files Attached Files
    Regards
    John



  9. #9
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: 2003 to 2007 Conversion Issue (2007)

    John,

    Thanks for the help. I tried your sample database and it worked fine. I am at a loss on this one. The only other thing I can think of is going back through the code to see if I have left any connections to a table open via an object while trying to update the data in the table. This still does not explain to me why all of the objects work properly if the compact on close checkbox is checked even though the database is not closed. Thanks again.
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •