Sub report not working after relinking to SQL view

[Brian]

Hi,
I have a strange issue where the subreport of a report just does not show up
after my app does a relinking.

What I do is that the app checks a UDL file to see if the name of the server
changed since last it was run and if it did it recreate the links by
deleting the linked table and recreating it with a connect string like
..Connect = "ODBC;DRIVER={sql server};DATABASE=" & strDatabaseName &
";SERVER=" & strServerName & ";Uid=UserName;Pwd=password;APP=CUMME"

For linking to views I also recreate the unique index.

For this one report, the whole report is based on one view and the sub
report is based on a query which uses the same view. When I first manually
linked to the view the report worked fine.

If I change the UDL to point to a different server, and let the app relink,
the main report comes up but the sub report section is blank. Once or twice
I even got ODBC call failed.
If I open the subreport by itself it shows with data.
If I open the mainreport without the subreport it shows with data.
If I open mainreport with the subreport only the mainreport shows up.
If I open the linked view in access I see all the data.

If I change the UDL to point to original server and let the app relink you
get the same weird behaviour.

If I manually use relink table wizard to just refresh the link it still does
not work.

If I manually delete the link then use link table to link to the view the
report works fine.

Any ideas would be very much appreciated.

Thanks
Brian Morris

 

[Brian]

Some follow-up info.
I have found that if I use a file DSN to manually link it the supreport does
not work but with a System DSN it does work.
I'm going to the same server ,same user and password so I don't know the
difference.
Is is possible to create and link to a system dsn in code?
Thanks
Brian