Open a query, run in acces, in Excell 5

[Rmck87]

Ok, i have a form that on the users selections runs and creates a query. Then it saves it. Then what i want to be able to do. Is when the user opens Excell he/she should be able to open the Query that was saved before in access. If anyone knows how to do this please let me know. Or if i am in the wrong im sorry, and please direct me to a better forum for this question. Thanks in advance!

-Ryan

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[Rmck87]

It still doesn't like it Geoff. But as long as it doesn't make a difference, i got it to work with ETID's idea. Thanks all for the help.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[Rmck87]

Geoff, i have a question, you never defined 'icol' What is it?

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[xlbo]

just an incrementing variable - as the loop progresses, it allows the data to be written to different columns rather than overwriting. Not sure what the problem is as I have run this code successfully many times. I'm pretty sure it'll be the references. What version of excel and access are you using ?

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Rmck87]

Access 2000, and Excell 2002, that could be the problem!

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[xlbo]

Ok - these are the references I have:
Microsoft Excel object library
OLE automation
Microsoft Office object library
Microsoft DAO 3.6 object library

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Rmck87]

It is still saying that 'icol' is not defined.

I also have another question for you guys. Lets say i were to save the query as a different name every time i ran the query in access. Would i have to go through this import thing every time i wanted to open the query in Excel?

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[xlbo]

aaaaah - you have "option explicit" set
Just enter
dim iCol as integer
after the other dim line

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[xlbo]

aaaaah - you have "option explicit" set
Just enter
dim iCol as integer
after the other dim line

In respect to the query name change - all you would have to do is change
Set rst = dbs.OpenRecordset("QueryName&quot
to reflect the new name of the query

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[ETID]

Or put the parameters in MSQuery..not access.

 

[Rmck87]

xlbo: hmmmmm... That would require me having to change the code every time i wanted to bring up the query.

ETID: I think i understand what you are saying, but its a little light in the info. Are you saying that i would just put the query criteria in MSQuery not in the query in access? Wouldn't that require changing code and risking losing information? If thats what you are saying?


-I want to be able to open somehting that eveyrtime i open it, i will go in and open the query that i saved it as, and it will run, without having to change, copy, or move code. and without having to reimport the query from access everytime. Is this possible?

Update on xlbo's idea for importing query: It works now. But is bringing up strange sharp symbols(########) in the date entry fields, which should not be happening.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[xlbo]

probably just need to expand the cell widths to get rid of the #######

As to the chaging query name - that makes life very difficult - if there is a default format then you could loop thru all the queries and find the one you want by checking the 1st x letters in the query name but why (and how) does the query name change. Seems like you are making a rod for your own back with that....

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[ETID]

If XLBo's Code is producing an instance of MsQuery then maybe...


but if you went the non code route...you can right click in the returned data and edit the query (look for a check box that says always use MSquery instead of wizard, if wizard pops up.)


then in MSQuery interface there is a criteria area ...it's in the menu.


You can link parameters in your query directly to cells in the workbook if you want then if the user changes these values the query refreshes automatically...more on that if needed.

 

[Rmck87]

xlbo: lol. A rod, no, but a mess, yes!!! Whenever the user runs the query i am going to program it so that a message box comes up asking if the user would like to save the query. Then if they select yes, the user will get to input what they want to save the query as. Then they will be able to go into Excel and pick their query that they just saved (along with any others that they want.) and compare or print the query/queries.

ETID: your idea might just work better, but i dont see how it will automatically refresh if the name of the query is changing? Read xlbo's message i explained what i want done in it.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[luceze]

What about using an input box so the user could just select the query that they want?

 

[Rmck87]

luceze: Then when they did that the code would run based on the query selected, and the query would be refreshed? Are you basing your idea off of xlbo's idea or ETID's? Also, how would i use an input box in Excel?

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[ETID]

Ah..I am going on the assupmtion that Linked query in excel would be the only query but it would be dynamic enough to eliminate the need for several remote querys.

 

[Rmck87]

ETID: So your saying that it can automatically refresh? or that what you are thinking won't work?

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[luceze]

Using xlbo's idea you could add this line to your code.

MyQuery = InputBox("Enter name of query.", "Query Name&quot

then you would change this line of xlbo's code to

Set rst = dbs.OpenRecordset(MyQuery)

Of course they would have to know the name of the query.

 

[Rmck87]

Luceze: Could i make the inputbox into something that would have like a list of the querys? or could i have a browse... button on it?


One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[xlbo]

I'm thinking you're gonna have to do something like:

Using code, loop through all the queries in the access database and return them to a dropdown box so the user can pick one to use

For this, you will need to have set your references.
sheet1 will get the returned data
Sheet2 should have a combobox called cbQueries (from the controls toolbox)

paste these into a standard module:

Sub GetQueries()
Dim dbs As Database
Set wks = DBEngine.CreateWorkspace("", "admin", "", dbUseJet)
Set dbs = wks.OpenDatabase("c:\Home\Access\GB_Universe.mdb", True) Sheets("Sheet2&quot.cbQueries.Clear
For Each qdf In dbs.querydefs
Sheets("Sheet2&quot.cbQueries.AddItem (qdf.Name)
Next
dbs.Close
Set dbs = Nothing
End Sub

Sub execute()
Dim dbs As Database, rst As Recordset, wks As Workspace, ws As Worksheet
Dim sQuery As String
sQuery = Sheets("Sheet2&quot.cbQueries.Text

Set wks = DBEngine.CreateWorkspace("", "admin", "", dbUseJet)
Set dbs = wks.OpenDatabase("c:\Home\Access\GB_Universe.mdb", True)
Set rst = dbs.OpenRecordset(sQuery)
'If Err.Number < 0 Then GoTo ErrorExit
Set ws = Worksheets(&quot;sheet1&quot
With ws
.Activate
.Cells.ClearContents

For iCol = 1 To rst.Fields.Count
.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
Next

.Cells(1, 1).CurrentRegion.Font.Bold = True
.Cells(2, 1).CopyFromRecordset rst
End With
'End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
wks.Close
Set wks = Nothing
Set ws = Nothing
Exit Sub
ErrorExit:
' error trapping...
End Sub

Run &quot;GetQueries&quot; - this will populate the combobox with a list of all the queries currently saved in the database

Once a query has ben selected, run &quot;Execute&quot; to return the data to sheet1

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244