Run query in datashet view from VB (using SQL) | Needs Regular Fix | | Join Date: Oct 2007 Location: Hull,UK
Posts: 309
| | |
Good day,
got another strange and wonderful question for y'all.
I have a listbox with dynamic rowsource (SQL) fed by VB.
what i would like to do is allow the user to double click the listbox and open the data in datasheet mode.
effectively string the SQL from the rowsource property. but im not sure of how to open it like this...
Cheers
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 16,173
| | | re: Run query in datashet view from VB (using SQL)
Action queries can be opened and run. SELECT queries cannot be opened as simple SQL without creating an (or updating an existing) QueryDef.
| | Needs Regular Fix | | Join Date: Oct 2007 Location: Hull,UK
Posts: 309
| | | re: Run query in datashet view from VB (using SQL)
If i create a querydef (on the double click) would i need to save the query then? and then delete it when done?
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 16,173
| | | re: Run query in datashet view from VB (using SQL)
You could try this, along with a template QueryDef object you could copy from, but it does stop your database from working as a multi-user object.
| | Needs Regular Fix | | Join Date: Oct 2007 Location: Hull,UK
Posts: 309
| | | re: Run query in datashet view from VB (using SQL)
That concerns me, not for this particular project but possibly for others. in the past i have used an ID number (staffid for logon) that prefixes the query name.
is there a better way then!?
Dan
|  | Member | | Join Date: Jun 2009
Posts: 60
| | | re: Run query in datashet view from VB (using SQL)
how is your sql being compiled for the listbox.
| | Needs Regular Fix | | Join Date: Oct 2007 Location: Hull,UK
Posts: 309
| | | re: Run query in datashet view from VB (using SQL)
i have a toggle button that is used and it also strings in a a value from another listbox, all in VB of course.
| | Member | | Join Date: Jun 2009
Posts: 60
| | | re: Run query in datashet view from VB (using SQL)
ok so when u say u want the "data" to open in datasheet mode. which data do you mean? is the form itself databound and that's the data you want in datasheet view? or a single column of data from the listbox itself?
btw - what is the ultimate user purpose of the data? there might be a better aproach..
| | Needs Regular Fix | | Join Date: Oct 2007 Location: Hull,UK
Posts: 309
| | | re: Run query in datashet view from VB (using SQL)
the data shows the result of a query in listbox form, to make this easily exportable, i would like to simply run the same query (SQL) in datasheet view so that the user can just copy and paste into their desired secondary software.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 16,173
| | | re: Run query in datashet view from VB (using SQL)
Quote:
Originally Posted by Dan2kx  That concerns me, not for this particular project but possibly for others. in the past i have used an ID number (staffid for logon) that prefixes the query name.
is there a better way then!?
Dan The problem with that approach (and I've suffered from it myself because I didn't appreciate this when I designed my processes) is that when changing the design of objects in a database it sets the mode of the database to locked for editing.
I don't believe there's much better approaches. The standard is to have personal copies of the Front-End for each user. Messy, but practicable for most purposes.
| | Needs Regular Fix | | Join Date: Oct 2007 Location: Hull,UK
Posts: 309
| | | re: Run query in datashet view from VB (using SQL)
i usually use FE/BE as well, not in this scenario tho, not worth the effort. this particular question may be something i would want to use again.
Thanks
Dan
| | Member | | Join Date: Jun 2009
Posts: 60
| | | re: Run query in datashet view from VB (using SQL)
Ok I had a meeting to run to a meeting or I would have responded earlier - Try This... it creates a temporary table with your data in it and opens it for the user.
first create a table called "ViewList" don't add any columns or data, just make sure the object exists... ENJOY! -
DoCmd.SetWarnings False
-
Dim sql As String
-
-
DoCmd.DeleteObject acTable, "ViewList"
-
sql = "select * into ViewList from(" & Left(Me.List3.RowSource, Len(Me.List3.RowSource) - 2) & ")"
-
-
DoCmd.RunSQL (sql)
-
DoCmd.OpenTable "ViewList", acViewNormal
-
DoCmd.SetWarnings True
-
| | Needs Regular Fix | | Join Date: Oct 2007 Location: Hull,UK
Posts: 309
| | | re: Run query in datashet view from VB (using SQL)
Rather than insert into a table (which would involve editting the SQL) i can use the example to modify a query in a similar way, that way i can run the SQL as is.
i'll post an example later on when i get the chance,
Cheers
| | Needs Regular Fix | | Join Date: Oct 2007 Location: Hull,UK
Posts: 309
| | | re: Run query in datashet view from VB (using SQL)
Here is my solution to the problem.. - Private Sub lstMain_DblClick(Cancel As Integer)
-
Dim qdef As QueryDef
-
On Error GoTo eTrap
-
Set qdef = CurrentDb.CreateQueryDef("qryViewData", Me.lstMain.RowSource)
-
DoCmd.OpenQuery "qryViewData", acViewNormal
-
Exit Sub
-
eTrap:
-
Select Case Err.Number
-
Case 3012
-
DoCmd.Close acQuery, "qryViewData"
-
DoCmd.DeleteObject acQuery, "qryViewData"
-
Resume
-
End Select
-
End Sub
Hows that then?
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 16,173
| | | re: Run query in datashet view from VB (using SQL)
This should work for you, but it will still put the database into Edit mode.
| | Member | | Join Date: Jun 2009
Posts: 60
| | | re: Run query in datashet view from VB (using SQL)
And just to clarify, my code suggestion is pretty simple and does not overtask the system at all. Access is designed to allow users to quickly create and delete objects. In general you shouldn't be afraid to let your code format sql strings to make them useful in other parts of your application.
- regardless... I'm glad you found a solution that works for you : )
| | Needs Regular Fix | | Join Date: Oct 2007 Location: Hull,UK
Posts: 309
| | | re: Run query in datashet view from VB (using SQL)
Quote:
Originally Posted by NeoPa This should work for you, but it will still put the database into Edit mode. What issues does this cause then? forgive my ignorance.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 16,173
| | | re: Run query in datashet view from VB (using SQL)
It means that no-one is able to make any design changes (including creating or changing a QueryDef) to the database until the session that caused it to be locked terminates. This is particularly (though not exclusively) problematic if trying to run as a multi-user database.
| | Needs Regular Fix | | Join Date: Oct 2007 Location: Hull,UK
Posts: 309
| | | re: Run query in datashet view from VB (using SQL)
at what point would the lock be released then?
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 16,173
| | | re: Run query in datashet view from VB (using SQL)
Quote:
Originally Posted by NeoPa ... until the session that caused it to be locked terminates. This is generally when either the database is closed in that session, or the whole Access application that it was running in is closed.
Does that make better sense?
| | Needs Regular Fix | | Join Date: Oct 2007 Location: Hull,UK
Posts: 309
| | | re: Run query in datashet view from VB (using SQL)
So if two people were using the same mdb database, and the createquerydef code runs it locks the other person from creating queries or anything else until the first user closes? or when the code stops?.
this for me isnt a problem with this instance though, and if it was multi user i would have mde file distribution. am i understanding correctly though?
Thanks for your patience (LOL)
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 16,173
| | | re: Run query in datashet view from VB (using SQL)
You sort of have it Dan.
If your database isn't likely to suffer from this then it's fine to use it.
However, I'm not sure where the stopping of code came into anything. Code stops all the time within a database application. This does not release the session and doesn't free up a database that's locked for editing.
It is only available again fully when either : - The database (locked for editing) is closed.
- The Access application (with database locked for editing) is terminated.
| | Needs Regular Fix | | Join Date: Oct 2007 Location: Hull,UK
Posts: 309
| | | re: Run query in datashet view from VB (using SQL)
Ok thanks, it would be much easiler if Access just did what i originally wanted (LOL)
Dan
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 16,173
| | | re: Run query in datashet view from VB (using SQL)
I agree absolutely. Some of the dancing around is a pain. ... and still not as effective as the simple facility to run a SELECT query from some SQL.
|  |
Similar Microsoft Access / VBA bytes | | |  Forums
Visit our community forums for general discussions and latest on Bytes
/bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 229,155 network members.
|
