SQL, Access + VB

[EscapeUK]

I have a VB app (uses MTS) which saves data to a SQL DB with no problems at all. However when I run a long MS access Query against the DB and try to save using my VB app the VB app bombs out.

Seems to be a problem in running the MS Access (uses SQL ODBC connetion) and the VB app at the same time. I guess people must do this all the time.

Any ideas

 

[cdukes]

You might find that the long Access query is causing locks on one of the tables in the database (access has a habit of doing that). If thie is the case and VB is tring to write to one of the tables, it might well timeout.

Check
a) locks on the database when running the access query.
b) Error code in VB/MTS when doing the save.
c) Stepping through VB code and find where it bombs out.

Hope this helps,

Chris Dukes

 

[EscapeUK]

How do I check for locks

 

[EscapeUK]

I went to tools, options, advanced but it was set to no record locks

 

[EscapeUK]

If something is causing the table to be locked how do i over come this

 

[cdukes]

You need to check under SQLServer rather than access:

Under Enterprise Manager for SQL Server, Select the appropiate Database Server, Select Management - Current Activity - Lock/Object.

This should indicate what tables are locked and by who.

Process Info contains the spid of who is connected and form where. The spid will then be indicated in the locks view.

Chris Dukes

 

[EscapeUK]

Sadley this is very random so it is difficult to catch this.

Anyway of preventing locking?