Link to home
Start Free TrialLog in
Avatar of Merlin-Eng
Merlin-EngFlag for United Kingdom of Great Britain and Northern Ireland

asked on

ADODB problem

I'm converting an MSAccess project to work with a SQL Server back end rather than storing data in native MsAccess tables. Most of the code uses DAO to access the data and i'm re-writing it to use ADODB. So this is just a transitionary problem while the conversion is under way.....

I have one process which writes a record into a table, and then a second process needs to update that record immediately afterwards. it's not ideal, but for the time being each of these two processes has it's own ADODB connection. My problem is that when the second process executes, it seems that the first process is still holding the recordset open even though I have closed the Connection.  When the second process attempts to do a .Update, I get an error stating that the record cannot be found.

I need to know if there is a way that when the first process has terminated, I can flush the ADODB Connection and ensure it is properly purged and closed before I allow the second process to open it's ADODB connection.
ASKER CERTIFIED SOLUTION
Avatar of Anders Ebro (Microsoft MVP)
Anders Ebro (Microsoft MVP)
Flag of Denmark 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
You should not use Recordset to insert the record but a single INSERT command.
Same for UPDATEing of the record.
Avatar of Merlin-Eng

ASKER

It's complicated, but I'll try to explain..... Currently the data is partly in MS Access and partly in SQL Server. Some of the processes need to be able to open an ADODB.Connection to either of those. For this reason, i've given some of the processes their own ADODB.Connection variable. When everything is finished there will be just one Connection to the SQLServer and every process  will use it.

I'm converting from DAO to ADODB because the original code makes heavy use of DAO indexes which are unavailable to me on the SQL Server.
Ok, I am just curious, as I have not myself found many things that I need in ADO that is not in DAO. FileStream is one of them. I just wouldn't want you to go through a lot of trouble if a simple linked DAO table might solve your needs.


In order to troubleshoot your current issue, are you sure that its a timing issue? If you e.g. put a Sleep command of 2 seconds in between the 2 executions does it work properly?
Are you closing the connection or setting to nothing....?
I have one process which writes a record into a table, and then a second process needs to update that record immediately afterwards.
It would seem a better process would be to determine the UPDATEs needed before you write the record, and then include those updates in the insert (which, essentially, means you'd no longer need the update). I do this regularly with ERP utilities I create - downstream functions sometimes require me to update the inserted record, so instead of doing an insert >> update process, I do a process more like <determine what will change after the update> >> insert, and avoid the update.
Thanks for all the comments guys.....

@John Tsioumpris: I am closing the Connections and setting them to nothing before I move on and allow the next process to execute.

@scott McDaniel: I agree it would be ideal to determine the updates necessary and do everything at once. But these routines are used in multiple scenarios. It would be impossibly complicated to make them do everything in every scenario.

@Mate Farkas: I tried replacing my recordset with an insert statement. Same problem though.

@Anders Ebro: I put a 2 second delay between the two processes and the problem went away.
Well in that case a low-tech solution would be to retry until it succeeds.
Or you could go the "bulk" method. If your final insert + update is to the same datasource, then stage your insert and update statements in a table, then at the end cycle through the table and execute your statements in sequence. I've done this as well. It's kludgy, but it works.

Or you could continually retry the update until it succeeds.
There is also the possibility depending on the nature of the 2nd process  you could use an SQL trigger that will "work" after the 1st insert and do the work you are doing right now manually...
CREATE TRIGGER TRG_2ndProcess 
ON TABLEA
AFTER INSERT AS
BEGIN
INSERT INTO TABLEB (ID)
SELECT TOP 1 ID FROM TABLEA ORDER BY ID DESC
END
GO

Open in new window

@John Tsioumpris: As far as I am aware, this issue only relates to when I am writing to the MS Access backend. So i cannot use triggers.

@Scott McDaniel: The two routines in question are completely separate and unaware of each other. I would tie myself in knots trying to unify the updates in some way.

At the moment, I am trying to think of a way in which the two processes could actually use the same Connection. That would fix things.
I thought you had the issues on the SQL part...i think its better to use DAO for the Access operations
Yes, but as part of my conversion i'm re-writing the code in ADODB so it will run equally well in either environment. So as each table migrates to the Sql Server, I only have to change the ConnectString.
Changing just the connection string is not the best thing to do....SQL server has more functionality than Access as a core database engine...so just migrating to it won't give you the benefits you are expecting to have...SQL is tables/views/stored procedures/functions/triggers...you should change your perspective in order to get the best of both worlds...
Most of the code uses DAO to access the data and i'm re-writing it to use ADODB
I think you've made more work for yourself than you needed to.  I have several applications that are sold to the public where the user has the option of using an SQL Server BE or an ACE BE.  I designed the app to use client/server best practices.  The DAO code includes certain options that SQL Server requires but Access doesn't need but is OK with such as dbSeeChanges.  All the app does to switch between the two BE's is to delete the linked tables and link them in the new environment.  Since I have to actually delete the linked tables to do this switch, there is a table of tables in the app that controls the relinking part.

If the DAO code is using find and seek, you should change the code to use queries with select clauses instead.  It will be far more efficient in a SQL Server environment to use a query to get single records than to open a large recordset and read through it backward and forward trying to find a specific record.
@PatHarman: This is an application I began developing in 2003. At the time I only knew about DAO and Access tables. So yes, my application makes heavy use of indexes and  the .Seek method. We've decided that I will migrate the data a table at a time and fix the problems as they arise. I've already migrated 40 tables, and there are about 55 still to do.  Much of the code is in classes which need to access the data wherever it resides.  So a class will be used to access an Access .accdb file one minute, and then a moment later access data in the SQL Server. It's easy when everything is written in ADODB using Select statements.  I don't use linked tables much. I find it's much quicker to open a connection to the server. All my forms used data in cache tables which are updated back to the server when anything changes. My reports use DSN-less queries to get data from the SQL server.

I've realised now that it's not practical to use multiple connections. So i've spent the past day re-hashing my code to use just one connection to the SQL Server and one connection to the Access data, rather than each class instance owning it's own connection which could be pointed to either data source. it's working fine now.

Thank you everyone for your input. It was Anders Ebro who made the simple but correct suggestion to use a single connection. So i will award the points there.
As I said.  You did much more work than you needed to.  No classes are required.  It is simply a matter of using DAO against linked tables.  When you want to upsize a table, simply delete the Jet link and link to SQL Server.  

It would have been better to change the code to use queries with criteria rather than opening recordsets against tables and moving around looking for records.  You should still do that if you have code that is opening a cursor.  It is far more efficient to run a query that selects only the record(s) you want to work with.

It appears that there is really nothing "Access-Like" in your design so there is no point in changing direction now.  At least Anders solved your problem.

Just FYI, I can convert an app that I wrote from ACE to SQL Server (or any other RDBMS) in under an hour.  That's the power of Access when you take advantage of the RAD environment.  I began long ago, almost from the beginning, to design apps using good client/server techniques so conversions are a snap.  In most cases, it is simply the time to upsize the database.  Although, the last time I did a migration, I discovered a new difference that I'd never run into before.  A form was doing validation that included a field that had a default value.  The defaults were defined at the table level and Jet/ACE populate them immediately as soon as you dirty the record. However, SQL Server et al don't populate the defaults until the record is saved.  To resolve the problem, I moved the defaults to the form.
I think we are confusing things up....you just can't go from linked tables of Access BE to linked tables SQL...you are shooting yourself in the foot by taking this approach destroying every single chance of benefiting from the power of SQL...you will be much better with Access BE..trust me on this and if you have doubts just try to a link a table with a couple of million records against Access BE & SQL....probably SQL linked table will take ages or more likely timeout...
SQL BE is about pass through queries..cleverly designed ADO recordsets and stored procedures/views/functions...everything else is ....we just wanted to say to the rest of the world that we are using SQL as BE because its cool.
I beg to differ. SQL server also offers server side processing, the CHOICE of when to use a linked table and when to use a stored procedure, improved security, reliability, access control and backup.

For small database you can start of using SQL server as backend just as you would access. There are certain "nono" for Access with SQL server, just like there are certain nono for access with an acccess backend.
John, I've been accessing other RDBMS' using linked tables for over 20 years.  Occasionally I need a view or a pass-through query.  A couple of times I've used stored procedures for reports or big batch processes but for the most part, forms/reports are bound to queries of linked tables using criteria to limit the rows returned.

I have a number of apps with million row tables.  One reporting app had 25 million rows in the monthly transaction table which held 6 years worth of data.  Access actually does send queries to the server for processing so the server selects and summarizes and sends me back 96 rows.  One per month per year per department for the past two years.  No drama, no slowness. no non-Access technique required.  You just need to be aware of things that can cause Access to not pass-through your queries.

If I were going to code it all from scratch, I would probably not use Access as the FE.  Access is very old and MS won't update it so it looks stale and hasn't had a new control type in my memory.  It has only lost the extra ActiveX controls it used to have.  Plus there's the distribution issues and dll hell with dealing with different versions.  I use Access precisely because it is a RAD tool and I don't have to code it all myself.

I came to Access from a mainframe environment and had been writing code to do everything for myself for the prior 30 years (I've been doing this since before I could vote.  I was in the right place at the right time and the Travelers send me to programming school in 1968).  So, I've written my million lines of code and don't need the practice.  That is why I love Access.  It doesn't make me code the mundane, detail tasks.  I get to code the things that are above and beyond the basics.

I learned early on the power of update queries and why I should NEVER use a cursor if I had another alternative.