difference linking view with file dsn and system dsn

difference linking view with file dsn and system dsn

Post by Bria » Fri, 07 Sep 2007 12:10:01


Hi,
(This message was posted in another group before I found this one.)

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.

I have then found that if instead of using a file dsn instead of a system
dsn I get the same problem so I am thinking that the syntax of the system
dsn has some info that the file dsn does not?

Any ideas would be very much appreciated.

Thanks
Brian Morris
 
 
 

difference linking view with file dsn and system dsn

Post by Gary Walte » Fri, 07 Sep 2007 19:07:00

Hi Brian,

There are so many tangents involved here
so I could be completely off base...

Identify the fields you use to link report to
subreport (Master/Child fields).

If you open the View in both DSN and DSN-less
connections, is there a difference in "padding" for
any of the fields you identified above?

When you set up DSN, you check/uncheck 2 boxes

Use ANSI quoted identifiers
Use ANSI Nulls, Padding and Warnings

however you check/uncheck those boxes, try
adding those settings to your DSN-less connection
string.

Or...try basing your recordsources of report and
subreport on queries that TRIM any text Master/Child
fields.

Like I said, I could be way off base. It is just that I
had a complicated report/subreport once where the
subreport started showing up blank and t *** the
Master/Child text fields solved it.

good luck,

gary

 
 
 

difference linking view with file dsn and system dsn

Post by Gary Walte » Fri, 07 Sep 2007 20:48:17

n further thought...

Obviously, you cannot add "DSN settings"
to a DNS-less connection (so please ignore that
erroneous advice).

But, I would still try the TRIM hypothesis.

"Gary Walter" wrote:


 
 
 

difference linking view with file dsn and system dsn

Post by Bria » Fri, 07 Sep 2007 21:55:24

ary,
Thanks for the thoughts but the report worked fine when it was based
directly on the linked tables. I wanted some speed and did the access query
as a view and linked to that instead so without actually setting those
options I should have the same settings for the view as I did with the
tables, yes?
I cannot see any difference in the padding.
Brian

"Gary Walter" < XXXX@XXXXX.COM > wrote in message
news:% XXXX@XXXXX.COM ...


 
 
 

difference linking view with file dsn and system dsn

Post by Gary Walte » Sat, 08 Sep 2007 01:37:53

i Brian,

What I thought I heard you saying:

- it works if I use DSN

- the subreport is blank in main report
if I use DNS-less connection, but the
subreport works by itself

that seemed to suggest one or more Master/Child
linking fields are "different" between different connections

the only other "difference" I might guess is you turned
off Subdatasheets when you manually link
with DNS?

you are welcome to ignore my speculations...

sorry to bother you,

gary

"Brian" wrote:


 
 
 

difference linking view with file dsn and system dsn

Post by davi » Sat, 08 Sep 2007 20:24:13

If I understand correctly, you have a query linked to a view?

And you have refreshed your table links, but not refreshed
your querydef?

The linked table manager only manages linked tables.

Most sample table re-link code only re-links tables, but it is
not hard to add a querydef loop to the tabledef loop.

(david)




up
server
manually
relink,
twice
does
 
 
 

difference linking view with file dsn and system dsn

Post by Bria » Wed, 12 Sep 2007 04:50:47

es you heard correctly, as strange as it sounds.
Where would I change the Subdatasheets setting?
To generate the problem I can just change the .connect and .refreshlink to
the linked view and I can go from working to not working without doing
anything else.
Bother? Let's just say you are the only one with any ideas so far so I would
hardly consider you bothering me.
I have found a way to create userDSNs on the fly and if I use that the
report works. I had stopped using DSNs years ago but it looks like I'll
have to start back using them. This problem is very scary since it is more
or less something simple that I would not have thought twice about doing and
there is no explanation why it does not work. It is just coincidence that I
never used a linked view for a subreport.

Thanks
Brian

"Gary Walter" < XXXX@XXXXX.COM > wrote in message
news:% XXXX@XXXXX.COM ...


 
 
 

difference linking view with file dsn and system dsn

Post by Bria » Wed, 12 Sep 2007 04:55:39

David,
Why would I have to refresh the querydef?
When I change the source of my tables I have never needed to refresh the
querydefs for my queries (based on tables) to work. It also would not
explain why the subreport works when I relink using system or userDSNs
instead of DSN-less.
Thanks
Brian Morris
 
 
 

difference linking view with file dsn and system dsn

Post by davi » Sat, 15 Sep 2007 23:35:18

nly if you have directly linked a query.

(david)

"Brian" <s@y> wrote in message
news:efx5TS% XXXX@XXXXX.COM ...
show
the
system
system