Updating a form after inking to a new backend database

[daveinuk]

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

[g4tv4life]

Showing us the code will be beneficial. I'd try both me.refresh and me.requery statements at the end of your code and see if that does the trick.

[daveinuk]

Hi G4tv4life,

I already tried those options and nothing happens.

Here is the code:

For Each Tdf In dbs.TableDefs
'
' if local then skip
If Len(Tdf.Connect) <> 0 Then
'
' The table is linked so check for a change
If Right(Tdf.Connect, Len(Tdf.Connect) - InStr(1, Tdf.Connect, "DATABASE=") - 8) <> strFilename Then
'
' Different link, so setup required link
Tdf.Connect = "MS Access;PWD=*************;DATABASE=" & strFilename
Tdf.RefreshLink
End If
End If
'
Next


Thanks
Dave