Expert: Scottgem - 6/28/2009

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:

strSQL = "SELECT MSysObjects.Name "
strSQL = strSQL & "FROM MSysObjects IN '" & Me.filepath & "' "

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

TBoxDbToUse.SetFocus

TBoxDbToUse.text = LaunchCD(Me)

End Sub



Private Sub ComboFieldtoUse_AfterUpdate()
DoCmd.SetWarnings False
DoCmd.OpenQuery "QYUpdateOptions"
DoCmd.SetWarnings True
DoCmd.OpenForm "FMMainScan"
DoCmd.Close acForm, "FMOptions"

End Sub

Private Sub ComboTabletToUse_AfterUpdate()
Dim RemoteDBPath, RemoteTablename As String

If TableExists("TBLinked") Then
DoCmd.RunSQL "DROP TABLE TBLinked"
End If

RemoteDBPath = TBoxDbToUse

RemoteTablename = ComboTabletToUse

DoCmd.TransferDatabase acLink, "Microsoft Access", RemoteDBPath, acTable, RemoteTablename, "TBLinked"
DoCmd.Close acQuery, "QYFindTables"
Me.ComboFieldtoUse.Visible = True
Me.ComboFieldtoUse.SetFocus
Me.ComboTabletToUse.Enabled = False


End Sub

Private Sub TBoxDbToUse_AfterUpdate()

Dim strSQL As String
Dim QDF As QueryDef
DoCmd.Close acQuery, "QYFindTables"

 
DoCmd.DeleteObject acQuery, "QYFindTables"
 

strSQL = "SELECT MSysObjects.Name "
strSQL = strSQL & "FROM MSysObjects IN '" & Me.TBoxDbToUse & "' "
strSQL = strSQL & "WHERE (((Left([Name], 4)) <> 'MSys') And ((MSysObjects.Type) = 1) And ((Left([Name], 1)) <> '~'))"

Set QDF = CurrentDb.CreateQueryDef("QYFindTables", strSQL)
DoCmd.OpenQuery QDF.Name


Me.ComboTabletToUse.RowSource = strSQL
Me.ComboTabletToUse.Visible = True
Me.ComboTabletToUse.SetFocus
Me.BTNBrowseForDB.Visible = False
Me.TBoxDbToUse.Enabled = False

End Sub



Thanks again. Alan

Answer
Glad you got it working, but I'm not sure why you are doing this:

Set QDF = CurrentDb.CreateQueryDef("QYFindTables", strSQL)
DoCmd.OpenQuery QDF.Name

I don't see where its necessary. I can understand doing it as a check to make sure the SQL will work, but then I would comment it out.

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA