We are currently running an Access database with roughly 15 users on a LAN. The DB is split with a front end and backend on the server and users are experiencing extremely slow response times. Database size both back end and front end is only about 8mb total in size. Server is NT 4.0 based and up to this point we have not had problems of this type. Can anyone give us a place to start troubleshooting?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Database crawls on LAN 3
- Thread starter bill7r
- Start date
I would assume you have tried compacting the databases. If you have a lot of subforms, this can slow down performance greatly. If this is the case, you might experiment with using VB to change the control source of a single subform as the user clicks on different buttons. Also, comboboxes that have a thousands of records in their list can slow down the forms. You can help this problem by setting the rowsource property using the 'on enter' of the combo box. [sig]<p>Mike Rohde<br><a href=mailto:rohdem@marshallengines.com>rohdem@marshallengines.com</a><br>[/sig]
As a test move the backend to your local drive and redirect your frontend. Is it stll very slow ? [sig]<p>Bill Paton<br><a href=mailto:william.paton@ubsw.com>william.paton@ubsw.com</a><br><a href= Check out my website ![/sig]
-
1
- #4
MichaelRed
Programmer
Since it is already split, place the front end on the users systems. Relieves the network of the traffic of loading forms/modules/reports ...
Try moving the queries to the BackEnd/server. Some additional work here, but the return is generally quite good. Rember that SQL builds query sets/results by first assembling the maximum recordset, then deletes records which do not match the relationships and criteria. With the query residing on the Local copy of the db, ALL of this traffic goes across the Network. [sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]
Try moving the queries to the BackEnd/server. Some additional work here, but the return is generally quite good. Rember that SQL builds query sets/results by first assembling the maximum recordset, then deletes records which do not match the relationships and criteria. With the query residing on the Local copy of the db, ALL of this traffic goes across the Network. [sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]
-
1
- #6
I had a problem similar to this. One day my database was working fine the next day it took nearly ten seconds to load a single form. I assumed this was because I had linked my tables to a back end database, but I found out that it was because of my virus scan. I have Command Antivirus for WindowsNT v4.59.2. I disabled the dynamic virus protection and everything is working fine again. If you have this version of Command Antivirus you might try this.
Sera [sig][/sig]
Sera [sig][/sig]
I am reviewing posts in order to improve performance.
A question to MichaelRed (Programmer) Oct 13, 2000:
I moved my query to the BackEnd/server as you suggested but linking seems to be allowed on tables only.
Am I missing the point :-I ?
Another question to Sera, Oct 13, 2000:
Can you suggest how to detect and deactivate the virusscan from Visual Basic script? (My company has recently installed a standard set of plattforms and applies very strict rules, B-( ).
Hakan
Osaka 2001-01-13
A question to MichaelRed (Programmer) Oct 13, 2000:
I moved my query to the BackEnd/server as you suggested but linking seems to be allowed on tables only.
Am I missing the point :-I ?
Another question to Sera, Oct 13, 2000:
Can you suggest how to detect and deactivate the virusscan from Visual Basic script? (My company has recently installed a standard set of plattforms and applies very strict rules, B-( ).
Hakan
Osaka 2001-01-13
Funny how these things resurface. nrjhano: Nice work searching for the existing material rather than reposting the same question!
MRed: I know you're out there--I never thought of putting the queries in the tables file--it's a cool idea! (Kind of a poor man's client-server. . .) How do you run them from the front end? docmd.runquery "name"? Is there a linking mechanism that I'm not aware of?
Also: What's a good way to surreptitiously load user forms in the backround--in the Autoexec macro? (But wouldn't this method make the initial menu appearance subsequent to loading every other form?)
This is definitely an important issue for me. I've got a great application out there for users (and it's fairly streamlined and the number of rcds is <500 for each instance of the app) but our LAN traffic can be heavy and speed is paramount.
Thanks for any input.
MRed: I know you're out there--I never thought of putting the queries in the tables file--it's a cool idea! (Kind of a poor man's client-server. . .) How do you run them from the front end? docmd.runquery "name"? Is there a linking mechanism that I'm not aware of?
Also: What's a good way to surreptitiously load user forms in the backround--in the Autoexec macro? (But wouldn't this method make the initial menu appearance subsequent to loading every other form?)
This is definitely an important issue for me. I've got a great application out there for users (and it's fairly streamlined and the number of rcds is <500 for each instance of the app) but our LAN traffic can be heavy and speed is paramount.
Thanks for any input.
With a SQL Server or Oracle back end the speed problem is resolved, as long as the other objects are in user PC's. But, correct me if needed, there's nothing to be gained (and perhaps DAO functionality lost) by using ODBC in a native Access system.
Thanks Quehay and Omega36 for your responses. Maybe I begin to understand some aspects of the situation.
Basically, in order for the Query to run on a remote machine a demon of a sort is required (as in unix). I understand this is the background for:
"How do you run them from the front end? docmd.runquery "name"? Is there a linking mechanism that I'm not aware of?"
End citation.
A solution I often return to is to use a WEB-server back-to-back to a database front-end talking to a back-end in the same machine. Some time ago I designed a distributed system like that on a Linux machine with Apache, CGI, Pearl and PostgreSQL. Worked like dream and took me only a few weeks fulltime to implement including installation of Linux. Regretfully, this is possible at pressent, :-( .
I am happy with my database when front and backend runs on the same machine. The ambition has been to avoid any compromises whatsoever from functional point of view. Regretfully, this is not enough.
On a networked mode performance factors appears to be rather difficult to control in Access. Generally speaking a massive amount of knowledge is necessary to exploit all marvelous (yes!) features correctly.
Each obstacle I have come across in Access has tended to take me about a day in average during the last year on leisure time - not taking this particular problem into account, X-) . This is much longer than the WEB solution for some mysterious and somewhat contradictory reasons.
Hakan
Basically, in order for the Query to run on a remote machine a demon of a sort is required (as in unix). I understand this is the background for:
"How do you run them from the front end? docmd.runquery "name"? Is there a linking mechanism that I'm not aware of?"
End citation.
A solution I often return to is to use a WEB-server back-to-back to a database front-end talking to a back-end in the same machine. Some time ago I designed a distributed system like that on a Linux machine with Apache, CGI, Pearl and PostgreSQL. Worked like dream and took me only a few weeks fulltime to implement including installation of Linux. Regretfully, this is possible at pressent, :-( .
I am happy with my database when front and backend runs on the same machine. The ambition has been to avoid any compromises whatsoever from functional point of view. Regretfully, this is not enough.
On a networked mode performance factors appears to be rather difficult to control in Access. Generally speaking a massive amount of knowledge is necessary to exploit all marvelous (yes!) features correctly.
Each obstacle I have come across in Access has tended to take me about a day in average during the last year on leisure time - not taking this particular problem into account, X-) . This is much longer than the WEB solution for some mysterious and somewhat contradictory reasons.
Hakan
MichaelRed
Programmer
"MRed: I know you're out there--I never thought of putting the queries in the tables file--it's a cool idea! (Kind of a poor man's client-server. . .) How do you run them from the front end? docmd.runquery "name"? Is there a linking mechanism that I'm not aware of?"
No. Just set a recordset to the querydef. QueryDef operates on the database, recordset transfers to front end.
MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over
No. Just set a recordset to the querydef. QueryDef operates on the database, recordset transfers to front end.
MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over
I sorry for the delay of my response to MichaelRed. After some search of msdn online library I found following code written in DAO that seems to do the job, 
:
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim fld As DAO.Field
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb"
' Get the QueryDef from the
' QueryDefs collection
Set qdf = db.QueryDefs("Sales by Year"
' Specify the parameter values
qdf.Parameters _
("Forms!Sales by Year Dialog!BeginningDate" = #8/1/1997#
qdf.Parameters _
("Forms!Sales by Year Dialog!EndingDate" = #8/31/1997#
' Open the Recordset
Set rst = qdf.OpenRecordset(dbOpenForwardOnly, dbReadOnly)
' Display the records in the
' debug window
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Loop
' Close the recordset
rst.Close
Now back to some real hard work...:
nrjhano
:Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim fld As DAO.Field
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb"
' Get the QueryDef from the
' QueryDefs collection
Set qdf = db.QueryDefs("Sales by Year"
' Specify the parameter values
qdf.Parameters _
("Forms!Sales by Year Dialog!BeginningDate"
= #8/1/1997#qdf.Parameters _
("Forms!Sales by Year Dialog!EndingDate"
= #8/31/1997#' Open the Recordset
Set rst = qdf.OpenRecordset(dbOpenForwardOnly, dbReadOnly)
' Display the records in the
' debug window
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Loop
' Close the recordset
rst.Close
Now back to some real hard work...:
nrjhano
This is a good way to generate read-only recordets quickly (thanks nrjhano for the code submission!). But it's not a solution for improving form loading speed when forms are based on joins and take longer--the original thread being performance enhancement in a network environment. What's missing in this scenario is allowance for multiuser updates on the same tables. Using a disconnected recordset (i.e. not part of a bound form) as the form control source doesn't allow for record locking or viewing updates made by others (nor does it prevent overwriting their changes).
So the original question of speeding up a front end remains.
Implied obviousness of the solution notwithstanding (pace Msr. Red), the complexities of multiusers and updates are what require a lot of work when creating unbound forms. Using ADO unbound forms appears to be the real answer to speed with a system that is to remain totally Access driven in a networked, multiuser environment (but it's just a lot of development work).
So the original question of speeding up a front end remains.
Implied obviousness of the solution notwithstanding (pace Msr. Red), the complexities of multiusers and updates are what require a lot of work when creating unbound forms. Using ADO unbound forms appears to be the real answer to speed with a system that is to remain totally Access driven in a networked, multiuser environment (but it's just a lot of development work).
MichaelRed
Programmer
nrjhano, quehay, et al
Sorry, I did not realize there wzs any need to expand on my last post. nrjhano's last post is more-or-less what I was attempting to indicate in my brief description. I did not realize that it would be a large hurdle for anyone using Ms. Access to implement this. nrjhano's post is, of course, limited - but that is just the result of the options/choices he has mande in instantiating the record set. After all, it is just a copy from the sample database provided. It can (NEEDS) to be modified to suit his app. This would normally include instantiating the recordset as a dynaset.
This DOES normally produce an improvement in the app performance (reduced time delays and reduced network traffic).
To really address quehay's concern (Network performance improvement), you really need a group approach. Network admin, users, db app design and implementation. Each has contributions to make here. Network admin needs to understand the impact of various network settings on performance. Most networks are set up to optimize e-mail and (possibly) file sharing. For the normal db, this is much less than desireable, so the admin can change the settings to be at least a little more favorable to the db app. Many net admins have some analytical tools which can analyse the network performance and 'suggest' some settings. Most admins don't have these running unless there is a performance issue - which they won't know about unless someone lets them (NICELY!) know. In some (smaller?)) installations, the net admin is not really a professional net admin, and may not even know about the tools (so be even NICER!).
Users are always the first to notice slowing of performance. They also usually know if the performance degredation is associated with a specific function/form. Ask them to watch closely and report directly to both the net admin and the db 'guru' when things go awry.
DB analyst/programmer. You need to monitor the overall process. Know what the users are doing - and what they want! Look at the difference in performance in various situations. Does one user always complain about performance before others? Is is system markedly different from other users? Less memory? Different logon connection? Do 'performance complaints' coincide with the number of records in a often referenced table crossing a threshold? How fast is the database growing anyway? When will it exceed the 'recommended' something (e.g. MS Access table > ~500K records)
There is no panacea for performance. It is just plain hard work. It is a like like Microsoft. It is absurdly easy to 'make something' that works. It takes a LOT of work to then make it work well and efficiently.
Opps. Sorry for the soap box bit.
MichaelRed
redmsp@erols.com
There is never time to do it right but there is always time to do it over
Sorry, I did not realize there wzs any need to expand on my last post. nrjhano's last post is more-or-less what I was attempting to indicate in my brief description. I did not realize that it would be a large hurdle for anyone using Ms. Access to implement this. nrjhano's post is, of course, limited - but that is just the result of the options/choices he has mande in instantiating the record set. After all, it is just a copy from the sample database provided. It can (NEEDS) to be modified to suit his app. This would normally include instantiating the recordset as a dynaset.
This DOES normally produce an improvement in the app performance (reduced time delays and reduced network traffic).
To really address quehay's concern (Network performance improvement), you really need a group approach. Network admin, users, db app design and implementation. Each has contributions to make here. Network admin needs to understand the impact of various network settings on performance. Most networks are set up to optimize e-mail and (possibly) file sharing. For the normal db, this is much less than desireable, so the admin can change the settings to be at least a little more favorable to the db app. Many net admins have some analytical tools which can analyse the network performance and 'suggest' some settings. Most admins don't have these running unless there is a performance issue - which they won't know about unless someone lets them (NICELY!) know. In some (smaller?)) installations, the net admin is not really a professional net admin, and may not even know about the tools (so be even NICER!).
Users are always the first to notice slowing of performance. They also usually know if the performance degredation is associated with a specific function/form. Ask them to watch closely and report directly to both the net admin and the db 'guru' when things go awry.
DB analyst/programmer. You need to monitor the overall process. Know what the users are doing - and what they want! Look at the difference in performance in various situations. Does one user always complain about performance before others? Is is system markedly different from other users? Less memory? Different logon connection? Do 'performance complaints' coincide with the number of records in a often referenced table crossing a threshold? How fast is the database growing anyway? When will it exceed the 'recommended' something (e.g. MS Access table > ~500K records)
There is no panacea for performance. It is just plain hard work. It is a like like Microsoft. It is absurdly easy to 'make something' that works. It takes a LOT of work to then make it work well and efficiently.
Opps. Sorry for the soap box bit.
MichaelRed
redmsp@erols.com
There is never time to do it right but there is always time to do it over
Thanks Quehay and MichaelRed for very enlightening contributions. I sense that you both are covering a lot of ground related to performance.
Yes Quehay, after some thinking and initial work I realized that you are right. After a second thought I realised that 3 query-tables I use to drive combiboxes with address information are updated directly from outlook or possibly from an other frontend. Only one problem. When I tried to generate tables I got myself into formatting trouble:
Dim tblCust As DAO.TableDef
' Create a new TableDef object.
Set tblCust = dbc.CreateTableDef("ActRep_Cust"
With tblCust
' Create fields and append them to the new TableDef object.
' This must be done before appending the TableDef object to
' the TableDefs collection of the Database.
For Each fld In rst.Fields
.Fields.Append .CreateField(fld.Name, fld.Type)
Next 'This assigns column names to a table - seems to works fine but maybe for the wrong reason?
Do Until rst.EOF
For Each fld In rst.Fields
.Fields.Append .CreateField(fld.Value, fld.Type, fld.Size) 'Regrettfully the fld.type does not seem to be compatible with the value type!
'Null values are also a problem. Also size 255 (or 256) was created, maybe default.
Debug.Print fld.Value & ";";
Next
' Debug.Print
rst.MoveNext
Loop
End With
Quehay, I also realised that multi-user update would be difficult to attain with this procedure for the core functions in my application. Your statement: "Using ADO unbound forms appears to be the real answer to speed with a system that is to remain totally Access driven in a networked, multiuser environment (but it's just a lot of development work)." inspired me to look at MS-library and I found following references:
- -
Since I am running short of time I will use the method proposed by MichaelRed as far as it goes and see where it takes me.
Thereafter, I intend to try a clean ADO approach as suggested by Quehay, starting from scratch. Also, I have seen posts elsewere that supports the idea of systematic use of unbound forms.
MichaelRed, the approach you suggest requires a lot of interdisciplinary knowledge that I suspect is very rare indeed. When starting from scratch, I will try to address your proposed reasoning from the beginning including safety and possibly security aspects. My present application was a quick and dirty solution based on purely functional considerations just as you mentioned. My mind was set to visualise the feasability of a practical solution rather than doing a finished product. I attained my objective but this proved to be insufficient. So here I am...
Is anybody aware of ongoing and posted discussions for these two topics?
Hakan
Osaka 2001-03-03
Yes Quehay, after some thinking and initial work I realized that you are right. After a second thought I realised that 3 query-tables I use to drive combiboxes with address information are updated directly from outlook or possibly from an other frontend. Only one problem. When I tried to generate tables I got myself into formatting trouble:
Dim tblCust As DAO.TableDef
' Create a new TableDef object.
Set tblCust = dbc.CreateTableDef("ActRep_Cust"
With tblCust
' Create fields and append them to the new TableDef object.
' This must be done before appending the TableDef object to
' the TableDefs collection of the Database.
For Each fld In rst.Fields
.Fields.Append .CreateField(fld.Name, fld.Type)
Next 'This assigns column names to a table - seems to works fine but maybe for the wrong reason?
Do Until rst.EOF
For Each fld In rst.Fields
.Fields.Append .CreateField(fld.Value, fld.Type, fld.Size) 'Regrettfully the fld.type does not seem to be compatible with the value type!
'Null values are also a problem. Also size 255 (or 256) was created, maybe default.
Debug.Print fld.Value & ";";
Next
' Debug.Print
rst.MoveNext
Loop
End With
Quehay, I also realised that multi-user update would be difficult to attain with this procedure for the core functions in my application. Your statement: "Using ADO unbound forms appears to be the real answer to speed with a system that is to remain totally Access driven in a networked, multiuser environment (but it's just a lot of development work)." inspired me to look at MS-library and I found following references:
- -
Since I am running short of time I will use the method proposed by MichaelRed as far as it goes and see where it takes me.
Thereafter, I intend to try a clean ADO approach as suggested by Quehay, starting from scratch. Also, I have seen posts elsewere that supports the idea of systematic use of unbound forms.
MichaelRed, the approach you suggest requires a lot of interdisciplinary knowledge that I suspect is very rare indeed. When starting from scratch, I will try to address your proposed reasoning from the beginning including safety and possibly security aspects. My present application was a quick and dirty solution based on purely functional considerations just as you mentioned. My mind was set to visualise the feasability of a practical solution rather than doing a finished product. I attained my objective but this proved to be insufficient. So here I am...
Is anybody aware of ongoing and posted discussions for these two topics?
Hakan
Osaka 2001-03-03
Guest_imported
New member
- Jan 1, 1970
- 0
I have a related question.
I have a multi user-database. The programs are local, the data on a server. (It does the same things in NT server and Novell). THe application was written in Access 2000, Service pack 1 and the record sets are DAO.
The problem is that when I open one program on one work station, it is fast. The second one takes forever to ope - 5 minutes. A third and take 15 mintues.
Obviously I am missing soemthing in a multi-user environment. Does any one have a clue where I should look.
I have been programming for more than 20 years. THis is the first mullti-user Access that I have written.
TIA
I have a multi user-database. The programs are local, the data on a server. (It does the same things in NT server and Novell). THe application was written in Access 2000, Service pack 1 and the record sets are DAO.
The problem is that when I open one program on one work station, it is fast. The second one takes forever to ope - 5 minutes. A third and take 15 mintues.
Obviously I am missing soemthing in a multi-user environment. Does any one have a clue where I should look.
I have been programming for more than 20 years. THis is the first mullti-user Access that I have written.
TIA
I have a similar problem - and I've seen this in the past. It does not appear to be a matter of network performance, database performance, or anything else. It seems to be a bug.
Here is our current scenario:
Link the new front end to the backend which resides on a typical network (really doesn't matter what NOS.) Distribute to all client machines. Performance is perfect. Forms load quickly.
Later, link a new front end (same FE with minor changes) to the same backend. Once again, distribute to all client machines. First computer to open the database has no performance issues. All other users will experience long delays in loading the first form - up to 2 minutes. No other performance issues beyond that.
Performing the last step again, the problem will sometimes go away.
Has anyone found some ways to avoid this problem?
Cory
Here is our current scenario:
Link the new front end to the backend which resides on a typical network (really doesn't matter what NOS.) Distribute to all client machines. Performance is perfect. Forms load quickly.
Later, link a new front end (same FE with minor changes) to the same backend. Once again, distribute to all client machines. First computer to open the database has no performance issues. All other users will experience long delays in loading the first form - up to 2 minutes. No other performance issues beyond that.
Performing the last step again, the problem will sometimes go away.
Has anyone found some ways to avoid this problem?
Cory
For all out there, I have been running a front (client) and back (server) for about 5 years now.
PC: Windows 95/98/ME
DB: Access 97
Net: Novell
During an upgrade of the Novell end in 99 ran into this very problem. Turned out that the problem was in the way the network would track the users as they logged on to the access backend. Not being familiar with the network settings, our local network guru changed something in the directory tracking and the problem was fixed.
Perhaps somebody out there with LAn experience would have an idea what settings they are.
To confirm this just place the back and front on the same pc, redirect the links (odbc or std) and open two or three versions. (ram required) All should run okay if the LAN is the problem.
PC: Windows 95/98/ME
DB: Access 97
Net: Novell
During an upgrade of the Novell end in 99 ran into this very problem. Turned out that the problem was in the way the network would track the users as they logged on to the access backend. Not being familiar with the network settings, our local network guru changed something in the directory tracking and the problem was fixed.
Perhaps somebody out there with LAn experience would have an idea what settings they are.
To confirm this just place the back and front on the same pc, redirect the links (odbc or std) and open two or three versions. (ram required) All should run okay if the LAN is the problem.
Quehay said: "MRed - I know you're out there--I never thought of putting the queries in the tables file--it's a cool idea! (Kind of a poor man's client-server. . .) How do you run them from the front end? docmd.runquery "name"? Is there a linking mechanism that I'm not aware of?"
Michael Red said: "No. Just set a recordset to the querydef. QueryDef operates on the database, recordset transfers to front end."
Say what?! The jist of this thread is that you can implement some sort of client/server connection using only Access, in a process where queries on tables in a linked database on a separate computer are opened and run using DAO commands from the client machine:
--------------------------------
' Open the database
Set db = DBEngine.OpenDatabase("\\server\NorthWind.mdb"
' Get the QueryDef from the
' QueryDefs collection
Set qdf = db.QueryDefs("Sales by Year"
' Open the Recordset
Set rst = qdf.OpenRecordset(dbOpenForwardOnly, dbReadOnly)
----------------------------------
That will work to process the query. But not in the way Michael Red says it will. Michael says (at least I think he does) that if the query being referenced in this way resides in the back end database, that the SQL will be processed and the recordset assembled, all on the server machine, and then only the recordset will be passed back to the requesting client. Access doesn't work that way, not even when you're using code like that in this thread.
Access is a fileserving database. Simply put, there is no process running on the server machine that could possibly process a query there. The query must be processed by the client. I mean, think about it. Take a server that has a bare install of Windows on it. Place a back end .mdb on it and enable filesharing. Then use the method outlined in this thread. There is nothing running on the server that can possibly do anything. All processing must happen as a result of passing data through the network to the client. Opening a back end via DAO this way is not essentially different from just opening an .mdb on a server machine in the usual way. There is no way to magically implement a "poor man's client/server system" with Access.
I know this is an old thread, but I found it and read it and just wanted to correct things in case anyone else stumbled upon it. I'm also curious if anyone has realized any real (i.e., not imagined) speed gains from setting queries up on linked back ends to be run this way. If there are gains, they certainly are not coming about because of the query being processed on the server before its results are passed back to client machine.
--- Herb
Michael Red said: "No. Just set a recordset to the querydef. QueryDef operates on the database, recordset transfers to front end."
Say what?! The jist of this thread is that you can implement some sort of client/server connection using only Access, in a process where queries on tables in a linked database on a separate computer are opened and run using DAO commands from the client machine:
--------------------------------
' Open the database
Set db = DBEngine.OpenDatabase("\\server\NorthWind.mdb"
' Get the QueryDef from the
' QueryDefs collection
Set qdf = db.QueryDefs("Sales by Year"
' Open the Recordset
Set rst = qdf.OpenRecordset(dbOpenForwardOnly, dbReadOnly)
----------------------------------
That will work to process the query. But not in the way Michael Red says it will. Michael says (at least I think he does) that if the query being referenced in this way resides in the back end database, that the SQL will be processed and the recordset assembled, all on the server machine, and then only the recordset will be passed back to the requesting client. Access doesn't work that way, not even when you're using code like that in this thread.
Access is a fileserving database. Simply put, there is no process running on the server machine that could possibly process a query there. The query must be processed by the client. I mean, think about it. Take a server that has a bare install of Windows on it. Place a back end .mdb on it and enable filesharing. Then use the method outlined in this thread. There is nothing running on the server that can possibly do anything. All processing must happen as a result of passing data through the network to the client. Opening a back end via DAO this way is not essentially different from just opening an .mdb on a server machine in the usual way. There is no way to magically implement a "poor man's client/server system" with Access.
I know this is an old thread, but I found it and read it and just wanted to correct things in case anyone else stumbled upon it. I'm also curious if anyone has realized any real (i.e., not imagined) speed gains from setting queries up on linked back ends to be run this way. If there are gains, they certainly are not coming about because of the query being processed on the server before its results are passed back to client machine.
--- Herb
- Status
Similar threads
- Locked
- Question
- Locked
- Question
