AboutScottgem Expertise I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.
Experience I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.
Organizations Author of Microsoft Office Access 2007 VBA Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing
Question I have written a query which when opened returns all the table names in an mdb.file. This works fine. I called it QYForTables. It has the path and file name to the mdb file hard coded.
SELECT MSysObjects.Name
FROM MSysObjects IN 'C:\documents and settings\alan\desktop\bcodesample.mdb'
WHERE (((Left([Name],4))<>"MSys") AND ((MSysObjects.Type)=1) AND ((Left([Name],1))<>"~"))
ORDER BY MSysObjects.Name;
Now I want to run it from an event. So I need to build a string to do a Docmd.RunQuery "SQLStr". However it seems that this command cant deal with a "Select" SQL. The primary reason for wanting to do this is to make the path and file name programmable (from a textbox entry) to get a list of tables in the target mdb file (Which is not the current db) into a combobox.
Help would be appreciated
I flatter myself that I am quite good at VBA but this defeats me.
Answer First, when using embedded SQL you have to buid the SQL statement using concatenation. I'm not sure whether you have done that or not. So if you are entering the path to the MDB file you would do something like this:
