How to query a backEnd database from a frontend database with a Sql script

[informer]

Hi

To query a backEnd database from a frontend database with Sql script, proceed as follows:

Private Sub Form_Load()
          
        Dim strPathDb As String, strDbName As String, strRsetTable As String, strBackEndLocation As String, strDBPSW As String
         
         strPathDb = Application.CurrentProject.Path & "\"
         strDbName = "test_be.accdb"
     
         strBackEndLocation = strPathDb & strDbName
         
         strDBPSW = ""
         Me.RecordSource = "SELECT * FROM tblTest IN'' [MS Access;PWD=" & strDBPSW & ";database=" & strBackEndLocation & "]"         
     end sub

Or go there http://www.utteraccess.com/forum/index.php?showtopic=2037433&view=findpost&p=2601389

 

[Ranman256]

You can just make a query.

 

[CJ_London]

or in a public module have

 dim BE as DAO.Database
  
 public function openBE() 'call when db opened
     Set BE = DBEngine.OpenDatabase(DBPath , 0, 0, "MS Access" )
 end function

and in your form load event you just need

me.recordset=BE.openrecordset("SELECT ....")

benefit is the back end is open persistently so lower time overhead on getting the records, whereas your method isn't, although fine for occasional access to other data.