VBA function declaration to create a query that gets MSysObjects in Another Database Ms Access Gurus

Help support this site, thank you.

Home Articles Code Tools

VBA to create SQL statement that gets MSysObjects in Another Database

What objects are in another database?

Here is VBA code you can use in your projects. After constructing the SQL, you can create a query or use the SQL to open a recordset.

Use VBA to create SQL that gets MSysObjects in Another Database

Quick Jump

Download VBA Reference Backstory Share Donate

Goto Top  

Download

Download zipped BAS file you can import into your VBA projects. mod_GetSQL_MSysObjects_AnotherDatabase_s4p.zip

If you have trouble with a download, you may need to unblock the ZIP file, aka remove Mark of the Web, before extracting the file. Here are steps to do that: https://msaccessgurus.com/MOTW_Unblock.htm

Goto Top  

VBA

Standard module 

' module name: mod_GetSQL_MSysObjects_AnotherDatabase_s4p
'*************** Code Start ***************************************************
' Purpose  : Function to create and return SQL to link MSysObjects in another database
' Author   : crystal (strive4peace)
' Code List: www.msaccessgurus.com/code.htm
' This code: https://msaccessgurus.com/VBA/SQL_MSysObjects_AnotherDatabase.htm
' LICENSE  :
'   You may freely use and share this code, but not sell it.
'   Keep attribution. Mark your changes. Use at your own risk.
' Then use the SQL to create a saved query or to open a recordset
'--------------------------------------------------------------------------------
'                              GetSQL_MSysObjects_AnotherDatabase_s4p
'--------------------------------------------------------------------------------'
Public Function GetSQL_MSysObjects_AnotherDatabase_s4p( _ 
   psPathFile As String _ 
   ) As String 
'230401 strive4peace
   Dim sSql As String 
   
   sSql =  "SELECT Q.* FROM MSysObjects as Q " _ 
      &  " In '" & psPathFile &  "' ;"
   
   GetSQL_MSysObjects_AnotherDatabase_s4p = sSql 
   
End Function 
'--------------------------------------------------------------------------------
'                             testSQL_MSysObjects_AnotherDatabase_s4p
'--------------------------------------------------------------------------------'
Sub testGetSQL_MSysObjects_AnotherDatabase_s4p() 
   Dim sPathFile As String _ 
      ,sSql As String 
   
   sPathFile =  "C:\MyPath\MyFilename.accdb"    '------------ customize
   
   'Call GetSQL_MSysObjects_AnotherDatabase_s4p
   sSql = GetSQL_MSysObjects_AnotherDatabase_s4p(sPathFile) 

   Debug.Print sSql 

   MsgBox sSql,, "done"
End Sub 
'*************** Code End *****************************************************
' Code was generated with colors using the free Color Code add-in for Access.

Goto Top  

Reference

Microsoft

QueryDef.SQL property (DAO)

Goto Top  

Backstory

Its good to be able to find out what Access Objects are in another database. This SQL can tell you.

If you then want to make a query, VBA code to make a query is posted here: https://msaccessgurus.com/VBA/Query_Make.htm

If you like this page, please let me know. Donations, big and small, are appreciated.

Share with others

Here's the link for this page in case you want to copy it and share it with someone:

https://msaccessgurus.com/VBA/SQL_MSysObjects_AnotherDatabase.htm

or in old browsers:
http://www.msaccessgurus.com/VBA/SQL_MSysObjects_AnotherDatabase.htm

Get Help with Access and maximising the power of queries

Let's connect and team-develop your application together. I teach you how to do it yourself. My goal is to empower you.

While we build something great together, I'll pull in code and features from my vast libraries as needed, cutting out lots of development time. I'll give you lots of links to good resources.

Access is great at many things and can link to data in all kinds of different places. Access is access! I'd love to help you. Email me at training@msAccessGurus.com

~ crystal

the simplest way is best, but usually the hardest to see

Goto Top