Connect to Read-Only Access database as linked server SQL 2005

Hi all,
I am trying to add an Access database as a linked server in SQL Server
2005.
For a "regular" database, everything works fine. BUT (there's a
but...) the database I'm trying to access is a "read-only" database
(i.e.: folder in which db is located is read-only). When I open it
from Access, it warns me that the db cannot be modified, bla bla bla.
I can access the data, but cannot make modifications to the structure
of the database (add queries, change tables, etc.).
So... my question is... Did I forget any parameter in the
sp_addlinkedserver procedure? Anytime I try to use the OpenQuery
method with this database I get the message
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "IVR_post"
returned message "The Microsoft Jet database engine cannot open the
file '\\CompletePathAndDatabaseName.mdb'
. It is already opened exclusively by another user, or you need
permission to view its data.".
However, like I said, the same db can be opened with access 2002 from
the same station, the same user, etc.
exec sp_addlinkedserver @.server='MyServer',
@.srvproduct='Access',
@.provider='Microsoft.Jet.OLEDB.4.0',
@.datasrc='\\CompletePathAndDatabaseName.mdb'
exec sp_addlinkedsrvlogin @.rmtsrvname='MyServer',
@.useself='false',
@.rmtuser='Admin',
@.rmtpassword=''
Regards,Hi,
I have replicated your problem and it works perfectly for me. Are you
sure that you don't have some issues with the Access lock file. If you
shut down all applications/clients/servers accessing this Access file,
does the .ldb file still show up?
Jonathan
fabien.turcotte@.fidomobile.ca wrote:
> Hi all,
> I am trying to add an Access database as a linked server in SQL Server
> 2005.
> For a "regular" database, everything works fine. BUT (there's a
> but...) the database I'm trying to access is a "read-only" database
> (i.e.: folder in which db is located is read-only). When I open it
> from Access, it warns me that the db cannot be modified, bla bla bla.
> I can access the data, but cannot make modifications to the structure
> of the database (add queries, change tables, etc.).
> So... my question is... Did I forget any parameter in the
> sp_addlinkedserver procedure? Anytime I try to use the OpenQuery
> method with this database I get the message
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "IVR_post"
> returned message "The Microsoft Jet database engine cannot open the
> file '\\CompletePathAndDatabaseName.mdb'
> . It is already opened exclusively by another user, or you need
> permission to view its data.".
> However, like I said, the same db can be opened with access 2002 from
> the same station, the same user, etc.
>
> exec sp_addlinkedserver @.server='MyServer',
> @.srvproduct='Access',
> @.provider='Microsoft.Jet.OLEDB.4.0',
> @.datasrc='\\CompletePathAndDatabaseName.mdb'
> exec sp_addlinkedsrvlogin @.rmtsrvname='MyServer',
> @.useself='false',
> @.rmtuser='Admin',
> @.rmtpassword=''
>
> Regards,
>sqlsql

Connect to ORACLE

Hello,
I created an ODBC connection to an ORACLE database that works in MS Access.
Now I would like to use the connection to link the tables in SS2K. However,
I can't figure out how to use it in SS2K. Should I be using
"sp_addlinkedserver?"
Any help would be appreciated.
--
Thanks in advance,
StevenYes. sp_addlinkedserver and sp_addlinkedsrvlogin. SQL Server Books Online
has examples specific to setting up Oracle linked servers.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Steven K" <sckeels@.lucent.com> wrote in message
news:OM5MKoOmDHA.2732@.TK2MSFTNGP11.phx.gbl...
Hello,
I created an ODBC connection to an ORACLE database that works in MS Access.
Now I would like to use the connection to link the tables in SS2K. However,
I can't figure out how to use it in SS2K. Should I be using
"sp_addlinkedserver?"
Any help would be appreciated.
Thanks in advance,
Steven|||Just want to add that you also need to install the Oracle client software on
the SQL Server box. The linked server to Oracle works fine.
You do need to watch out for performance and avoid bringing unnecessary data
to SQL instance for processing. Try to get as much processing done as
possible on Oracle and bring back only the resultset for further processing
on the SQL instance.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:eYa6FqOmDHA.2416@.TK2MSFTNGP10.phx.gbl...
> Yes. sp_addlinkedserver and sp_addlinkedsrvlogin. SQL Server Books Online
> has examples specific to setting up Oracle linked servers.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> What hardware is your SQL Server running on?
> http://vyaskn.tripod.com/poll.htm
>
>
> "Steven K" <sckeels@.lucent.com> wrote in message
> news:OM5MKoOmDHA.2732@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I created an ODBC connection to an ORACLE database that works in MS
Access.
> Now I would like to use the connection to link the tables in SS2K.
However,
> I can't figure out how to use it in SS2K. Should I be using
> "sp_addlinkedserver?"
> Any help would be appreciated.
>
> --
> Thanks in advance,
> Steven
>
>sqlsql

Connect to MSSQL from UNIX

Hi experts,
I am new in java programming. I am wana to write an application program to access MSSQL2000 from UNIX. But unfortunately, I am faile. (The program can access to MSSQL2000 from microsoft platform.). Anyone can help me to solve this program ? Thank you.
David Hunag
F.Y.I.
OS : HPUX 10.20
JDK : 1.1.8
..cshrc :
...
set mypath = ( . /usr/bin /usr/sbin /usr/local/bin )
set optpath = ( /opt/java/bin /usr/local/perl5/bin )
set hp93kpath = ( /opt/hp93000/soc/prod_env/bin )
set path=( $mypath $optpath $hp93kpath )
setenv classpath /opt/msSQL2000jdbc/lib/.msbase.jar: /opt/msSQL2000jdbc/lib/mssqkserver.jar:/opt/msSQL2000jdbc/lib/msutil.jar
...
?2004 Microsoft Corporation. All rights reserved. Terms of Use | Privacy Statement
Hi,
You need an ODBC driver middleware installed on the
UNIX box itself, so that it can talk to the SQL Server
Have a look into the old post for more more info,
http://groups.google.co.in/groups?q=...+unix&hl=en&lr
=&ie=UTF-8&selm=oopoaEevBHA.2288%40cpmsftngxa09&rnum=10
Thanks
Hari
MCDBA
"David Huang" <huangjuichung@.yahoo.com> wrote in message
news:6274A7C7-7C80-4D15-A46B-C6A8BC683EF5@.microsoft.com...
> Hi experts,
> I am new in java programming. I am wana to write an application program to
access MSSQL2000 from UNIX. But unfortunately, I am faile. (The program can
access to MSSQL2000 from microsoft platform.). Anyone can help me to solve
this program ? Thank you.
> David Hunag
> F.Y.I.
> OS : HPUX 10.20
> JDK : 1.1.8
> .cshrc :
> ...
> set mypath = ( . /usr/bin /usr/sbin /usr/local/bin )
> set optpath = ( /opt/java/bin /usr/local/perl5/bin )
> set hp93kpath = ( /opt/hp93000/soc/prod_env/bin )
> set path=( $mypath $optpath $hp93kpath )
> setenv classpath /opt/msSQL2000jdbc/lib/.msbase.jar:
/opt/msSQL2000jdbc/lib/mssqkserver.jar:/opt/msSQL2000jdbc/lib/msutil.jar
> ...
>
>
>
> 2004 Microsoft Corporation. All rights reserved. Terms of Use | Privacy
Statement
>

Connect to MSDE/SBS2003 remotely

I am writing a small app for a company using Access (adp) and MSDE. The MSDE
is installed on a Small Business Server and I have been told it is a
complete standard setup.
First question: I know MSDE is running because I recognise the icon in the
system tray. Also, locally I can connect to the MSDE so I know it is there.
However, from previous installations of MSDE I seem to recall that although
you don't get Enterprise Manager, Query Analyzer etc with MSDE, you do get
Client Network Utility etc. I have not been able to locate *any* utilities
for MSDE on the server. Is this normal?
This brings me onto my main question. I expect to do some alterations to the
database after the initial installation, and it is not feasible for me to
make a trip to the client for every alteration. I would therefore like to be
able to use Enterprise Manager that I have locally to connect to the remote
SBS/MSDE. How do I do that under the circumstances? I know they have a Cisco
firewall in place.
Any pointers in the right direction would be much appreciated.
What I have found out so far is that I need to check if Server Network
Utility (svrnetcn.exe) is present and has enabled TCP/IP protocol. But I am
sure that is not the end of the story...
"bobby" <bobby@.bobbys.com> wrote in message
news:%23arsRdOjEHA.3612@.TK2MSFTNGP12.phx.gbl...
> I am writing a small app for a company using Access (adp) and MSDE. The
MSDE
> is installed on a Small Business Server and I have been told it is a
> complete standard setup.
> First question: I know MSDE is running because I recognise the icon in the
> system tray. Also, locally I can connect to the MSDE so I know it is
there.
> However, from previous installations of MSDE I seem to recall that
although
> you don't get Enterprise Manager, Query Analyzer etc with MSDE, you do get
> Client Network Utility etc. I have not been able to locate *any* utilities
> for MSDE on the server. Is this normal?
> This brings me onto my main question. I expect to do some alterations to
the
> database after the initial installation, and it is not feasible for me to
> make a trip to the client for every alteration. I would therefore like to
be
> able to use Enterprise Manager that I have locally to connect to the
remote
> SBS/MSDE. How do I do that under the circumstances? I know they have a
Cisco
> firewall in place.
> Any pointers in the right direction would be much appreciated.
>
|||MSDE does not ship with any of the graphical utilities that you're used to
seeing. It does ship with a command line utility, osql.exe, that you can use
to manage it, assuming that you're familiar with T-SQL.
As to whether or not you're allowed to use the copy of SQL Server Enterprise
Manager you have to manage it remotely, I doubt it. Please see the FAQ page
at http://www.microsoft.com/sql/msde/howtobuy/msdeuse.asp. The sixth Q & A
is the following:
Q. Can I use SQL Server tools and services in conjunction with MSDE?
A. You can only use SQL Server tools and services in conjunction with
MSDE if you acquired MSDE through SQL Server 2000 (Developer Edition,
Standard Edition, or Enterprise Edition), and if you are using MSDE in
conjunction with a properly licensed copy of SQL Server 2000. Visit the How
to Buy page for information on obtaining a valid SQL Server license.
Note: The tools and services included with SQL Server Developer
Edition may not be used to manage production server environments.
The following utilities are installed by the MSDE setup application
and are provided without restrictions for use with the copy of MSDE that is
installed by your application: bcp.exe, cnfgsvr.exe, dcomscm.exe, osql.exe,
sqlmaint.exe, sqlmangr.exe, scm.exe, sqladhlp.exe, and svrnetcn.exe. The
dtsrun.exe utility is also provided, but can only be used to run existing
Data Transformation Services (DTS) packages against the copy of MSDE
installed by your application; it cannot be used to develop new DTS
packages.
It sounds to me like the small app that you're writing isn't being used in
conjuction with your copy of SQL Server 2000 and thus you won't be allowed
to use SQL Server Enterprise Manager against it.
As always, of course, I'm not a lawyer so you should consult yours. :-)
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"bobby" <bobby@.bobbys.com> wrote in message
news:%23arsRdOjEHA.3612@.TK2MSFTNGP12.phx.gbl...
>I am writing a small app for a company using Access (adp) and MSDE. The
>MSDE
> is installed on a Small Business Server and I have been told it is a
> complete standard setup.
> First question: I know MSDE is running because I recognise the icon in the
> system tray. Also, locally I can connect to the MSDE so I know it is
> there.
> However, from previous installations of MSDE I seem to recall that
> although
> you don't get Enterprise Manager, Query Analyzer etc with MSDE, you do get
> Client Network Utility etc. I have not been able to locate *any* utilities
> for MSDE on the server. Is this normal?
> This brings me onto my main question. I expect to do some alterations to
> the
> database after the initial installation, and it is not feasible for me to
> make a trip to the client for every alteration. I would therefore like to
> be
> able to use Enterprise Manager that I have locally to connect to the
> remote
> SBS/MSDE. How do I do that under the circumstances? I know they have a
> Cisco
> firewall in place.
> Any pointers in the right direction would be much appreciated.
>
begin 666 ts.gif
J1TE&.#EA`0`!`( ``````/___R'Y! $`````+ `````!``$```(!1 `[
`
end
|||Hi bobby,
In addition, we've got a program called MSDE Manager that's free for
personal use. Details at our site.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
news:O7dgcQrjEHA.1348@.TK2MSFTNGP15.phx.gbl...
> MSDE does not ship with any of the graphical utilities that you're used to
> seeing. It does ship with a command line utility, osql.exe, that you can
use
> to manage it, assuming that you're familiar with T-SQL.
> As to whether or not you're allowed to use the copy of SQL Server
Enterprise
> Manager you have to manage it remotely, I doubt it. Please see the FAQ
page
> at http://www.microsoft.com/sql/msde/howtobuy/msdeuse.asp. The sixth Q & A
> is the following:
> Q. Can I use SQL Server tools and services in conjunction with
MSDE?
> A. You can only use SQL Server tools and services in conjunction
with
> MSDE if you acquired MSDE through SQL Server 2000 (Developer Edition,
> Standard Edition, or Enterprise Edition), and if you are using MSDE in
> conjunction with a properly licensed copy of SQL Server 2000. Visit the
How
> to Buy page for information on obtaining a valid SQL Server license.
> Note: The tools and services included with SQL Server Developer
> Edition may not be used to manage production server environments.
> The following utilities are installed by the MSDE setup application
> and are provided without restrictions for use with the copy of MSDE that
is
> installed by your application: bcp.exe, cnfgsvr.exe, dcomscm.exe,
osql.exe,
> sqlmaint.exe, sqlmangr.exe, scm.exe, sqladhlp.exe, and svrnetcn.exe. The
> dtsrun.exe utility is also provided, but can only be used to run existing
> Data Transformation Services (DTS) packages against the copy of MSDE
> installed by your application; it cannot be used to develop new DTS
> packages.
>
> It sounds to me like the small app that you're writing isn't being used in
> conjuction with your copy of SQL Server 2000 and thus you won't be allowed
> to use SQL Server Enterprise Manager against it.
> As always, of course, I'm not a lawyer so you should consult yours. :-)
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "bobby" <bobby@.bobbys.com> wrote in message
> news:%23arsRdOjEHA.3612@.TK2MSFTNGP12.phx.gbl...
the[vbcol=seagreen]
get[vbcol=seagreen]
utilities[vbcol=seagreen]
to[vbcol=seagreen]
to
>
>
sqlsql

Connect to internal SharePoint database from web site.

Hello,
There's information on my Internal SharePoint DataBase that I want to access. How can I do this? Could you help me or at least point me in the right direction.

The information I am requesting is just text.

Thank you,
Mark

Hi Mark,

I don't think so that kind of info is available. The table structure and relationships are quite messy. It's not recommended to access those tables directly, not to say it's prohibited.

I have done a lot of Sharepoint customizations, and what I did was accessing the Sharepoint internal object with it's object model. You can download the WSS SDK (both 2.0 and 3.0), and install it. The help file in the SDK has alot of sample code that we can use.

Happy coding :)

|||

Hi Mark,

I don't think so that kind of info is available. The table structure and relationships are quite messy. It's not recommended to access those tables directly, not to say it's prohibited.

I have done a lot of Sharepoint customizations, and what I did was access the Sharepoint internal object with it's object model. You can download the WSS SDK (both 2.0 and 3.0) are available. There are alot of sample code that we can use.

Happy coding :)

|||

Hi Mark,

I don't think so that kind of info is available. The table structure and relationships are quite messy. It's not recommended to access those tables directly, not to say it's prohibited.

I have done a lot of Sharepoint customizations, and what I did was accessing the Sharepoint internal object with it's object model. You can download the WSS SDK (both 2.0 and 3.0), and install it. The help file in the SDK has alot of sample code that we can use.

Happy coding :)

|||

I am starting to see this now. Thank you for your response.

Connect to DB2 using SQL-Server

Hello there
I have SQL 7 and ODBC-Client access to connect to DB2 (AS400). I can connect
to the DB2 database using the ODBC and VB without problems but now I want t
o use the sp_addlinkedserver to link the DB2 to my SQL database using the OD
BC. Does anybody know what
parameters I have to use and what are the values to link the DB2 to my datab
ase?.
I will appreciate any help because I've navigated for the internet looking f
or this information without luck.
regards
osotoIf you install Client Access on the server, it will install
the drivers and providers for the AS400 on the SQL Server
box so that you can use linked servers. For data source,
use the IP address of the AS400. For provider string, you
need to include the library you are using, connect timeout
setting and code page. There is some documentation for the
settings in the Client Access help files.
You can also use the OLE DB provider and follow the similar
example on books online for "OLE DB Provider for DB2"
You'd want to change the provider to IBMDA400
and set @.provstr somewhat like:
InitCat=YourLibrary;CCSID=37;PCCodePage=
1252;
Data Source=xxx.xxx.xxx.xxx
Settings will depend on how your AS400 is configured. Again,
the Client Access help files have information on the
necessary connection string settings.
-Sue
On Fri, 5 Mar 2004 08:26:07 -0800, "osoto"
<anonymous@.discussions.microsoft.com> wrote:

>Hello there
>I have SQL 7 and ODBC-Client access to connect to DB2 (AS400). I can connect to the
DB2 database using the ODBC and VB without problems but now I want to use the sp_ad
dlinkedserver to link the DB2 to my SQL database using the ODBC. Does anybody know w
hat
parameters I have to use and what are the values to link the DB2 to my database?.kred">
>I will appreciate any help because I've navigated for the internet looking
for this information without luck.
>regards
>osoto
>sqlsql

Connect to AS400 table

Hi,
Is there anyway to connect to AS400 table (something like link table feature in MS Access). OR. I have to use the DTS package and schedule it to update in intervals, this i am not preferring due to the performance issue ?

your help highly appreciate

thanks
Murali.As long as you have ODBC drivers that will allow a Windows application to access the AS400 table, you can use sp_addlinkedserver (http://msdn2.microsoft.com/en-us/library/ms190479.aspx) to allow SQL Server to access it too.

-PatP

Connect to an SQL 2005 server through proxy

Ok, here is the situation...
I have created an Access Project application which connects to our SQL
Server.
SQL server is not behind a proxy, it listens to the default port
Most of our users are outside the LAN and thus they connect through the
internet.
One of our users works in a university environment which controls the
outbound connections through proxy.
I want this user to be able to connect to the SQL server through this
proxy (which i cant control ofcourse)
Is this possible? What settings are required in the proxy (no idea if
it is an MS proxy or not). Does the user has to change anything inside
the MS access connection dialog?
Plz help me on this
If it is not possible please provide some alternatives...
Thanks in advance
Noone knows anything about that? Plz help me on this
On Oct 21, 4:45 pm, IoannisKirmitzog...@.gmail.com wrote:
> Ok, here is the situation...
> I have created an Access Project application which connects to our SQL
> Server.
> SQL server is not behind a proxy, it listens to the default port
> Most of our users are outside the LAN and thus they connect through the
> internet.
> One of our users works in a university environment which controls the
> outbound connections through proxy.
> I want this user to be able to connect to the SQL server through this
> proxy (which i cant control ofcourse)
> Is this possible? What settings are required in the proxy (no idea if
> it is an MS proxy or not). Does the user has to change anything inside
> the MS access connection dialog?
> Plz help me on this
> If it is not possible please provide some alternatives...
> Thanks in advance
sqlsql

Connect to an SQL 2005 server through proxy

Ok, here is the situation...
I have created an Access Project application which connects to our SQL
Server.
SQL server is not behind a proxy, it listens to the default port
Most of our users are outside the LAN and thus they connect through the
internet.
One of our users works in a university environment which controls the
outbound connections through proxy.
I want this user to be able to connect to the SQL server through this
proxy (which i cant control ofcourse)
Is this possible? What settings are required in the proxy (no idea if
it is an MS proxy or not). Does the user has to change anything inside
the MS access connection dialog'
Plz help me on this
If it is not possible please provide some alternatives...
Thanks in advanceNoone knows anything about that? Plz help me on this
On Oct 21, 4:45 pm, IoannisKirmitzog...@.gmail.com wrote:
> Ok, here is the situation...
> I have created an Access Project application which connects to our SQL
> Server.
> SQL server is not behind a proxy, it listens to the default port
> Most of our users are outside the LAN and thus they connect through the
> internet.
> One of our users works in a university environment which controls the
> outbound connections through proxy.
> I want this user to be able to connect to the SQL server through this
> proxy (which i cant control ofcourse)
> Is this possible? What settings are required in the proxy (no idea if
> it is an MS proxy or not). Does the user has to change anything inside
> the MS access connection dialog'
> Plz help me on this
> If it is not possible please provide some alternatives...
> Thanks in advance

Connect to a Novell NDS Datasoource via LDAP

Hi All,

Is there a connector in Connections Manager that can access a Novell NDS tree and help to pull data in an SSIS package?

Thanks,

BIG SYou might be able to use a script component (vb.net) as a source to get at your Novell tree.|||Can't. I run a component in CLR and it craps out.Would SSIS be any different?|||There is a Novell ODBC connector which allows you to do that. Check out Novell's website for "NDK:Novell

ODBC Driver for eDirectory".

You need to have rights to the NDS to get the information and you need to have the Novell CLIENT installed before you install the driver (which they don't tell you).|||Is that a connector that I can add to SSIS?|||

BIG S wrote:

Is that a connector that I can add to SSIS?

Once it's installed, you can use a datareader source against the ODBC driver.|||Would simply like to pull user object data back from eDirectory to MS SQL without having to implement the Novell Client, which I think is needed for the Novell ODBC driver. This can be done with a couple of lines of perl... Isn't there an easy way to make this connection and pull this data from within SQL Server? It's just an ldap query....sqlsql

Connect to a Novell NDS Datasoource via LDAP

Hi All,

Is there a connector in Connections Manager that can access a Novell NDS tree and help to pull data in an SSIS package?

Thanks,

BIG S
You might be able to use a script component (vb.net) as a source to get at your Novell tree.|||Can't. I run a component in CLR and it craps out.Would SSIS be any different?
|||There is a Novell ODBC connector which allows you to do that. Check out Novell's website for "NDK:Novell ODBC Driver for eDirectory".

You need to have rights to the NDS to get the information and you need to have the Novell CLIENT installed before you install the driver (which they don't tell you).

|||Is that a connector that I can add to SSIS?
|||

BIG S wrote:

Is that a connector that I can add to SSIS?

Once it's installed, you can use a datareader source against the ODBC driver.|||Would simply like to pull user object data back from eDirectory to MS SQL without having to implement the Novell Client, which I think is needed for the Novell ODBC driver. This can be done with a couple of lines of perl... Isn't there an easy way to make this connection and pull this data from within SQL Server? It's just an ldap query....

Connect SSRS to MS Access db that has User-Level Security

Hello Everyone,
I have an Access database that i set up some time ago and that is still in
use by employees. The database was set up using user-level security. The
database is seperated, one- the "back end", named **be.mdb- housing the
tables and some queries. The other- **.mdb- has all the forms, modules and
other queries. The back end is on a shared file server, and each user has the
front end on thier local machine.
I use the Report Wizard to setup the data source connection, which is ODBC,
and permissions. I can preview it in Report Designer fine. Once the report is
published to the Report Manager however, I get an error telling me that no
connection to datasource can be established because either i have no
permissions, the workgroup information file can not be found or someone else
is using the file.
When I setup up the dsn, i made sure to include the WIF in the string- named
secure.mdw. However I'm stumped as to my failure to connect. Can anyone give
me some guidance on how to properly setup connection from SSRS to an Access
datbase that has user-level security?
Many thanks.I'd just move it to SQL Server.. Honestly; I've been through a ton of
migrations over the years-- moving to SQL Server _NOW_ instead of
later can save you a lot of headaches.
I mean.. the front end ; it should be easy to move to an Access Data
Project-- simplify the database side before worrying about the
reports.
-Aaron
On Mar 21, 1:49=A0pm, Damon Johnson
<DamonJohn...@.discussions.microsoft.com> wrote:
> Hello Everyone,
> I have an Access database that i set up some time ago and that is still in=
> use by employees. The database was set up using user-level security. The
> database is seperated, one- the "back end", named **be.mdb- housing the
> tables and some queries. The other- **.mdb- has all the forms, modules and=
> other queries. The back end is on a shared file server, and each user has =the
> front end on thier local machine.
> I use the Report Wizard to setup the data source connection, which is ODBC=,
> and permissions. I can preview it in Report Designer fine. Once the report= is
> published to the Report Manager however, I get an error telling me that no=
> connection to datasource can be established because either i have no
> permissions, the workgroup information file can not be found or someone el=se
> is using the file.
> When I setup up the dsn, i made sure to include the WIF in the string- nam=ed
> secure.mdw. However I'm stumped as to my failure to connect. Can anyone gi=ve
> me some guidance on how to properly setup connection from SSRS to an Acces=s
> datbase that has user-level security?
> Many thanks.

Connect SQL with Active Directory

Hi everybody. I have this problem: I have to access to the Active Directory of a remote server from my computer using SQL and make searches based on the LDAP of the server. Someone told me to use Add Linked Server, but I don't know how to do it with the Active Directory. Thanks for the help.

Please take a look at the link below for the solution:

http://msdn2.microsoft.com/en-US/library/ms190803.aspx

Books Online topics on linked servers are also useful to know. Note that there are however restrictions on what you can do via distributed queries. For example, some of the collection data types in the Active Directory Schema are not supported in SQL Server, number of rows that you can retrieve is restricted by default & so on.

|||

Linking SQL Server to Active Directory is tricky. and Microsoft support/help isn't very intuitive for this.
Check a few things first...
1. two single quotes not double.
2. Make connection w/out security context.

Follow this syntax:
EXEC master.dbo.sp_addlinkedserver @.server = N'ADSI',
@.srvproduct=N'Active Directory Services', @.provider=N'ADsDSOObject',
@.datasrc=N'Servername.domain.com' --AKA the full computer name of the AD server

Then execute the openquery like this:

select * from openquery
(
ADSI,'SELECT name
FROM ''LDAP://Servername.domain.com''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
')

I couldn't find anything on the Internet that would help me.

Connect SQL with Active Directory

Hi everybody. I have this problem: I have to access to the Active Directory of a remote server from my computer using SQL and make searches based on the LDAP of the server. Someone told me to use Add Linked Server, but I don't know how to do it with the Active Directory. Thanks for the help.

Please take a look at the link below for the solution:

http://msdn2.microsoft.com/en-US/library/ms190803.aspx

Books Online topics on linked servers are also useful to know. Note that there are however restrictions on what you can do via distributed queries. For example, some of the collection data types in the Active Directory Schema are not supported in SQL Server, number of rows that you can retrieve is restricted by default & so on.

|||

Linking SQL Server to Active Directory is tricky. and Microsoft support/help isn't very intuitive for this.
Check a few things first...
1. two single quotes not double.
2. Make connection w/out security context.

Follow this syntax:
EXEC master.dbo.sp_addlinkedserver @.server = N'ADSI',
@.srvproduct=N'Active Directory Services', @.provider=N'ADsDSOObject',
@.datasrc=N'Servername.domain.com' --AKA the full computer name of the AD server

Then execute the openquery like this:

select * from openquery
(
ADSI,'SELECT name
FROM ''LDAP://Servername.domain.com''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
')

I couldn't find anything on the Internet that would help me.

connect SQL server to access

hi everybody!
can anyone help me, please. thanks in advance.
i have installed sql server express 2005, and have a .mdb database in access
2003. with the upsizing wizard i have succeeded in connecting to a sql
database.
i am now trying manually to connect to sql server, bu t i cant seem to find
my database anywhere, in the "linked tables" dialog box.
id appreciate any help.
im a complete newnie to sql server
ill note again that my .mdb database currently has linked tables, which were
added automatically. my problem is figuring out how to do it myself.
--
Lumped & Stumped
Thankfully, YisManYou may have better response by posting in one of the 20+
microsoft.public.access groups.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"YisMan" <yisman@.att.net> wrote in message
news:508E1C1E-6172-4029-B20D-FE17389DE34D@.microsoft.com...
> hi everybody!
> can anyone help me, please. thanks in advance.
> i have installed sql server express 2005, and have a .mdb database in
> access
> 2003. with the upsizing wizard i have succeeded in connecting to a sql
> database.
> i am now trying manually to connect to sql server, bu t i cant seem to
> find
> my database anywhere, in the "linked tables" dialog box.
> id appreciate any help.
> im a complete newnie to sql server
> ill note again that my .mdb database currently has linked tables, which
> were
> added automatically. my problem is figuring out how to do it myself.
> --
> Lumped & Stumped
> Thankfully, YisMan

connect more than one database in a query

How can I access tables from different databases in the same server in a
query or stored procedure.
Also how to access databases existing in different database servers.
Thanks fro your help.
Retna
Hi,
To access the tables in the databases in same server you can use the below
syntax.
select * from dbname.tableowner.table_name
To access the table in in differenet server u need to create linked server.
AFter that you could use
select * from linkedservername.dbname.tableowner.table_name
See the below article on how to create linked server and execute DML
comamnds.
http://www.microsoft.com/India/msdn/articles/166.aspx
Thanks
Hari
MCDBA
"Retna" <Retna@.discussions.microsoft.com> wrote in message
news:04CD4FE1-0A0F-466C-9EBC-0AEBE55440A8@.microsoft.com...
> How can I access tables from different databases in the same server in a
> query or stored procedure.
> Also how to access databases existing in different database servers.
> Thanks fro your help.
> Retna
|||Thanks Hari for your valuable and timely reply .
Retna
"Hari Prasad" wrote:

> Hi,
> To access the tables in the databases in same server you can use the below
> syntax.
> select * from dbname.tableowner.table_name
> To access the table in in differenet server u need to create linked server.
> AFter that you could use
> select * from linkedservername.dbname.tableowner.table_name
> See the below article on how to create linked server and execute DML
> comamnds.
> http://www.microsoft.com/India/msdn/articles/166.aspx
> Thanks
> Hari
> MCDBA
>
> "Retna" <Retna@.discussions.microsoft.com> wrote in message
> news:04CD4FE1-0A0F-466C-9EBC-0AEBE55440A8@.microsoft.com...
>
>

connect more than one database in a query

How can I access tables from different databases in the same server in a
query or stored procedure.
Also how to access databases existing in different database servers.
Thanks fro your help.
RetnaHi,
To access the tables in the databases in same server you can use the below
syntax.
select * from dbname.tableowner.table_name
To access the table in in differenet server u need to create linked server.
AFter that you could use
select * from linkedservername.dbname.tableowner.table_name
See the below article on how to create linked server and execute DML
comamnds.
http://www.microsoft.com/India/msdn/articles/166.aspx
Thanks
Hari
MCDBA
"Retna" <Retna@.discussions.microsoft.com> wrote in message
news:04CD4FE1-0A0F-466C-9EBC-0AEBE55440A8@.microsoft.com...
> How can I access tables from different databases in the same server in a
> query or stored procedure.
> Also how to access databases existing in different database servers.
> Thanks fro your help.
> Retna|||Thanks Hari for your valuable and timely reply .
Retna
"Hari Prasad" wrote:
> Hi,
> To access the tables in the databases in same server you can use the below
> syntax.
> select * from dbname.tableowner.table_name
> To access the table in in differenet server u need to create linked server.
> AFter that you could use
> select * from linkedservername.dbname.tableowner.table_name
> See the below article on how to create linked server and execute DML
> comamnds.
> http://www.microsoft.com/India/msdn/articles/166.aspx
> Thanks
> Hari
> MCDBA
>
> "Retna" <Retna@.discussions.microsoft.com> wrote in message
> news:04CD4FE1-0A0F-466C-9EBC-0AEBE55440A8@.microsoft.com...
> > How can I access tables from different databases in the same server in a
> > query or stored procedure.
> > Also how to access databases existing in different database servers.
> >
> > Thanks fro your help.
> >
> > Retna
>
>

Connect from Visual Basic 6 to SQL Server 2005 express

Hi people,
I downloaded sql server 2005 express and I installed it. I have Visual
Basic 6 application, that work with MS access with ado connection. This
connection give me seek and findfirst methods. I want migrate this access db
to sql sever 2005, but i don't make any change in vb6 code (only the
connection).
My first question is, can i connect from vb6 application to sql sever
2005 express? Moreover,how must i connect the application to sql sever and
work with seek and findfirst methods?
If it's possible, you will put a code example to connect...
Thanks!> Hi people,
> I downloaded sql server 2005 express and I installed it. I have Visual
> Basic 6 application, that work with MS access with ado connection. This
> connection give me seek and findfirst methods. I want migrate this access
db
> to sql sever 2005, but i don't make any change in vb6 code (only the
> connection).
> My first question is, can i connect from vb6 application to sql sever
> 2005 express? Moreover,how must i connect the application to sql sever
and
> work with seek and findfirst methods?
> If it's possible, you will put a code example to connect...
> Thanks!
--
You can't use SEEK and FINDFIRST methods against a SQL Server table. Those
methods will only work against an Access Jet database.
You need to use T-SQL statements such as SELECT...WHERE to accomplish
similar results. Code changes are inevitable.
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Connect from Visual Basic 6 to SQL Server 2005 express

Hi people,
I downloaded sql server 2005 express and I installed it. I have Visual
Basic 6 application, that work with MS access with ado connection. This
connection give me seek and findfirst methods. I want migrate this access db
to sql sever 2005, but i don't make any change in vb6 code (only the
connection).
My first question is, can i connect from vb6 application to sql sever
2005 express? Moreover,how must i connect the application to sql sever and
work with seek and findfirst methods?
If it's possible, you will put a code example to connect...
Thanks!

> Hi people,
> I downloaded sql server 2005 express and I installed it. I have Visual
> Basic 6 application, that work with MS access with ado connection. This
> connection give me seek and findfirst methods. I want migrate this access
db
> to sql sever 2005, but i don't make any change in vb6 code (only the
> connection).
> My first question is, can i connect from vb6 application to sql sever
> 2005 express? Moreover,how must i connect the application to sql sever
and
> work with seek and findfirst methods?
> If it's possible, you will put a code example to connect...
> Thanks!
You can't use SEEK and FINDFIRST methods against a SQL Server table. Those
methods will only work against an Access Jet database.
You need to use T-SQL statements such as SELECT...WHERE to accomplish
similar results. Code changes are inevitable.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Connect from VB6.0 via ODBCDirect to SQL Server

Hi All,
I have an existing application with frontend in VB 6.0 and backend in MS Access. I am trying to convert the application to SQL Server 2000 (standard) backend. At the moment the frontend uses ODBCDirect to communicate with MS Access backend. I upsized the MS Access database to SQL Server 2000 using MS Access upsizing wizard. (I know you need to use DTS, but I tried that and it did not copy the data across citing the error "The Server could not load DCOM", hence I used the upsizing wizard).
The backend now set up in SQL Server, I presume all I need to do is change the connection string in VB6.0, which I did, but I have not been able to make a successful connection yet. The following is the connection string I am using:

"ODBC;DSN=;Server=sql-server;Database=cabinetSQL;UID=dbo;PWD=;"

The SQL Server has been configured for windows authentication and the new database that I created due to upsizing, in the User section, it lists "dbo" as user name and login name is "domain/myloginname". So in the user ID I am using "dbo". I got some hints about how to connecte via ODBCDirect to SQL Server using the required workspace, as follows:

Set dbw = CreateWorkspace("pomsws", "admin", "", dbUseODBC)
Workspaces.Append dbw

Set pomsc = dbw.OpenConnection("pompom", dbDriverNoPrompt, _
true, con_string)

where con_string is the above mentioned connection string.

Can anybody let me know what I am doing wrong and provide any suggestions?

Thanks to all for your time and efforts.

Regards:
PrathmeshAn update on my question. I created a seperate small application just to test for the connectivity. The following is the code:

Dim strConnectionAdmin As String
Dim objConnAdmin As Connection
Dim CollegeRs As Recordset
Dim strsql As String
Dim i As Integer
Dim mdbodbcdirect As Database
Dim dbw As Workspace
Dim pomsc As Connection

strConnectionAdmin = "driver={SQL Server};Server=sql-server;Database=cabinetSQL;uid=;pwd=;"

Set dbw = CreateWorkspace("pomsws", "", "", dbUseODBC)
Workspaces.Append dbw

'On Error GoTo handler
Set pomsc = dbw.OpenConnection("pompom", dbDriverNoPrompt, _
False, strConnectionAdmin)

MsgBox "Connection success"

End Sub

This gives me an error "Invalid Argument" and points to the "Set pomsc" statement.

If I change the connection string to:
strConnectionAdmin = "ODBC;DSN=' ';driver={SQL Server};Server=sql-server;Database=cabinetSQL;uid=;pwd=;"

then it gives me an error for "ODBC-Call failed".

I am not sure where I am going wrong. Please help.

Regards:
Prathmesh