Question 2: Pre-made query access in VBA (1 Viewer)

[kevygee]

I would really like to populate a combobox with a list of queries. This would allow the user to add a group of data into a form's subform all at once. Although I have no clue how to use the pre-made queries from VBA or even how to get access to them via a combobox.

Any ideas? If I didn't make myself clear, I can explain further. Thanks in advance!

 

[ghudson]

Set this as the control source of your combo box...

SELECT [MSysObjects].[Name]
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") AND ((MSysObjects.Type)=5))
ORDER BY MSysObjects.Name;

...to get a listing of all your queries.

 

[kevygee]

That's amazing. So MSysObjects is just the table that holds the meta information on the database? Never knew about it. One more follow up question though. So now the user can select the query name, but how do I get to the SQL behind that? I found out how to show the system tables and it's not a field in that table (MSysObjects). I would like to throw the a select query into an insert statement for a multiple row insert.

I know it's an object, and so I looked to see if there was a query object that I could use some methods on, but I couldn't find any. Am I going down the right path? Any ideas on how to use this to insert multiple rows into a table?

 

[ghudson]

I am not sure for I have never attempted that.

You might have to hard code the SQL in a VB routine. Let the user make a choice selection in a combo box and use a Select Case to run the right SQL.

Do not mess with any MSys tables. You will corrupt your db if you try to do anything other than reading the system tables.