Linking Front end to backend db (1 Viewer)

[robess01]

I have a front end DB. For various reasons I need to create a new database but apart from removing the data I want all the fields etc to be the same. To do this I went into the back end deleted all the data from the tables and then saved it as a new copy. I have then taken a copy of the front end (FE) and then tried to link it to the new BE by going to external data, selecting the new BE and then re linking the tables. I note however when I do this it offers both the new and old tables paths. When I select the new table paths (by clicking them and not the old ones) however and relink the FE to the new BE, when you run the FE it never points to the new BE always the old. I thought doing this was a simple exercise, must be missing something. Anyone help me out. Access 2016.

 

[Minty]

Delete all the linked tables from the FE. Compact and repair the FE.
Now reopen the FE and relink all of the tables from the new BE.

 

[CJ_London]

To do this I went into the back end deleted all the data from the tables and then saved it as a new copy.

better and safer to create a new db, then import the tables from the old back end ensuring you select options>definition only

 

[Cronk]

Agree with CJ. No problem with any potential enforced data integrity issues if table data is deleted in wrong order.

 

[Pat Hartman]

Relationships are enforced ONLY in the BE so swapping one BE for another has nothing to do with anything.

If this is something you need to do for yourself but users will not have to do it, open the linked tables manager and check the box that says to prompt for new BE. Then select all the currently linked tables and press the relink button. Access will point the links to the new BE.

If this is something that users need to do, then you need to make a form to handle it. I have posted an example that does this several times. If you can't find it, just ask and I'll post it again.

 

[robess01]

Delete all the linked tables from the FE. Compact and repair the FE.
Now reopen the FE and relink all of the tables from the new BE.

Thank you - did this database along time ago. Tried your suggestion with the FE but when I try and compact etc I get an

DoCmd.SetWarnings False
If Me.txtPswd = Me.pwdhide Then
varEmployID = Me.txtEmployID
varUserID = Me.txtEmployID
DoCmd.RunSQL ("Delete from tempUser")
DoCmd.RunSQL ("Insert into tempUser Select * from tblEmployees Where EmployeeID=" & varEmployID)
DoCmd.Close
DoCmd.OpenForm "frmMain"

and from there I am stuck.

 

[robess01]

Relationships are enforced ONLY in the BE so swapping one BE for another has nothing to do with anything.

If this is something you need to do for yourself but users will not have to do it, open the linked tables manager and check the box that says to prompt for new BE. Then select all the currently linked tables and press the relink button. Access will point the links to the new BE.

If this is something that users need to do, then you need to make a form to handle it. I have posted an example that does this several times. If you can't find it, just ask and I'll post it again.

Ok thank you - I think this is it. Working on it, looks good so far.