Lost all joins

[Guest]

I made a copy of a database which is made up of a front and back end.
The backend data doesn't change. Then in the copy version of the database, I
relinked all tables. But when i checked the queries all joins between tables
are gone and i have to recreate all those links for the queries to run.
Is this normal in Access 2003?
If there a way to recreate all those joins automatically (it would be a huge
amount of work of recreate all of them manually).
Help

 

[Michel Walsh]

Hi,


Should not be like that, definitively, but if it does somehow happened, have
you tried to IMPORT the queries from the original back end, instead to
re-write all the queries?


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

1 - Create a table tblLinkTables with the columns LinkTableName and LinkPath
(example shown below). You can also put this in an Excel workbook and save
it in the same directory as the Access application (tblLinktables.Xls). If
you choose this option, uncomment the section of code below that imports this
file.

tblLinkTables
LinkTableName LinkPath
tblAccounts \\server\dir1\dir2\dir3\LinkMdb1.Mdb
tblAcctType \\server\dir1\dir2\dir3\LinkMdb1.Mdb
tblAdjustment \\server\dir1\dir2\dir3\LinkMdb2.Mdb
tblAvailView \\server\dir1\dir2\dir3\LinkMdb3.Mdb
tblCCY \\server\dir1\dir2\dir3\LinkMdb4.Mdb

2 - Create a public subroutine RelinkAllTables and enter the code below
Public Sub RelinkAllTables
' Declarations
Dim sSrcFile As String
Dim db As Database
Dim rst As Recordset
Dim lMsgResp As Long

On Error GoTo RelinkAllLinkTables_Err

DoCmd.SetWarnings False
'DoCmd.RunSQL ("DELETE * FROM tblLinkTables")
'DoCmd.SetWarnings True
' Import the data
'sSrcFile = AppPath & "\" & "tblLinkTables.xls"
'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7,
"tblLinkTables", sSrcFile, True

' Go through the data and relink all tables.
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT * FROM tblLinkTables", dbOpenDynaset)
rst.MoveLast: rst.MoveFirst
If rst.RecordCount = 0 Then
lMsgResp = MsgBox("There are no records in tblLinkTables",
vbCritical + vbOKOnly)
Else
rst.MoveFirst
While Not rst.EOF
If TableExists(rst.Fields("LinkTableName").Value) Then
DoCmd.DeleteObject acTable, rst.Fields("LinkTableName").Value
DoCmd.TransferDatabase acLink, "Microsoft Access",
rst.Fields("LinkPath").Value, acTable, _
rst.Fields("LinkTableName").Value,
rst.Fields("LinkTableName").Value
rst.MoveNext
Wend
End If
RelinkAllLinkTables_Exit:
Set rst = Nothing
Set db = Nothing
Exit Sub
RelinkAllLinkTables_Err:
MsgBox "Error #: " & Err.Number & vbLf & _
"Error Description: " & Err.Description, vbOKOnly + vbExclamation,
"RelinkAllLinkTables"
Resume RelinkAllLinkTables_Exit
End Sub


3 - Run this subroutine. It will relink all of the tables you have
specified in tblLinkTables.

 

[Guest]

Hi Michel, thank you. I had tried it and it doesn't work.

Hi Steve, thank you for your input. The tables are still linked (i checked
the relationship screen), it is just the joins in all the queries that are
gone. Plus all the fieldname in the field pane of the query design have been
replaced by exp1, exp2, etc... followed by : and then the fieldname.
So not only do i have to redo all the joints but i need to go to each
fieldname to erase the first part so the fieldname shows.



Steve, i will keep your code and try and figure it out so i can customize it
for other databases

 

[Gary Walter]

Hi Sylvie,

PMFBI

I'm still running AccessXP so do not know
for sure if following is important for 2003...

try this:

Start a new db for front end.

Turn off Autocorrect in Options.

Import only the tables.

Relink to new backend.

Now, import everything else.

Apologies again for butting in,

gary

 

[Guest]

Not butting in. All advice is always welcome.
I turned offthe autocorrect option and also in the ImportObjects Screen, i
went into the options screen and selected them all. And it WORKED.
It was simple -once you know it.

Thank you

 

[Tom Ellison]

See, Gary! You're not butting in if you're right!

Tom