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 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:
Second, If the end result is to populate a combobox on that form, after you have generated the correct SQL state, just use:
Me.comboname.RowSource = strSQL
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
---------- FOLLOW-UP ----------
QUESTION: Many thanks for the prompt reply. This is the
Sub as it now stands.
Private Sub TBoxDbToUse_AfterUpdate()
Dim strSQL
strSQL = "SELECT MSysObjects.Name "
strSQL = strSQL & "FROM MSysObjects IN '" & Me.TBoxDbToUse & "' "
MsgBox strSQL ' This shows the correct path and file name - so far so good
DoCmd.OpenQuery strSQL ' This always crashes with error 7874
End Sub
The 7874 error is apparently because the RunSQL and openSQL methods are only for Action queries and can't be used with a Select query.
Whats my next step please.
regards Alan.
ANSWER: I told you. Since the reason you want to do this is to populate a combobox with the list of tables, then just assign the strSQL variable as the RowSource of your combo.
You are correct that DoCmd.RunSQL or CurrentDB.Execute will only run action queries. And the OpenQuery method will only open saved querys.
If you want to run this as a query, then you would need to assign the SQL statement to a QueryDef to define it as a saved query before using DoCmd.OpenQuery.
But, again, if your ultimate goal is to use it as a rowsource, just assign it that way.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
---------- FOLLOW-UP ----------
QUESTION: Thanks for putting me on the right track. Its all solved now and working well.
Just for the record this is how I finished up
Option Compare Database
Option Explicit
Private Sub BTNBrowseforDB_Click()
Dim RemoteDBPath
