Link to home
Start Free TrialLog in
Avatar of ACanadian
ACanadian

asked on

Error 3197 When using DAO in Access 2002 with ODBC to SQL 2000

Hi,

I have read many other posts on this site about access and error 3197. Seems no one have found a real solution yet. But I thought I would post the question anyways.

Here is my setup: Win Xp, Access 2002, SQL 2000

The problem I am facing is this, I imported my access mdb into sql 2000, created an ODBC link to sql 2000 and linked up my tables in access.  Now when I attempt to make changes to the database using DAO, I get error

"3197 - The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time. "

This doesn't make any sense because no one else is connected to the server, hopefully someone can help me out here.

ACanadian
Avatar of Bembi
Bembi
Flag of Germany image

You should change the settings of Access to shared / multi user and lock by recordset.
Second, make sure, that you do not open the database in exclusive mode.
Third, take care of the lock behaviour of recordsets. Access will not unlock tables at once, idle time is used and if you make several simultanious queries, it may happed, that you blcok yourself out.
Make sure, that you have full access to the directoiry, where the database resides, otherwise, Access may not be able to delecte the lock file (*.ldb)

Another hint, it may be an idea, to link your tables as SQL Pass through query, not by static link within Access. The advantage is an increase of  performace between the factoer 20 - 100, second, you never have to relink all tables, if something  is changes within the table definitionson the SQL Server.
Hi  ACanadian,

Have you set a reference to DAO, I believe it is no longer a default in ac2002, I may be wrong, as I dont have 2002 yet.

Alan
Avatar of ACanadian
ACanadian

ASKER

Bembi:

Thanks for your prompt response,

The settings in access are setup as you mentioned.  The database is not being opened in exclusive mode. I have full access to the database directory.

Now I am curious about you comment about taking care of the lock behavior of recordsets.  How much time does access require to release its lock? Can I turn all locks off by default?

Also I am curious about your pass through query idea. It sounds great, but I am working with a large database (100+ tables) and if I create each table as a pass through query is that not going to result in a lot of coding changes?

Alanwarren:

Thanks for your comment,

When I first read your post I thought YES!, thats what it is, something simple, unfortunately DAO was already referenced. Thanks for the info though..

ACanadian
ASKER CERTIFIED SOLUTION
Avatar of Bembi
Bembi
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I am just about to give up on this.

I have download the latest MDAC and it has updated the version number of my ODBC SQL Driver to: 2000.81.9031.14

I tried trapping the error 3197. So when that error occured I would do a:  

DoEvents
dbEngine.idle
Resume

I let it run for 2 min, and the locks are not being removed.  The I tried turning off all locking in the database using advanced options, still no luck.

The problem has to do with my code accessing the same tables quickly and locking its self out. Because changes to records are always successful if there is just one command. (update, delete, insert)

Pass Through:
I haven't been able to test these properly because when I make a pass through query it doesn't remember the password to my sql server, and being prompted continually for a password is enough to drive anyone insane.

The options of putting my commands inside a transaction are out of the picture because this is a huge database and we don't want to modifying code, we shouldn't have to. It should be an easy data upgrade from MS Access 2000 to SQL 2000.

Yes making changes to the data, not the definitions.

Is there no way to get around this turmoil, arghh

ACanadian

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
alanwarren:

Actually it isn't an ADP. I considered using one except I am not sure how to move my queries from my access 2000 mdb to sql views.  There are hundreds of queries that use a value on a form as a parameter. And I really don't want to be recreating queries as views or changing code.  

The reference to DAO 3.6 is as high in list as access will let me put it. :)

So far what I have learnt about this problem:
Some of my code that makes changes to the data in the db isn't that smart. It works great for an access backend, but for some reason when using an sql db it locks its self out.  The error message is returned from dbEngine, but could this just be a problem with drivers?  Are there versions of SQL server drivers and odbc that access 2000 doesn't like?

Here is my exact setup in the hopes it may help.

Windows XP (All updates on Windows Update installed), Access 2000, SQL 2000
SQL ODBC DRIVER: version: 2000.81.9031.14
MDAC 2.7
Jet 4.0 Service Pack 7

I also have Visual Studio .Net Enterprise installed on the machine ( I plan on creating a better UI in .Net)

And what my plan is since this application is currently in production. I wanted to move the data into SQL. Keep the current app running, and as features are recreated in the .Net UI have the users work between two apps until everything has been recreated.  

I know what your thinking, just leave the data in access until the new UI is ready and the goto SQL. Well while I am creating the new UI it sure would be nice to use stored procedures to help speed things up. But it is a valid plan B. Though I haven't given up on plan A yet.

Well gurus, lets get this one beat. I am going to increase the points because I need to get this figured out ASAP.

ACanadian

Hi ACanadian

just did a search on Google for:

"The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time."

Nearly every link suggested database repair and recovery software. (347 results)

I wont post them here but it might be worth investigating.

Regards Alan
Hi ACanadian

Another thought:

When I upsized mdb's recently to SQL Server, encountered a couple of problems.

1... Identity seed property not set on key columns (autonumber in access)
2... Default value for bit fields needs to be set ... yes/no fields in access.

Once I fixed these things in Enterprise manager, had no further problems.


Regards Alan
This question has been abandoned and needs to be finalized.
  You can accept an answer, split the points, or get a refund (information at http:/help/closing.jsp)
  If you need a moderator to help you, post a question at Community Support (http:/Community_Support/)
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
nexusnation, cleanup volunteer
ACanadian,
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area for this question:

RECOMMENDATION: split points between Bembi http:#9040656 and alanwarren http:#9045670

Please leave any comments here within 7 days.

-- Please DO NOT accept this comment as an answer ! --

Thanks,

jadedata
EE Cleanup Volunteer