I have been developing for some time an Access database which has been split into the tables and the forms as separate files. The forms file is distributed to remote computers.
I have found that by I can get good performance by bringing all the relevant data together using a query executed by the server and putting it into a local table for further queries to be run on or for reports or forms to make reference to.
An example of this below.
Set DB1 = DBEngine(0).OpenDatabase("\\Agspsrv50\genimp\SHARE1\GPL\Annualdb Server.mdb")
SQL = "SELECT DISTINCT Traits.RoundTo, Series.Year, Series.SeriesNo, Traits.TraitCode, Trials.TrialNo, Varieties.TestName, Series.Name, Trials.Rego, Results.Results, Traits.ShortDesc INTO qry_" & QueryName & " IN 'C:\Annual Database\Annualdb Interface.mdb' " _
& "FROM ((AWBType2 INNER JOIN Traits ON AWBType2.AWBType2 = Traits.AWBType2) INNER JOIN (TraitGroups INNER JOIN TraitLists ON TraitGroups.TraitGroupID = TraitLists.TraitGroupID) ON Traits.TraitCode = TraitLists.TraitCode) INNER JOIN (ResultDetails INNER JOIN (Series INNER JOIN ((((Samples INNER JOIN Trials ON Samples.TrialID = Trials.TrialID) INNER JOIN Varieties ON Samples.VarID = Varieties.VarietyID) INNER JOIN (Products INNER JOIN ProductLists ON Products.ProductID = ProductLists.ProductID) ON Samples.SampIDBarcode = ProductLists.SampleID) INNER JOIN Results ON Samples.SampIDBarcode = Results.SampleID) ON Series.SeriesID = Trials.SeriesID) ON ResultDetails.ResultDetailID = Results.ResultDetailID) ON Traits.TraitCode = ResultDetails.TraitCode " _
& "WHERE Samples.SampleTypeID = 1 AND TraitLists.TraitGroupID=""" & TraitGroup & """ AND (" & ProdList & ")" & IIf(Temp <> "", " AND (" & Temp & ")", "")
One thing to take note of being...
SELECT... INTO qry_" & QueryName & " IN 'C:\Annual Database\Annualdb Interface.mdb'...
What I like about this is that makes the server do the work be retrieving the data but from that point onward forms and reports can make reference to the local data that is created.
Is it possible to do something similar to this using ADO in an ADP Access database to connect to an SQL Server and create a local table or recordset to use?
Honestly while I have done a course on programing, right now I'm well beyond anything that I've been taught and I'm just throwing things together in a way that seems to work so I'm not sure if maybe I'm looking at this in completely the wrong way.