[MS_AccessPros] Re: getting protected access data into excel

 

I had to do something like this for a user.
I built a form when the user clicked the first button I made the query a make table query, the query ran and prompted the user. The user typed what they wanted and the table was created.
The user then clicked the second button which opened the Excel workbook which pointed to the table.
It might be a litle clunky, but it worked.

--- In MS_Access_Professionals@yahoogroups.com, Phil Knowles <pdk444444@...> wrote:
>
> Hi John
>  
> The query is simple - something along the lines of
>  
> select field1, field2, field3 from table1 where field2 like "*"&[enter text]&"*".
>  
> oviously if I am running it from a spreadsheet then the text (that in an access query would be entered at runtime) can be the value in a cell
>  
> I'm afraid I have no idea how to write the rest of the macro - ie to connect to the database (with username and password), load the QueryDef, set the Parameter, then open and "paste" it into the spreadsheet.
>  
> Are there any examples of something similar or can you point me in the right direction?
>  
> cheers
>  
> Phil
>
>
> ________________________________
> From: John Viescas <JohnV@...>
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Sunday, 17 March 2013, 13:42
> Subject: RE: [MS_AccessPros] Re: getting protected access data into excel
>
>
>  
>
> Phil-
>
> Not from the UI. You could write an Excel "macro" to connect to the database, load the QueryDef, set the Parameter, then open and "paste" it into your spreadsheet.
>
> John Viescas, Author
>
> Microsoft Access 2010 Inside Out
>
> Microsoft Access 2007 Inside Out
>
> Microsoft Access 2003 Inside Out
>
> Building Microsoft Access Applications
>
> SQL Queries for Mere Mortals
>
> http://www.viescas.com/
>
> (Paris, France)
>
> From: mailto:MS_Access_Professionals%40yahoogroups.com [mailto:mailto:MS_Access_Professionals%40yahoogroups.com] On Behalf Of Phil Knowles
> Sent: Sunday, March 17, 2013 2:35 PM
> To: mailto:MS_Access_Professionals%40yahoogroups.com
> Subject: Re: [MS_AccessPros] Re: getting protected access data into excel
>
> Hi Bill
>
> I have run into another problem I'm afraid.
>
> with your help I am now able to pass security and I can get data into my spreadsheet from my database .......
>
> however, the query I want to run doesn't appear in the list of queries.
>
> After a bit of experimenting, I have found that it doesn't appear in the list because it it has a criteria parameter entered when it is run.
>
> So my question now is - is it possible to run this type of query when loading data from the database into the spreadsheet?
>
> once again, any help you can give will be very much appreciated.
>
> Phil
>
>
>
> ________________________________
> From: Bill Mosca <mailto:wrmosca%40comcast.net <mailto:wrmosca%40comcast.net> >
> To: mailto:MS_Access_Professionals%40yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com>
> Sent: Saturday, 16 March 2013, 16:35
> Subject: [MS_AccessPros] Re: getting protected access data into excel
>
> Phil
>
> Glad I could save you all that time. My grandma used to use that expression. I think I was about 10 years old when I asked her how long a month of Sundays was. In her wise way she said, "Go figure it out."
>
> Bill
>
> --- In mailto:MS_Access_Professionals%40yahoogroups.com, Phil Knowles <pdk444444@> wrote:
> >
> > Hi Bill
> > Â
> > You are an absolute star - I would never have worked that out in a month of Sundays!
> > Â
> > thank you
> > Â
> > Phil
> > Â
> > Â
> >
> >
> > ________________________________
> > From: Bill Mosca <wrmosca@>
> > To: mailto:MS_Access_Professionals%40yahoogroups.com
> > Sent: Friday, 15 March 2013, 15:44
> > Subject: [MS_AccessPros] Re: getting protected access data into excel
> >
> >
> > Â
> >
> > Phil
> >
> > This took some time for me to figure out. Thanks for the Challenge! When you build your connection to the ULS database you have to also tell it where the MDW file is.
> >
> > 1. Put in your user name and password on the Connection tab.
> > 2. Click on the All tab in the Data Link Properties.
> > 3. Find the Jet OLEDB:System database and edit the value. Put the full path to your MDW as the value.
> >
> > That should let you link up.
> >
> > Regards,
> > Bill Mosca, Founder - MS_Access_Professionals
> > http://www.thatlldoit.com/
> > Microsoft Office Access MVP
> > https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E
> > My nothing-to-do-with-Access blog
> > http://wrmosca.wordpress.com/
> >
> > --- In mailto:MS_Access_Professionals%40yahoogroups.com, "pdk444444" <pdk444444@> wrote:
> > >
> > > I am trying to run a query against by access database in an excel sheet but am running into security issues
> > >
> > > my db is a .mdb with user level security
> > >
> > > in excel I have selected the 'get external data from access' option which takes me into the 'data link properties' panel.
> > >
> > > When i try the 'test connection' option it returns an error saying it can't find the wif
> > >
> > > How and do i specify which wrkgrp file to use?
> > >
> > > I have used this option with an unprotected db and it works fine.
> > >
> > > any help would be very much appreciated.
> > >
> > > cheers
> > >
> > > Phil
> > >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
> [Non-text portions of this message have been removed]
>
> [Non-text portions of this message have been removed]
>
>
>
>
> [Non-text portions of this message have been removed]
>

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (9)

Recent Activity:

• New Members 1

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us Feedback

.

__,_._,___