Re: Microsoft Excell Connection

From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 02/26/04 

Date: Thu, 26 Feb 2004 15:26:10 -0000

Use your linked server to Access then as the source by adding a SQL Server
connection and using OPENQUERY()

You should be able to add an Access Connection to a secured Access MDB

Enter the username and password when setting things up. In the advanced
properties enter the location of your system database.
I also posted an article about the configuration of this in code. 

-- 
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
<anonymous@discussions.microsoft.com> wrote in message
news:269201c3fc78$7e4741a0$a301280a@phx.gbl...
> Ok. I try again. Sorry I'm very fresh man in SQL Server.
> Thank You for your patience.
>
> I asking about Excel
> ... from the beginning
>
> I wanted to make  first very easy DTS / Local Package
> which would select rekords from ACCESS and export them to
> EXCEL.
> So
>
> 1.I made the first connection to UNSECURED Access
> Database, then
> 2.I made the second connectiom to Ms Excel and at the and
> 3.I added a task
> It was working.
>
> Next I decided to create a Linked server to access a
> SECURED Access Database. According to SQL Server Help file
> I configured(using the Registry Editor) the Windows
> Registry. It is required operation.
> So I changed line
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\JET\4.0
> \Engines\System DB in Windows Registry. I put the full
> path of my Workgroup Information file instead of standard
> system.mdw
>
> My Linked Server to access secured database WORKS but my
> DTS stop working. Login failed
>
> I created that DTS again
> 1. I made the first connection to UNSECURED Access
> Database ( with login and password)
> 2. When I tried to make the second connectiom to Ms Excel
> setting: name - (The only thing i can set) and save it -
>  DOESN'T WORK - becouse The user and password is required
>
> I would like to
> 1.have a linked server SECURED ACCESS DATABASE
> 2.have a possibility to make connectios to MS EXCEL
>
> so I repeat my questions
>
> Is it possible?
> How and Why?
> Is The Dynamic Properties Task with VB code the only way
> to solve the problem?
> Maybe I missed any very importent step?
>
> Please send any suggestion to my e-mail
> at home: borys.jarzembski@wp.pl
> at work: borys.jarzembski@bunge.com
>
> THANK YOU VERY MUCH
> P.S. Sorry for my English
>
> >-----Original Message-----
> >OK Just reread the posts
> >
> >The subject is re: Microsoft Excel
> >You asked about Microsoft Access
> >
> >Do you want Excel or Access?
> >
> >If you have an Access Linked server that works then use
> that.  You add a SQL
> >Server connection but use the Linked server
> >
> >-- 
> >
> >Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> >www.allisonmitchell.com - Expert SQL Server Consultancy.
> >www.SQLDTS.com - The site for all your DTS needs.
> >I support PASS - the definitive, global community
> >for SQL Server professionals - http://www.sqlpass.org
> >
> >
> ><anonymous@discussions.microsoft.com> wrote in message
> >news:1f3401c3fc4b$78675310$a501280a@phx.gbl...
> >> Thank you very much Allan
> >> Your help was very useful but It did not solve my
> problem.
> >> These are the steps I did.
> >>
> >> 1.Set up Workgroup Information with Windows Registry
> >> 2.Configured Linked Server to My Acces Database - WORKS
> >> 3.Making new DTS
> >> 3a.Add New Access Connection to secured Access Database(
> >> setting: database path and name .mdb; user and password
> >> according to Workgruop Info set in Windows Registry) -
> >> WORKS
> >> 3b.Add new Excel Connection (setting: name - The only
> >> thing i can set) - DOESN'T WORK - I can not even save
> the
> >> connection becouse The user and password is required.
> I'm
> >> not asked for user and password.
> >>
> >> Why?
> >> Is The Dynamic Properties Task with VB code the only way
> >> to solve the problem?
> >> Maybe I missed any very importent step?
> >>
> >>
> >>
> >> >When setting up your Access Source it should ask you
> for
> >> UserName ad
> >> >Password.
> >> >
> >> >Does this article help you at all to set the Workgroup
> >> file
> >> >
> >> >How can I dynamically set the Access System Database
> >> >(http://www.sqldts.com/default.aspx?241)
> >> >
> >> >
> >> >
> >> >-- 
> >> >
> >> >Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> >> >www.allisonmitchell.com - Expert SQL Server
> Consultancy.
> >> >www.SQLDTS.com - The site for all your DTS needs.
> >> >I support PASS - the definitive, global community
> >> >for SQL Server professionals - http://www.sqlpass.org
> >> >
> >> >
> >> >"borys.jarzembski@bunge.com"
> >> <anonymous@discussions.microsoft.com> wrote in
> >> >message news:134501c3fbb0$bae7b8b0$a501280a@phx.gbl...
> >> >> Hello
> >> >> I created a Linked server to access a secured Access
> >> >> Database. According to SQL Server Help file I
> configured
> >> >> (using the Registry Editor) the Windows Registry
> >> >> I have add the full path name of the Workgroup
> >> Information
> >> >> file Used by my Access Database => in the line
> >> >> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\JET\4.0
> >> >> \Engines\System DB
> >> >> The Linked server is working correct but I have a
> >> problem
> >> >> with DTS. When I'm trying to add the new connection
> to
> >> >> Microsoft Excel I'm learnt that my login is
> incorrect.
> >> >> In Microsoft Excel Connection Window I HAVE NO field
> to
> >> >> put login and password as I have in Microsoft Access
> >> >> Connection Window
> >> >> What should I do?
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Relevant Pages

  • Re: My Book is Incomplete Regarding SQL Commands ;-(
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... Create a Connection object and pass in a ConnectionString to ... manage the database table you reference. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: (Newbie)Application Roles
    ... level: the database itself. ... SQL Server accommodates these needs through the use of application ... the user's connection through a specific application. ... the connection permanently loses all permissions applied to ...
    (microsoft.public.sqlserver.security)
  • Re: Help! Set up Windows Group to access application
    ... The connection string for a trusted connection is: ... --add login as database user ... SQL Server MVP ... If you have troubles with finding your ways in Crystal Reports, ...
    (microsoft.public.sqlserver.security)
  • Re: My Book is Incomplete Regarding SQL Commands ;-(
    ... Visual Studio but not to VS 2003. ... Create a Connection object and pass in a ConnectionString to address ... manage the database table you reference. ... Yep, my latest book can help too, but mostly if you're targeting SQL Server ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: Logging in in background
    ... wold take all kinds of modifictions as I'd need to be checking that each SQL ... my database and all have connection strings associated with them. ... I suspect that there is also an issue on the SQL Server side as I keep ...
    (microsoft.public.access.modulesdaovba)
Quantcast