Update open form after linking to a new backend database (1 Viewer)

[daveinhull]

Hi,

Hope someone can help.

I have an Access DB with a form that allows the user to select a new backend database. I can connect to the backend and then .refreshlinks but nothing on the form is updated. I have tried requiring the form but that doesn't do anything. I've tried loads of other things, refresh, recalc etc., but nothing updates the open form.

The only way I have managed to get it to work is to close the form and reopen it, then it shows the data from the newly linked backend database.

While it works, it doesn't look good but also there seems to be some problem with it because eventually it reports an error saying "cannot open any more databases" and when clicking OK comes back with "An error occurred while sending data to the OLE server (the application used to create the object" and a whole bunch of other messages.

I think it might have something to do with the fact that the form has a number of MS graphs open on it, but I'm not sure. Also, I can't track down a particular line of VBA code which causes this error.


Anybody got any thoughts on this and particular how to update a form after changing the backend database.

Many thanks in advance
Dave

 

[JHB]

What about setting the form's recordsource to nothing before connecting to the "new" database and after relinking set the form's recordsource again, (to what it was before + maybe a form requery)?

 

[daveinhull]

Hi JHB,

Thanks for the reply. I've tried just reloading the .recordsource after the .refreshlink but nothing happened.

However. I'll give your idea of clearing it first and restoring it after the .refreshlinks and see what happens.

Thanks
Dave

 

[JHB]

However. I'll give your idea of clearing it first and restoring it after the .refreshlinks and see what happens.

Thanks
Dave

Do that - and give some feedback if it helps or not!

 

[daveinhull]

Hi JHB,

Ok, I tried your idea and resetting the record source for the main form updated certain things on the form but not a particular list and a subform. The list updated once I used list30.recalc and the subform updated once I reloaded the .sourceobject for the subform.

Because there could be multiple controls and subforms on the main form I eventually wrote

    For Each myForm In Application.Forms
      TempHold = myForm.RecordSource
      myForm.RecordSource = TempHold
      myForm.Recalc
      For Each myControl In myForm.Controls
         If TypeName(myControl) = "SubForm" Then
            TempHold = myControl.SourceObject
            myControl.SourceObject = TempHold
         End If
      Next
   Next myForm

It seems to work and I don't now need to close and reopen the main form.

But why doesn't it do this if I just requery the main form?

Thanks for the idea
Dave

 

[gemma-the-husky]

if the form is already open, you would need to requery the form.

if you are not seeing the data you should, then I would check that the relink succeeded, and that you actually ARE looking at the new tables.

 

[JHB]

...
But why doesn't it do this if I just requery the main form?
...

It could be that your code runs faster as the new data is ready, try to put in a break point in your code in the line before you do the requery for the form, wait a few seconds and then continue.
If you then get the new data, I would put in a DoEvents in your code before the requery, maybe build a loop with a DoEvents in, checking if connection string for the datasource is set to the new data.

 

[daveinhull]

Hi JHB, gemma-the-husky,

gemma-the-husky, I tried all forms of .requery, .refresh, .recalc etc. and none of them worked. I've stepped through, checked error messages and I've also been into the hidden Access tables and found that the reference connect links do actually get changed for the linked tables

JHB, I do think there might be something in what you are saying about the code running too fast. I'm sure that in the past while I've been trying to find the problem, it has worked on certain occasions which may or may not have been linked to me single stepping through the code - but while debugging my mind was on the hard problem, not the secondary effects of single stepping.

I'll give something a go and see what happens. Do you think just checking the .connect string would be the right thing to do or actually go into the hidden Access tables (MsysObjects) and actually checking to see if this table connection string is/has been updated? I.e. when I set it through the tdf.Connect does it actually go away and do things which could take time, which I suspect it does rather than just setting a table entry string.

Thanks for help.
Dave

 

[daveinhull]

Hi,

Ok, so I put a delay (5 seconds) after relinking the tables before doing a requery and nothing happened, no form update.

I moved the delay in between the relinking the table and the actual .refreshlinks and still nothing.

Good idea but it doesn't seem to address the problem.

My other approach works but it does take slightly longer than just closing and opening the form. I wonder what is happening on opening a form over just requerying a form?

Dave

 

[daveinhull]

Hi Again,

Now I'm confused. I changed the code from previous to this where all I do is recordsource = recordsource and SourceObject = SourceObject and it still works.

    For Each myForm In Application.Forms
      myForm.RecordSource = myForm.RecordSource 
       myForm.Recalc
      For Each myControl In myForm.Controls
         If TypeName(myControl) = "SubForm" Then
            myControl.SourceObject = myControl.SourceObject
         End If
      Next
   Next myForm

What is actually happening? There must be some processing going on when I assign the .RecordSource and .SourceObject.

Dave

 

[gemma-the-husky]

I would do this

msgbox "Backend before relink: " & mid(currentdb.tabledefs("sometable").connect,11)

relink

msgbox "Backend after relink: " & mid(currentdb.tabledefs("sometable").connect,11)


and see what is REALLY going on

 

[daveinhull]

Hi gemma-the-husky (Dave),

I did what you suggested and got what I expected.

The first message box contained the initial database table name and the second message box contained the new database table name, i.e. the one I change the link to.

As I said I have singled stepped through the code and I have checked in the hidden Access table (MsysObjects) which I believe holds this connect information and have seen it actually change during the single stepping.

I know the connection is changing because when I closed the form and re-opened it, it opened with the new database, the one I changed the link to.

Thanks for your help, but I'm still left with either closing and opening the form (messy and just not right) or reloading the form .RecordSource and subform .SourceObject fields (works OK).

Dave

 

[gemma-the-husky]

actually, on reflection, it probably does makes sense

closing and reopening.

the form is currently bound to a record source that no longer exists. requery probably IS going to fail.

 

[CJ_London]

I agree - you do need to refresh the recordsource - I use

tmpStr=me.recordsource
me.recordsource=""
...relink table(s)...
me.recordsource=tmpStr

Although if it is a subform I simply reset the sourceobject

tmpStr=me.subfrom.sourceobject
me.subfrom.sourceobject=""
...relink table(s)...
me.subfrom.sourceobject=tmpStr

 

[JHB]

Ok, so I put a delay (5 seconds) after relinking the tables before doing a requery and nothing happened, no form update.

I don't think a delay is the right way to do it, you need to give the control back to the system to finish the job and this is done with a DoEvents.
From the Help file - DoEvents function yields execution so that the operating system can process other events.

 

[daveinhull]

Hi gemma-the-husky, CJ_London, MHB,

Thanks for your comments.

CJ_London, I actually found that you just need to put:

form.recordsource = form.recordsource 
 subform.sourceobject = subform.sourceobject

just after doing the relinking and it works - see code above. You don't even need to put it into a temp variable. I initially thought that the compiler might optimise this out but of course its not actually being fully compiled. It also works when an .accde (.mde) file is created.

MHB, yes of course that makes sense. I'll ad a DoEvents loop in and see if that makes any difference.

Thanks
Dave

 

[daveinhull]

Hi MHB,

Ok, so I put in a loop containing a DoEvents between the .connect= and the .refreshlinks and also a similar loop after the linking and before a .requery but still nothing.

Just to be sure I went back and actually put in a breakpoint and slowed everything down.

Still nothing, although I do have a solution that doesn't require closing and opening the form.

Many thanks for your help.

Dave

 

[gemma-the-husky]

again, on reflection - it really would be unusual to relink a different backend while forms are open. normally, you would relink the tables as part of the opening procedure.

in some respects, it's a wonder that you don't get "access stopped working!"

 

[JHB]

Still nothing, although I do have a solution that doesn't require closing and opening the form.

Then you should stick to that solution, why fiddle around with something that doesn't work.
Good luck with you project.

 

[daveinhull]

Hi,

Ok, so I moved the code to a more complicated form and it failed. I now realise that if I have anything in the main form OnOpen or OnLoad or on...anything for that matter it will not work.

So, full circle back to closing and opening the form :banghead:

Thanks for everyone's ideas.

Dave