Unable to use UDL file in data environment

#1 05-02-2002, 06:18 AM

When I use a udl file as the ConnectionSource of a DEConnection, I get the following error : "Failed getting Rowset(s) from current data source"
The DEConnection won't accept my udl anywhere, no matter what I put in my udl and no matter where I specify the connection string.

I have altered the udl file numerous times, first I used the Jet engine, then ODBC, File DSN, SYSTEM DSN, nothing worked, I have made sure that the UDL is consistent with the ODBC DSN, but it just doesn't accept it.
It works fine when I use ODBC but I would like to know how to use a UDL since it is simpler and more advanced.

Hope someone can help,

Shaul

Thinker · #2 05-02-2002, 07:25 AM

When you created the UDL, you did click the Test Connection
button? If you open the dataview window, can you see anything
under your connection in the Data Environment Connections
group?

pisoftwar · #3 05-02-2002, 07:38 AM

What I have done in the past is reverse the process.
Build the connection string using the data environment then copy and paste it into the udl.

You will note that there is some differences between creating a udl (let say on the desktop) to the string that the data environment uses to the same data source.

Also be aware that the connection cannot be changed after it is open and if you have a form bound to the data environment open then that connection is in use, if you close the connection and re open it don't expect the bound control to respond to the new connection with out rebinding.

I know I have left out some steps here like "recordsets"

but I am just trying to give a brief overview.

#4 05-02-2002, 08:10 AM

>What I have done in the past is reverse the process.
>Build the connection string using the data environment then >copy and paste it into the udl.

Tried it, doesn't work.

>When you created the UDL, you did click the Test Connection
>button? If you open the dataview window, can you see >anything under your connection in the Data Environment >Connections group?

Test Connection succeeds only when I use the Jet provider, it doesn't work with ODBC.

I have tried all possible combinations of altering between providers, DSNs, etc.

I think that the DE just doesn't recognize UDL files.

Thinker: you seem to got it working the normal way, how did you do it?

Cheers,

Shaul

pisoftwar · #5 05-02-2002, 08:23 AM

You are using the "file=" in the property right?
FILE=<Path>\<UDL FileName>

#6 05-02-2002, 08:34 AM

I put my udl file at C:\udlfile.udl and this is the path I am using.

pisoftwar · #7 05-02-2002, 08:56 AM

the connection string has to have "FILE=" for it to read a udl

#8 05-02-2002, 09:29 AM

pisoftwar

>the connection string has to have "FILE=" for it to read a udl

I'm such a moron, I put FILE NAME= instead of just file.

Now that I changed it, When I run the report I get a dialog box asking me to choose an ODBC data source (either file or machine)
When I choose the file DSN I created everything works but I obviously want just the UDL to be used.
I tried fiddling aroung with the DEConnection and when I put the Jet provider string in the connectionString I got a similar prompt from the Jet provider asking me to put in a data source and using my UDL file as "Extended Properties"

How come it is still asking me to provide a DSN?
Why is it not taking everything from the UDL file?

I have tried copying and pasting the ConnectionString with the Jet Provider to the UDL File but that didn't work either.
The only way my UDL file can connect to the remote DB is using the Jet Provider.

Shaul

Thinker · #9 05-02-2002, 09:48 AM

The reason it is asking for the DSN is the UDL file is no longer
being used because you took out the word file. You had it right to
begin with. You have to be able to get the Test Connection to
work before you can use the UDL for anything else. If you need it
to work with ODBC and a DSN, you would be much better off
skipping the UDL and just code the connection string directly. The
UDL can't relieve you of the burden of supplying the DSN when
you install this on some other computer.

#10 05-02-2002, 10:13 AM

THinker:

The Test Connection is working fine, and in the Data View everything is ok as well.
I don't want to work with ODBC and DSN.
I just don't understand why I keep getting my error.
If I have to specify a DSN with each new installation then there is no point to use the UDL.
I want to use a UDL with the Jet Provider and have that udl used for each installation of my application, so that if the location of the db changes, all I have to do is change the UDL.

If you are using a UDL in a data environment with the Jet Provider, can you please give me the OLE DB connection that is used in your UDL so I can just copy and paste it?

I just don't understand why I get the "failed to get RecordSet(S).." error. I seem to be doing everything right. Maybe its to do with file premissions or certain logins??

Shaul

Thinker · #11 05-02-2002, 10:23 AM

This is not a complicated thing at all. If the connection tests out,
and you can see all the tables, etc. in the dataview window, then
there is nothing wrong with your UDL. What do you have for your
command under the connection in the dataenvironment? Are you
able to expand it and see the fields?

#12 05-02-2002, 10:29 AM

I have a command that uses the following SQL query, no grouping, no parameters.

SELECT
Aborts.`District`
FROM
`Aborts` Aborts
WHERE
Aborts.`Date` = Format(Now-1,'dd-mmm-yy')
GROUP BY
Aborts.`District`

Then from that command I have a child Command that has the following SQL query:

SELECT
Aborts.`District`, Aborts.`Comments`, Count(Aborts.`Comments`) As ComPerDist
FROM
`Aborts` Aborts
WHERE
Aborts.`Date` = Format(Now-1,'dd-mmm-yy')
GROUP BY
Aborts.`District`,
Aborts.`Comments`

Again, no parameters or grouping, there is a relation between the "District" fields in both commands.

I can expand both commands and see the fields in the data environment and I draged and droped the fields into my data report to create it.

Both commands use my DEConnection as the active connection.

Thinker · #13 05-02-2002, 11:17 AM

The only thing that seems strange to me is enclosing field names
in backquotes (`). I can't say it won't work, but I would try [].
Still, if you can see the fields for both the parent and child
commands, it is doing something right. Anyway you look at this,
it isn't a problem with the UDL. The problem is no records are
available when the datareport is being run. Since I do so little
with the datareport, I am not sure what to suggest right now, but
I will try to test a few things and maybe I can come up with a
suggestion. In the meantime, try replacing all the 'fieldname`
with [fieldname].