newbie on SQL Server needs some direction .adp and .mde

I'm trying to figure out what is going on. I have an .adp file that has a
connection DNS to a .mde file on the network. It is confusing. In the
connection dialog it has my computer as the server name and it has the
database I am using to connect to the database I want. How can I find out
what is the DNS database source? It seems like it is connected to itself
instead of the right DNS. I don't see any tables in tables but I do get a
list of queries on the server so it must be connected but why don't I see the
tables as well? This is the one I would like to use but I don't get the
world icons that show the real SQL server data. In fact I don't even see the
tables on the access .mde file.
I try to view some queries and I get the error message "ADO error: MSDTC
on Server 'myComputer' is not available. I have MS SQL Server running in
Services as well as SQL Server express. Mainly because I don't know which one
is best. I also get the error 'Ole DB error trace Ole 10B Profvider MS Jet
OlebDB 4.0 Open rowset returned ox.... The specified table does not exist'
which I think means the query cannot access the tables. Why can I get the
queries and not the tables?
If I access it through terminal server access frontend I can make changes to
the queries. I also can see the tables which include dbo tables on a Server
but I would rather write my queries on my own desktop version and not do
anything on the terminal services version.
On the terminal services .mde database there are some pass through queries.
In fact the one I think I need appear to be a pass through query. If I open
the query the data is 2 years old. Can I make a make table from the pass
through query to see if it updates the data? I really do not want to do
anything wrong to the frontend db on terminal services but I need to write
this one query for a report.
tia,DNS are only a name given to a connection string. You don't have to use a
DSN to connect to a SQL-Server; it's only one possibility amongst others.
Second, DNS are only for ODBC while ADP use only ADO. So, you cannot use a
DSN with ADP and even with ADO, ADP can connect only to a SQL-Server and not
to a MDB or a MDE file. Also, the fact that you can connect to a sql-server
doesn't mean that you will be able to see everything (tables, views, sp,
functions, etc.) on the server because of permission issues.
Looks like that you have copied an ADP file from a terminal server to a
local machine but that the permissions on the server are set to block you
from accessing the data from outside the forms in the ADP project or
something like that.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Janis" <Janis@.discussions.microsoft.com> wrote in message
news:7333903F-5E59-4DF0-8BAB-5C45137AF7A5@.microsoft.com...
> I'm trying to figure out what is going on. I have an .adp file that has a
> connection DNS to a .mde file on the network. It is confusing. In the
> connection dialog it has my computer as the server name and it has the
> database I am using to connect to the database I want. How can I find out
> what is the DNS database source? It seems like it is connected to itself
> instead of the right DNS. I don't see any tables in tables but I do get a
> list of queries on the server so it must be connected but why don't I see
> the
> tables as well? This is the one I would like to use but I don't get the
> world icons that show the real SQL server data. In fact I don't even see
> the
> tables on the access .mde file.
> I try to view some queries and I get the error message "ADO error: MSDTC
> on Server 'myComputer' is not available. I have MS SQL Server running in
> Services as well as SQL Server express. Mainly because I don't know which
> one
> is best. I also get the error 'Ole DB error trace Ole 10B Profvider MS
> Jet
> OlebDB 4.0 Open rowset returned ox.... The specified table does not
> exist'
> which I think means the query cannot access the tables. Why can I get the
> queries and not the tables?
>
> If I access it through terminal server access frontend I can make changes
> to
> the queries. I also can see the tables which include dbo tables on a
> Server
> but I would rather write my queries on my own desktop version and not do
> anything on the terminal services version.
> On the terminal services .mde database there are some pass through
> queries.
> In fact the one I think I need appear to be a pass through query. If I
> open
> the query the data is 2 years old. Can I make a make table from the pass
> through query to see if it updates the data? I really do not want to do
> anything wrong to the frontend db on terminal services but I need to write
> this one query for a report.
> tia,