none
Access 2010 SQL and dbSeeChanges RRS feed

  • Question

  • Thanks for taking the time to read my question.

    I just created a SQL db from my Access tables. They are in SQL Server Express

    (Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) )

     I linked the SQL tables to my Access front end and the data shows up in the forms.

    When I edit a record and then navigate to another record I get:

    "3622 you must use the dbseechanges option with openrecordset when accessing a sql server table that has an IDENTITY column"

    I have looked this up on line and see how to use dbSeeChanges when using recordsets, but how do you implement this requirement when you have a bound form to a table or a query?

    My Access program works well so I know it's no "me", but rather the new connection to SQL instead of having a split db and linked Access tables.

    Any help would be great.

    Thanks,
    Brad

    Friday, October 6, 2017 6:25 PM

Answers

  • Hi,

    I have reviewed my code and found one spot where I was writing to an audit table (tracking changes and who changed record) it did not have ", dbOpenDynaset, dbSeeChanges" on it when doing OpenRecordset. I had it there, but it did not fix things, so I took it out. adding it back seemed to make it work... not sure why. So VERY sorry for all the confusion. Thank you very much for your replies, I truly appreciate it.

    So to clarify for anyone reading this thread, the recordset of the form is a SQL statement without any conditions (SELECT tbl.* FROM tbl;) showing data from a table that is in a SQL database (not an Access table). In my BeforeUpdate event of the form I capture any changes to the record and save the changes to an Audit table. This is where I had the OpenRecordset call. After adding ", dbOpenDynaset, dbSeeChanges" to it, things began working.

    Thanks again to everyone for your replies and helping me identify the issue.

    I wish you all a great week.

    Brad

    • Marked as answer by mbrad Tuesday, October 10, 2017 5:17 PM
    Tuesday, October 10, 2017 5:17 PM

All replies

  • Hi Brad,

    Is the Record Source of your form just the name of the table or are you using a query or a View from SQL Server?

    I'm not sure I understand why you're getting an error at this point.

    Friday, October 6, 2017 6:55 PM
  • Hi,

    thanks for your reply.

    Yes, the Record Source of my form is

    SELECT tblImportedData.* FROM tblImportedData; 

    Brad

    Friday, October 6, 2017 7:09 PM
  • Hi Brad,

    And when you linked the tables, did you use a System or File DSN? Or, are you using a DSN-less connection? If so, can you post the connection string you're using? Thanks.

    Friday, October 6, 2017 7:14 PM
  • Hi,

    I used a DSN.

    Brad

    Friday, October 6, 2017 7:17 PM
  • So, I could be wrong but perhaps the issue could be coming from the DSN setup. Also, double-check the permissions you set up for your tables in SQL Server Express.

    Try creating a new DSN and pay attention to each setting just in case you find something you may have missed the first time.

    Hope it helps...

    Friday, October 6, 2017 7:22 PM
  • Ok, so lets ignore the form for the time being.

    If you open the linked table directly - of which then there is no sql. Can you edit information on that table?

    So for testing, lets not a use a query or sql, and lets not use a form. Just open the linked table and try editing a row in datasheet view (so no forms or sql here).

    Regards,
    Albert D. Kallal (Access MVP, 2003-2017)
    Edmonton, Alberta Canada

    Friday, October 6, 2017 7:28 PM
  • I set up a new DSN

    My Settings:
    Driver = SQL Server (did not select SQL Server Native Client 10.0)
    Name = NCIT_DB

    Which SQL Server do you want to connect to? = (local)
    With Windows NT authentication using the network login ID = Selected

    Change Default Database = NCIT_DB
    Use ANSI quoted identifiers = checked
    Use ANSI null, paddings and warnings = checked

    Perform translation for character data = checked (all others unchecked)

    I tested the connection successfully.

    I added all my tables and got the same error. Not sure what to do differently on the DSN.

    Brad

    Friday, October 6, 2017 7:58 PM
  • Hi Albert,

    Yes, I can edit data right in the table.

    Thanks,
    Brad

    Friday, October 6, 2017 8:00 PM
  • Oh, ok – that certainly a Sherlock homes like “detective” bit of info! (This is good!).

    If you can edit the data in the linked table, then using that linked table for the data source of that form should work. (No real advantage of using a query exists in this regards).

    However, I will much admit that this is perplexing. If the linked table works, then try clicking on that query and again see if you can edit data in that query view. If the query works, then the form should work based on that query. Perhaps there is some additional VBA code that works with record sets in that form?

    Anyway, try launching the query directly – that query you posted certainly looks to be plain Jane, and not out of the ordinary.

    So far, based on what you have, this should work without a hitch.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Saturday, October 7, 2017 12:58 AM
  • Hi Brad,

    In the same token, try changing the Record Source of your form to simply have the table name. If you still can't edit the data on the form, then it has to be something about the form.

    Just a thought...

    Saturday, October 7, 2017 1:43 AM
  • Sorry for the delay.

    I will run through the code on my form as changing the recordsource to just the table did not work either.

    Thanks,
    Brad

    Sunday, October 8, 2017 1:10 PM
  • Hi mbrad,

    I try to find some information regarding the issue.

    I find that you need to set DAO property dbSeeChanges.

    so in code you can do something like below.

    Set rst = CurrentDb.OpenRecordset("SELECT * From tblName", dbOpenDynaset, dbSeeChanges)

    Reference:

    You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column. (Error 3622)

    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.

    Monday, October 9, 2017 8:11 AM
    Moderator
  • That seems rather strange. If you can edit data in the linked table, then setting the forms data source in the property sheet should allow that form to edit data.

    You are “now” introducing the concept of some VBA code and recordsets – that is “huge” different issue.

    You in general have to open a record set like this for it to be updatable:

    Set rstEmployee = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

    So open such recordsets as dbOpenDynaset. However, in general you don’t need or want to open some recordset, and THEN assign that recordset to the form.

    In most cases, just set the form data source directly to the linked table. If you have say a form bound to a table with 1 million rows, and open the form with a “where” clause, then access will ONLY pull the one row from the server. This means that really no extra code is required to filter or reduce the records pulled over the network into that form.

    Eg this air code sample shows this in action:

    Dim strInvoice  as string.

    strInvoice = inputbox("what invoice to work on")

    docmd.OpenForm "frmInvoice",,,"InvoiceNum = " & strInvoice

    So the above will only pull the one record from SQL server, and no reocrdset or additional VBA code is required. And there is ZERO performance gain by introducing some reocrdset code as opposed to the above simple code. (again: Access will ONLY pull the one record).

    I would add (or ensure) that you using dbOpenDynaset as per above to ensure that such recordsets are updatable, but just binding the form directly to the linked table that you noted works fine should result in a form that should be able to freely display and update data – and all without some reocrdset code in-between this simple process.

    So if that linked table is able to edit data, then the simple solution of setting the form to that linked table in the property sheet should work. And to restrict or reduce network traffic down to the one requested record, you can simply add the "where" clause to the openform command as per above.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Monday, October 9, 2017 8:59 AM
  • Hi,

    I have reviewed my code and found one spot where I was writing to an audit table (tracking changes and who changed record) it did not have ", dbOpenDynaset, dbSeeChanges" on it when doing OpenRecordset. I had it there, but it did not fix things, so I took it out. adding it back seemed to make it work... not sure why. So VERY sorry for all the confusion. Thank you very much for your replies, I truly appreciate it.

    So to clarify for anyone reading this thread, the recordset of the form is a SQL statement without any conditions (SELECT tbl.* FROM tbl;) showing data from a table that is in a SQL database (not an Access table). In my BeforeUpdate event of the form I capture any changes to the record and save the changes to an Audit table. This is where I had the OpenRecordset call. After adding ", dbOpenDynaset, dbSeeChanges" to it, things began working.

    Thanks again to everyone for your replies and helping me identify the issue.

    I wish you all a great week.

    Brad

    • Marked as answer by mbrad Tuesday, October 10, 2017 5:17 PM
    Tuesday, October 10, 2017 5:17 PM