By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,224 Members | 1,523 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,224 IT Pros & Developers. It's quick & easy.

Run query in datashet view from VB (using SQL)

100+
P: 365
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
Jun 23 '09 #1
Share this Question
Share on Google+
23 Replies


NeoPa
Expert Mod 15k+
P: 31,707
Action queries can be opened and run. SELECT queries cannot be opened as simple SQL without creating an (or updating an existing) QueryDef.
Jun 23 '09 #2

100+
P: 365
If i create a querydef (on the double click) would i need to save the query then? and then delete it when done?
Jun 23 '09 #3

NeoPa
Expert Mod 15k+
P: 31,707
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.
Jun 23 '09 #4

100+
P: 365
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
Jun 23 '09 #5

yarbrough40
100+
P: 320
how is your sql being compiled for the listbox.
Jun 23 '09 #6

100+
P: 365
i have a toggle button that is used and it also strings in a a value from another listbox, all in VB of course.
Jun 23 '09 #7

yarbrough40
100+
P: 320
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..
Jun 23 '09 #8

100+
P: 365
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.
Jun 23 '09 #9

NeoPa
Expert Mod 15k+
P: 31,707
@Dan2kx
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.
Jun 23 '09 #10

100+
P: 365
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
Jun 23 '09 #11

yarbrough40
100+
P: 320
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!
Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings False
  2. Dim sql As String
  3.  
  4. DoCmd.DeleteObject acTable, "ViewList"
  5. sql = "select * into ViewList from(" & Left(Me.List3.RowSource, Len(Me.List3.RowSource) - 2) & ")"
  6.  
  7. DoCmd.RunSQL (sql)
  8. DoCmd.OpenTable "ViewList", acViewNormal
  9. DoCmd.SetWarnings True
  10.  
Jun 23 '09 #12

100+
P: 365
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
Jun 24 '09 #13

100+
P: 365
Here is my solution to the problem..

Expand|Select|Wrap|Line Numbers
  1. Private Sub lstMain_DblClick(Cancel As Integer)
  2. Dim qdef As QueryDef
  3. On Error GoTo eTrap
  4.     Set qdef = CurrentDb.CreateQueryDef("qryViewData", Me.lstMain.RowSource)
  5.     DoCmd.OpenQuery "qryViewData", acViewNormal
  6. Exit Sub
  7. eTrap:
  8.     Select Case Err.Number
  9.         Case 3012
  10.             DoCmd.Close acQuery, "qryViewData"
  11.             DoCmd.DeleteObject acQuery, "qryViewData"
  12.             Resume
  13.     End Select
  14. End Sub
Hows that then?
Jun 24 '09 #14

NeoPa
Expert Mod 15k+
P: 31,707
This should work for you, but it will still put the database into Edit mode.
Jun 24 '09 #15

yarbrough40
100+
P: 320
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 : )
Jun 24 '09 #16

100+
P: 365
@NeoPa
What issues does this cause then? forgive my ignorance.
Jun 24 '09 #17

NeoPa
Expert Mod 15k+
P: 31,707
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.
Jun 24 '09 #18

100+
P: 365
at what point would the lock be released then?
Jun 24 '09 #19

NeoPa
Expert Mod 15k+
P: 31,707
@NeoPa
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?
Jun 25 '09 #20

100+
P: 365
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)
Jun 25 '09 #21

NeoPa
Expert Mod 15k+
P: 31,707
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 :
  1. The database (locked for editing) is closed.
  2. The Access application (with database locked for editing) is terminated.
Jun 25 '09 #22

100+
P: 365
Ok thanks, it would be much easiler if Access just did what i originally wanted (LOL)

Dan
Jun 25 '09 #23

NeoPa
Expert Mod 15k+
P: 31,707
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.
Jun 25 '09 #24

Post your reply

Sign in to post your reply or Sign up for a free account.