Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jan 2008
    Location
    Australia
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data not displayed in table (2003/2007)

    Data I write to file using ADO is not turning up in the table even though the primary key (autonum) is being added to (as per a msgbox). I have experimented with ADO 2.1 to 2.8 as well as 6.0 in the References.

    Hans, sorry I have been so long about this but I was using the wrong browser (Opera) & therefore couldn't add this test file.
    Attached Files Attached Files

  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: Data not displayed in table (2003/2007)

    Can you convert your sample db to an mdb file. Many users of the Lounge can't read .accdb files as they require Access 2007.
    Regards
    John



  3. #3
    New Lounger
    Join Date
    Jan 2008
    Location
    Australia
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data not displayed in table (2003/2007)

    Here's the file in 2003 format
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data not displayed in table (2003/2007)

    I don't know why it doesn't work. I'd use DAO instead of ADO. I have always found it to be much easier to use and much more dependable than ADO.

    Private Sub cmdSave_Click()
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)
    With rst
    .AddNew
    !one = Me.txtTest
    MsgBox !ID
    .Update
    .Close
    End With
    Set rst = Nothing
    End Sub

  5. #5
    New Lounger
    Join Date
    Jan 2008
    Location
    Australia
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data not displayed in table (2003/2007)

    Hi Hans, thanks for the quick response. The reasons why I use ADO in preference to DAO are:

    1) Microsoft keep threatening to remove any support for DAO and have been doing so since Office 2003 came out. I know that they have now put that back to at least 2016 because of the huge number of legacy databases that use DAO. Therefore I thought it prudent to learn ADO early. I must say that I find it easier than DAO once you get used to it.

    2) It is a part of my work duties to both investigate and document the procedures for placing all Access Databases within my organisation (+2000 employees and there are a lot of Access dbs out there) that will have to to be supported by the DBA team (previously Oracle only, now, to their disgust, SQL Server as well) . These databases will be converted from the .mdb format to the .adp format, ie, Access Data Project. ADO has to be used in this process. In fact, according the documentation I have been reading, a database built using DAO has to be basically rewritten into ADO for it to be converted to a .adp. One also has to rejig booleans as they have a different numbering in SQL Server and one has to be careful of any queries as they are converted to Views. The SQL with SQL server is readable, unlike that created by the Jet Engine. I used to write SQL off the top of my head when using Oracle but have never been able to do that with Access as the bracketing within the Jet version is over the top. Lastly, prior to conversion, linked tables must be included with the front end so that they get converted and placed into SQL server. I am really impressed with the end result. Having not used SQL Server at all until just a month ago when I downloaded the free version for Vista, I have found the managing/maintenance of a converted Access database is easy and the tools that Microsoft include (even in the free version) are great.

    The thing that really peeves me about this problem is that I have created any number of databases in ADO prior to this occuring. The only other thing I can think of is to reinstall Office completely and see what happens then as maybe, just maybe, a corruption has occured within the Jet engine. Wouldn't be the first time that this has happened.

    Anyway, thanks once again.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data not displayed in table (2003/2007)

    About 1: DAO was the default data object model in Access 97 and before. Microsoft intended to replace it with ADO, and in Access 2000 and 2002, ADO was the default. But the idea was abandoned, and in Access 2003 and 2007, DAO was reinstated as the default.

    About 2: you have a valid point here.

    (Writing Jet SQL isn't that hard - if you don't use spaces in field and table names, you don't need to enclose them in square brackets [ ], and you don't have to emulate the excessive use of parentheses - they are created by the interactive query designer to make it easy to add and remove criteria, but the SQL will work fine without them)

    I don't think reinstalling Office will help - the problem with ADO occurs in my Access 2002 too, while the DAO code runs flawlessly.

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

    Re: Data not displayed in table (2003/2007)

    When I change adLockBatchOptimistic to adLockOptimistic your code works for me.

    If I want to keep adLockBatchOptimistic then it will work if I change .update to .Updatebatch
    Regards
    John



  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data not displayed in table (2003/2007)

    Ah, of course, good catch! When using batch update mode, Update doesn't execute the update, it only adds it to the list of transactions waiting to be executed in batch.

Posting Permissions

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