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.

 

[KirkJewell]

I don't know the answer to your problem, but....

a, I dont believe that is it possible to compact the database while it is open.

b, I don't think a fragmented database would create an out of memory error.

However...

When a function or sub closes it is supposed to release all the local variable memory back to windows. I always put the following at the end of subs or funcs:-

set rs = nothing
set frm = nothing
set etc ......

Just to make sure. You might also want to do the same with any links that are redundant.

Are you calling functions or subs recursively (calling a function from itself - which loops back through previous calls as the functions exits each time). This can cause out of memory errors becuase it makes a new set of variables each call?

I hope this helps!

Regards
Kirk.

 

[MichaelRed]

Slightly off topic, but why do you/would you delete-recreate links? Cant you just leave them in place?


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

 

[MartinF]

Hi KirkJewell,

Thanks for the feedback, i am relatively new to the programming side so i will re-look at my code bearing in mind the suggestions you have raised.

Thanks again.

 

[MartinF]

Hi MichaelRed,

Unfortuately due to the amount of data we use the data has too be split acorss 8 backend's so as to make the database run at a usable speed. So i either have 8 identical front ends or use the one front end which can link upto any backend that is selected of a menu by deleting one set of links and then linking in to another. This is a bit of a nightmare but it seems the best way of doing it at the moment.

 

[MartinF]

Hi KirkJewell,

Further to your suggestions, does it make a difference where i declare my variables?

I tend to declare them at the top of the modules after the Option statements, is this good practise? or is it better to declare them at the start of each Function?

 

[MichaelRed]

Martin,

I would persue the issue of asuring that the links/connections to the back-ends are properly 'closed'. Without an 'exhaustive' check of the app code, I do not think anyone can really be assured they have pin-pointed the problem. However, failure to destroy 'objects' which are switched can easily be a source of out of memory errors.

On a different slant, if Ms. Access is slowing down due to the volume of data necessary, it is time to switch to one of the 'Industrial Strength' database engines. I know this is not going to be popular w/ the 'Boss', as these will cost more than Ms. Access - on the other hand, the cost of failure is usually highgr than hte cost of (simple) tools.

As an iterim soloution, could you re-start the front end with a selection set of the various back ends (Option group on startup) instead of deleting/creating the various links?


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

 

[MartinF]

Hi MichaelRed,

Thanks for your feedback,

On the subject of ensuring that the links are properly closed, how do i go about ensuring this? the commands i am using are...

DoCmd.DeleteObject acTable, "Table Name"

DoCmd.TransferDatabase acLink, "Microsoft Access", path, etc, etc

...to first remove the existing link and then re-create another link to a different backend.

Both these commands are passed in the same function. Is this sufficient or do i need to add anything else here?


As far as 'Industrial Strength' databases and the cost of failure i agree with you entirely, and i am sure it would resolve all of my problems, but unfortunately i dont make the decisions...


An option group on start up could be a solution, but the nature of the application means that switiching between backend connections happens quite often, so the users would have to keep restarting the database every time they want to switch. At present everytime they open the database it resumes with the connection they left it on.


Did you see my eariler question in this thread about where is best to decalre variables? I have had no feedback on this as yet, What do you think of this?

Thanks again.

 

[chell]

Hello Martin;

I've chased this problem down before and like you I deleted the table and then used transferdatabase; however I later found out the easier method - the RefreshLink method. Here is a reprint from the Access 97 help file:

Updates the connection information for a linked table (Microsoft Jet workspaces only).

Syntax

tabledef.RefreshLink

The tabledef placeholder specifies the TableDef object representing the linked table whose connection information you want to update.

Remarks

->->->->To change the connection information for a linked table, reset the Connect property of the corresponding TableDef object and then use the RefreshLink method to update the information. Using RefreshLink method doesn't change the linked table's properties and Relation objects.

For this connection information to exist in all collections associated with the TableDef object that represents the linked table, you must use the Refresh method on each collection.

 

[MartinF]

Hello Chell,

Thanks for your feedback, you obviously know a lot about this very frustrating problem.

I'm now in the process of trawling through the help files pursuing this method.

I would be grateful if you could explain to me a bit more about the last comment regarding the connection information exist in all collections. I've never used the TableDef objects or associated commands before so i'm a bit in the dark here and any extra help would be gratly appreciated.

Many thanks.

 

[chell]

Hello Martin;

I've ignored that last part about the "refresh on all collections" and have had no problems so far (knock on wood). However if I were to hazard a guess, I think the following would apply:

Suppose you had an open recordset based on table X. You then applied the refreshlink method on the tabledef object of table X. This could invalidate the fields collection inside of the recordset and so you would have to apply the refresh method (say MyRecordset.fields.refresh) before accessing that collection or you might get a run-time error.

In my application the above scenario never applies.

Here is a code snippet from one of my apps using the refreshlink method:

'Switch the links for the data tables from
'testing tables to live tables.
Sub SetForRelease()
Dim TBL As TableDef
Dim Db As Database

'Note that I do not say "set tbl = currentdb.tabledefs.."
'This seems to be important for some reason.
Set Db = CurrentDb
Set TBL = Db.TableDefs("Weekly Timecard Submittal&quot
TBL.Connect = ";DATABASE=\\Pro1\TimeCardSubmittal.mdb;"
TBL.RefreshLink
End Sub


Hope It Helps
Chell

 

[Durkin]

Here is a loop I have used many times to relink databases. It is important to use the .Attributes property or else it will start to relink system tables and local tables. What I also usually do is record what the orginal connection string was and if there is an error along the way, run a loop in the error handler to reset the table to the way they were originally.

dim TDs as TableDef
dim strConnect as string


strConnect = ";DATABASE=\\Server\Folder\DB.mdb"

For Each TDs In CurrentDb.TableDefs
If TDs.Attributes = dbAttachedTable
TDs.Connect = strConnect
TDs.RefreshLink
End If
Next TDs
Durkin
alandurkin@bigpond.com

 

[MartinF]

Hi,

Thanks for all the help in this area, i now have my database relinking tables this way, but unfortunately i still get the 'Out of memory' errors.

Any more suggestions?

 

[MichaelRed]

Martin,

You may need to review some of the system/setup parameters. You noted that the original reason for this arraggement (re-linking tables) was the size of the data set(s). Is it possible that these continue to grow and you really are running out of memory? Is your disc getting full or so fragmented that virtual memory is not able to handle the process? Does the out-of-memory happen on a specific sub-set of the data? Does it happen after X number of changes?



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

 

[MartinF]

Hi MichaelRed,

Thanks for the feedback,
The backends themselves as stand alone items only have a few thousand records each, which may not seem that big. The reason they cannot be all put together is that there is a process that compiles the data from these into another table of around 17-20,000 records. The process runs 1st time ok, but if you run it a second time without quiting and restarting the database it crashes with the out of memory error - Its always on the second time, so this makes me think that there is something holding up memory, but i just cannot seem to work out what it is, because apart from this process the database does little else. The process itself just opens up various recordsets and runs SQL statements to append records from on table to another, i have checked to ensure that these recordsets are closed properly afterwards, but still the problem persists.

 

[MichaelRed]

O.K., have you made sure that the ' ... another table of 17-20,000 records ... ' is cleared out before the second 'run'? This would be a prime suspect in my mind.

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

 

[MartinF]

Hi,

Yes, the first thing that the process does is empties the table before it is re-compiled.

 

[MichaelRed]

Have you VERIFIED this? Do a debug after the delete records, and get the record count of the (empty) table?

Do you create 'temporary' recordsets on the way to the new one? Can these remain in memory (do you set them to 'Nothing' somewhere)?


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

 

[MartinF]

Hi,

By checking that the table is empty using the debug window, do you mean opening up a temporary recordset and printing the .recordcount in the window? or is there a quicker way?

The process does open up temporary recordsets, but i always close them when done, does this have the same effect as setting them to nothing or is that slightly different?

 

[MichaelRed]

I did not intend to specify how you got the record count, just to verify that it is ZERO after the table is cleared. The method mentioned is probably what I would do, as it is really only meant to confirn the deletion did what is should.

"Close" the record set should be O.K., however it is better to set it to "Nothing".



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