Linked tables in VB 3

[MartinF]

Hi,

I am currently running a database that continually deletes and re-creates links to tables in other databases. Whilst i have this working fine, the problem i occasionaly get is that after a while i get 'Out of Memmory' messages and the whole thing crashes.

Is this because when table links are deleted and re-created the database fragments?, and if so can i resolve the problem by getting the database to compact its self every so often?, and if so how do i program this in visual basic (bearing in mind that the database will have to remain open during this proccess)

Any help would be greatly appreiciated.

 

[MartinF]

Hi MichaelRed,

I have now confirmed that the table record count is zero after it is cleared, and i have set the recordsets to 'nothing' at the end of the module, however the problem still persists.

One thing i have now noticed thought is that it always crashes on a 'docmd.runSQL' statement but not always the same one. Unless it is something simple like clearing a table i always build my SQL sentence up into a string variable before passing it into the 'runsql' command.

In this module the main bulk of the SQL string variables are huge and take probably around 2 complete line lengths to put toghether as they contain a few variables. Do you thing this could cointribute to the problem?

 

[MichaelRed]

Martin,

This doesn't seem likely, as the sql string can be pretty large - like 1KB. It could be that you are putting stuff into the string which is not apparent - like concatenating some of the variables instead of replacing them. You could check this quite easily, just put a breakpoint on the runsql command and look at the SQL string in the debug window. It is somewaht of a pain, as you either neeed to "pan" across it, or manually divie it into 'manageable' strings. It does give you a way to review/check the SQL. One of the things I often do with these is to execute the statement, and when/if it fails, simplify if and re-run it untill it 'works'. Then re-introduce parts untill I get it 'right'. Can be time consuming - but better that just watching the error re-occur.

Also, check the limit on the SQL statement length and compare the actual length.

Another option would be to further restrict the recordset (add some more where clauses ...) and see if this helps -- it is still possible that you are really running out of memory.

Reun an additional copy of your app. in parallel with the original. Do the query/build table thinngy in the first, then do tehsame in the second copy. If you don't gett he out of mem err on the second, you can be sure there is a problem in the app/process of releasing something.

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[MartinF]

Hi Michael,

I checked the SQl statements as they ran and they all looked fine, even the on eit crashed on. Unfortunately it is not possible to restrict the recordset any more than it is already.

I made a copy of the application, strated the first one off, then started the second one off. The first one then crashed with a runtime error code 3000 and it said that there was no error message for this error! Whilst the second one completed its process.

 

[MichaelRed]

Martin,

You are reaching the limit of my ability to offer useful help. One more thought would be to take the SQL statement(s) at the same break points noted earlier and copy/paste them into the SQL view of the query builder. Swith to design view. If the query builder will go there, at least the syntax is not illegal. Then, try to run the query from the query builder. It may give a more informative error.

W/ respect to the multiple execution, I really meant to just start a second copy of the same DB, although making a copy of the DB should accomplish the same thing. It is somewhat strange that the first copy would fault after starting the copy. You might attempt to trace through the first copy to see why it is crashing (more specifically than run-time error 3000). Crashing a copy of a database app when multiple users (initally) attempt to run the database usually involves a sharing violation - however if these are seperate (COPY) db's, then this would need to be a shared resource (linked table?) which is open in exclusive mode.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[MartinF]

Michael,

The SQl statement was origianlly put together in the query builder window, and it doesn't always crash on the same one, so i think the syntax should be ok, althought i will check it like you suggessted to be double sure.

I had to make a copy of the DB as if i had started a second copy of the original Db off they both would have crashed pretty much straight away as they would both be altering the same link information at the same time and end up all over the place. The table that the records are appended into are in another database, so i had even made a second copy of the other database, so that the front end copy wasnt trying to update the same tables as the original copy.

One thing that was strange though was that when i deleted the copies of the databases and re-ran the original it crashed again with the same unknown run-time error, but it now runs ok after i did a repair on it.

 

[MichaelRed]

So actually you have three dbs. The one you are 'running', the source (which is variable), and the destination?

I should have rembered to advise you to 'repair' the db as soon as you mentioned the (changing) Links.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[MartinF]

Michael,

Yes, in effect there are three db's, although the destination has just a few tables in it and nothing else. Do you think that this is the reason?

When you say repair the db as soon as the links change, do you mean that each time link information is changed the db should be repaired?

 

[MichaelRed]

NO, just if there is an error during the process which changes the Link(s). You probably should have this function isolated in a seperate procedure with robust error trapping/handling. This could let the User know there was a problem and 'repair' the database automatically. You would need to let the Users know how this would affect them, either via extensive messages or by an 'education' program.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[MartinF]

HI Michael,

I think i've just cracked it!
The append SQL statements appended records with one of the variable's being based on a list of names, however not all names appeared in each database that it linked to, so i've filtered out the list of names to reflect the current database connection, and it refreshes this list each time it makes a new connection, so now when the SQL statements execute, they ALWAYS have data to append, whereas before they would frequently try to append no records.

Its now on its third consecutive execution without crashing.

Anyway, just like to say a big thanks for all the time you've spent helping me on this one, it has been truly appreciated and i've definately learnt a few things on the way!

Cheers