Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DBSeeChanges (A2K / SQL Server 2000)

    Hello,
    I have an Access FE hooked up to an SQL DB using ODBC. I can create a record but I am getting a 3622 error while using the OpenRecordset function. It states that I must use the "DBSeeChanges" method on tables that have an identity field. It is true that the table I am writing to has an identity field. It is creating the record properly. Here is the line of code that I use to open the recordset:

    Set rstNewInc = db1.OpenRecordset("Incidents", dbOpenDynaset, dbSeeChanges, dbOptimistic)

    You can see that I have the "dbSeeChanges" set. If I leave out the "..dynaset, seechanges, and optimistic" I get the same error. Does anyone have any ideas?

    Thanks,
    Mark

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DBSeeChanges (A2K / SQL Server 2000)

    I don't know if this will directly address your situation, but it's a start...

    Microsoft KB Article Q190620

    HTH <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DBSeeChanges (A2K / SQL Server 2000)

    And another....

    Microsoft KB Article Q251203

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DBSeeChanges (A2K / SQL Server 2000)

    I went on to some other testing on some other forms. I had the same error on one of them, however the dbSeeChanges fixed that one. For some reason this particular form doesn't like the dbseechanges. I also looked to make sure that I wasn't opening any other recordsets, there is one one. When running the code I see that the value of dbSeeChanges is 512, so therefore I think that it is being set properly. Any other ideas?

    Thanks,
    Mark Santos

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DBSeeChanges (A2K / SQL Server 2000)

    I hate to suggest a different way of doing things, but I've found it much easier to use ADO with SQL Server. Of course, it would require some changes (such as using unbound forms)...

    I'll let you know if I find anything else about your error.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: DBSeeChanges (A2K / SQL Server 2000)

    Have you tried using an updateable View instead of a table, or is that what you're already doing?
    Charlotte

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: DBSeeChanges (A2K / SQL Server 2000)

    A couple of suggestions:
    You might want to check and see what version of the Jet engine you are running. If you aren't running at least SP4 of Jet40 then you should definitely install the latest service pack. There were several weird things that happened because of some excessive restrictions with ODBC linked tables in the early versions of Jet40.

    The other is to specifically trap the error that is occurring. This MS KB article (Q206175) includes a VBA routine that can be used to trap specific ODBC errors.

    Hope one of these helps.
    Wendell

  8. #8
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DBSeeChanges (A2K / SQL Server 2000)

    Hi Wendell, Charlotte, and Mark J,
    I was able to remedy the problem by searching for any occurrence of "OpenRecordset" on the resulting form and then added "dbOpenDynaset, dbSeeChanges" and it worked.

    Thanks again,
    Mark

  9. #9
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DBSeeChanges (A2K / SQL Server 2000)

    Hi Mark,
    I would like to explore ADO and even bought a book on it. The entire project is DAO right now. Our goal is: use existing FE with as little work as possible, then write web based FE to talk with the SQL Server. So, if ADO causes an extensive re-write then I can't see doing it right now. What do you think?

    Thanks,
    Mark

  10. #10
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DBSeeChanges (A2K / SQL Server 2000)

    Hi Charlotte,
    I haven't even begun to explore the whole view / stored procedure thing. All of the queries are stored in the FE and I know that this is not the way to do it for SQL for best performance.

    ...A lot of work to do.....

    Thanks,
    Mark

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DBSeeChanges (A2K / SQL Server 2000)

    Hi Mark,

    It's no secret that DAO is on the way out. When I was first introduced to ADO I hated it! I struggled for months to get used to ADO.

    Now that I work primarily with Web-based data, I find ADO impossible to live without! It's flexible, efficient, and runs very fast! It's also nice that it is supported on many platforms - I'm even using it to write an app in eMbedded Visual Basic for PocketPC now!

    Since you're planning to make some major changes to your app in the future, there's no reason to change everything now - especially if you've found what you need to make this work with DAO.

    HTH <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DBSeeChanges (A2K / SQL Server 2000)

    I'm sure Wendell and Charlotte will have much more to say about this, but I'll get things started with my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>...(forgive me if you're already aware of the following):

    A View is basically the same as a query stored on SQL Server. One way to use views (beyond typical query functions) is to assign security permissions to views and link your data directly from the view - not the table.
    This is similar to the Access idea of keeping users out of Tables by having them work in Forms. If you need to monkey with the structure or data in the tables you won't have to worry about changing the permissions on the tables because the users are working from the related view.

    Stored Procedures are a whole different animal. They are a compiled set of instructions, written in Transact-SQL, that can be used for any of the typical database functions (Create, Retrieve, Update or Delete). Because they're compiled, they execute VERY quickly. It's not one of those things you'll notice with just a few users, but when you have a dozen or more users requesting data every few seconds, it will really make a difference.
    I haven't taken the time to learn T-SQL yet, but you can still create effective stored procedures with the Access-like Query interface in the SQL Enterprise Manager.

    I'll just step aside now and let the real pros (Wendell, Charlotte, and others) take over... <img src=/S/hello.gif border=0 alt=hello width=25 height=29>

  13. #13
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DBSeeChanges (A2K / SQL Server 2000)

    Thanks Mark,
    I did know some of that but I am learning as well. I am sure some of what I have in the front end will need to be moved over. Do you know if there is a generic way to take the SQL strings out of an Access FE file and put them into SQL as views? Or do you have to do it manually?

    Thanks,
    Mark

  14. #14
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DBSeeChanges (A2K / SQL Server 2000)

    AFAIK You should be able to copy and paste SQL from Access Queries to SQL Server Views...

    Stored Procedures are a little different. Rather than investing the time into learning T-SQL, I've cheated in the past... I used an Access ADP connection to the SQL Server to set up Stored Procedures with the Access Query interface. That worked for most basic Stored Procs.

    HTH <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: DBSeeChanges (A2K / SQL Server 2000)

    The SQL Server-supported version of SQL is just different enough from the Access version, so it's usually simpler to create the views in SQL Server in the first place. The other thing to be aware of is that nested queries, action queries and any queries with parameters do NOT translate into views, they're stored procedures.

    To add to what MarkJ said, stored procedures are not just another version of a query. They are more akin to code routines in Access and have far greater capabilities than even a very complex query.
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

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