If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Relink tables?
Hello All
I have a very complex, A2K frontend/backend split mdb, which is often now used by 7 users at the same time. The backend sits on a server machine running Windows2K Server, and is approaching 1Gb. I know that sooner or later I should move to SQLserver, but do not have the expertise to do this myself and cannot afford to buy it in yet (hopefully in a year or so!). Until I can move to SQLServer, I was wondering whether a solution might be to split the backend in half (or even into thirds), and get the users then to relink the tables of their frontend to whichever backend they need (from the actual usage point of view of the users this would be perfectly feasible, and would probably only need to be done once - at the start of each day). That way the 7 users would be very unlikely all to be using the same backend at the same time, and each backend would obviously be half (or third) of the size of the original backend. Is there any reason why this idea would not be a good one? Obviously another idea would be simply to have 2 (or 3) separate frontends which link to the 2 (or 3) cutdown backends, but that way I would have to maintain 2 (or 3) frontends and as I am regularly required to make amendments to the frontend this would be tedious and carry the risk of the frontends becoming inconsistent with each other. Hope someone can help. Many thanks Leslie Isaacs |
#2
|
|||
|
|||
Relink tables?
Allowing the users to change the backend to which they're linked isn't an
unreasonable thing. There's sample code to do this at http://www.mvps.org/access/tables/tbl0009.htm at "The Access Web", or you can get the free J Street Access Relinker at http://www.jstreettech.com/cartgenie...rDownloads.asp -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Leslie Isaacs" wrote in message ... Hello All I have a very complex, A2K frontend/backend split mdb, which is often now used by 7 users at the same time. The backend sits on a server machine running Windows2K Server, and is approaching 1Gb. I know that sooner or later I should move to SQLserver, but do not have the expertise to do this myself and cannot afford to buy it in yet (hopefully in a year or so!). Until I can move to SQLServer, I was wondering whether a solution might be to split the backend in half (or even into thirds), and get the users then to relink the tables of their frontend to whichever backend they need (from the actual usage point of view of the users this would be perfectly feasible, and would probably only need to be done once - at the start of each day). That way the 7 users would be very unlikely all to be using the same backend at the same time, and each backend would obviously be half (or third) of the size of the original backend. Is there any reason why this idea would not be a good one? Obviously another idea would be simply to have 2 (or 3) separate frontends which link to the 2 (or 3) cutdown backends, but that way I would have to maintain 2 (or 3) frontends and as I am regularly required to make amendments to the frontend this would be tedious and carry the risk of the frontends becoming inconsistent with each other. Hope someone can help. Many thanks Leslie Isaacs |
#3
|
|||
|
|||
Relink tables?
|
#4
|
|||
|
|||
Relink tables?
First thing is have you ever run compact on the backend? If not, make a
backup copy and then try compacting the backend and see if that decreases the size significantly. You can split the backend into multiple sections with some tables in one backend and other tables in another backend. The problem with this is you can see some performance degradation and you cannot enforce relational integrity between tables in one backend and tables in another backend. You can program around that, but it is a headache to handle. If your data is such that the users can work with separate backends then you can do that. Moving to SQL Server is not too difficult. In most cases, moving the backend to SQL Server database and linking to the tables via ODBC and a DSN or DSN-less connection will work. Sometimes there are problems that will cause a need to redesign some forms. And performance can suffer a bit in some cases. '================================================= === John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County '================================================= === Leslie Isaacs wrote: Hello All I have a very complex, A2K frontend/backend split mdb, which is often now used by 7 users at the same time. The backend sits on a server machine running Windows2K Server, and is approaching 1Gb. I know that sooner or later I should move to SQLserver, but do not have the expertise to do this myself and cannot afford to buy it in yet (hopefully in a year or so!). Until I can move to SQLServer, I was wondering whether a solution might be to split the backend in half (or even into thirds), and get the users then to relink the tables of their frontend to whichever backend they need (from the actual usage point of view of the users this would be perfectly feasible, and would probably only need to be done once - at the start of each day). That way the 7 users would be very unlikely all to be using the same backend at the same time, and each backend would obviously be half (or third) of the size of the original backend. Is there any reason why this idea would not be a good one? Obviously another idea would be simply to have 2 (or 3) separate frontends which link to the 2 (or 3) cutdown backends, but that way I would have to maintain 2 (or 3) frontends and as I am regularly required to make amendments to the frontend this would be tedious and carry the risk of the frontends becoming inconsistent with each other. Hope someone can help. Many thanks Leslie Isaacs |
#5
|
|||
|
|||
Relink tables?
Hello Douglas
Many thanks for your reply: it's good to know that there is no fundamental reason not to have users relink the tables. I will have at look at the links you have given, and will probably use one of them. Thanks again Les "Douglas J. Steele" wrote in message ... Allowing the users to change the backend to which they're linked isn't an unreasonable thing. There's sample code to do this at http://www.mvps.org/access/tables/tbl0009.htm at "The Access Web", or you can get the free J Street Access Relinker at http://www.jstreettech.com/cartgenie...rDownloads.asp -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Leslie Isaacs" wrote in message ... Hello All I have a very complex, A2K frontend/backend split mdb, which is often now used by 7 users at the same time. The backend sits on a server machine running Windows2K Server, and is approaching 1Gb. I know that sooner or later I should move to SQLserver, but do not have the expertise to do this myself and cannot afford to buy it in yet (hopefully in a year or so!). Until I can move to SQLServer, I was wondering whether a solution might be to split the backend in half (or even into thirds), and get the users then to relink the tables of their frontend to whichever backend they need (from the actual usage point of view of the users this would be perfectly feasible, and would probably only need to be done once - at the start of each day). That way the 7 users would be very unlikely all to be using the same backend at the same time, and each backend would obviously be half (or third) of the size of the original backend. Is there any reason why this idea would not be a good one? Obviously another idea would be simply to have 2 (or 3) separate frontends which link to the 2 (or 3) cutdown backends, but that way I would have to maintain 2 (or 3) frontends and as I am regularly required to make amendments to the frontend this would be tedious and carry the risk of the frontends becoming inconsistent with each other. Hope someone can help. Many thanks Leslie Isaacs |
#7
|
|||
|
|||
Relink tables?
Hello John
Many thanks for your reply. I do compact/repair the mdb regularly. Apart from its size, it's the number of simultaneous users issue that I think I'd like to tackle by splitting the backend. I seem to remember reading somewhere that access may have problems with more than 6 or 7: is that right? The way I was thinking of splitting the backend, each frontend would only have linked tables from one backend: sort of, data for clients A to M in backend 1 and data for clients N to Z in backend 2. I am mystified that you say "Moving to SQL Server is not too difficult.", as I have had quotes for serveral thousand £GBP for the job at least one of which was from a very reputable company! The frontend is very complex (large numbers of forms, reports and queries, and tons of code). I hesitate to say this on this newsgroup (!), but if you or anyone you know believes it may be a simple matter that would cost £hundreds instead of £thousands, and would be interested in the job, please let me know. Thanks again Les "John Spencer" wrote in message ... First thing is have you ever run compact on the backend? If not, make a backup copy and then try compacting the backend and see if that decreases the size significantly. You can split the backend into multiple sections with some tables in one backend and other tables in another backend. The problem with this is you can see some performance degradation and you cannot enforce relational integrity between tables in one backend and tables in another backend. You can program around that, but it is a headache to handle. If your data is such that the users can work with separate backends then you can do that. Moving to SQL Server is not too difficult. In most cases, moving the backend to SQL Server database and linking to the tables via ODBC and a DSN or DSN-less connection will work. Sometimes there are problems that will cause a need to redesign some forms. And performance can suffer a bit in some cases. '================================================= === John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County '================================================= === Leslie Isaacs wrote: Hello All I have a very complex, A2K frontend/backend split mdb, which is often now used by 7 users at the same time. The backend sits on a server machine running Windows2K Server, and is approaching 1Gb. I know that sooner or later I should move to SQLserver, but do not have the expertise to do this myself and cannot afford to buy it in yet (hopefully in a year or so!). Until I can move to SQLServer, I was wondering whether a solution might be to split the backend in half (or even into thirds), and get the users then to relink the tables of their frontend to whichever backend they need (from the actual usage point of view of the users this would be perfectly feasible, and would probably only need to be done once - at the start of each day). That way the 7 users would be very unlikely all to be using the same backend at the same time, and each backend would obviously be half (or third) of the size of the original backend. Is there any reason why this idea would not be a good one? Obviously another idea would be simply to have 2 (or 3) separate frontends which link to the 2 (or 3) cutdown backends, but that way I would have to maintain 2 (or 3) frontends and as I am regularly required to make amendments to the frontend this would be tedious and carry the risk of the frontends becoming inconsistent with each other. Hope someone can help. Many thanks Leslie Isaacs |
#8
|
|||
|
|||
Relink tables?
Depending on the design of your database, Access with JET (the native
database engine for Access) can often support a few dozen users with little problem and degradation of performance. It depends on what the users are doing - entering new data, changing existing data, or just accessing the information for reports and information. Also, good design does not load all the records in a table. You filter the records so tat you only access a few records at one time. For instance, in one of my databases I load one patient in a main form and use subforms to load only the records associated with that patient (visits, caregivers, etc). I have moved medium level complexity databases from JET to SQL Server in less than a day. The data structure and data was transferred from JET to SQL server using the built-in tools in Access - upsizing wizard. Then I linked to the SQL Server tables and tested. In most cases, I did not have to make any further changes to the front-end to have a working application. I did find that the performance could be enhanced by using some of the features of SQL server. I wouldn't undertake this job at a distance because I can only get the SQL server set up correctly (security, backups, etc) by being on site. That is probably due to my limited ability working with SQL server. '================================================= === John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County '================================================= === Leslie Isaacs wrote: Hello John Many thanks for your reply. I do compact/repair the mdb regularly. Apart from its size, it's the number of simultaneous users issue that I think I'd like to tackle by splitting the backend. I seem to remember reading somewhere that access may have problems with more than 6 or 7: is that right? The way I was thinking of splitting the backend, each frontend would only have linked tables from one backend: sort of, data for clients A to M in backend 1 and data for clients N to Z in backend 2. I am mystified that you say "Moving to SQL Server is not too difficult.", as I have had quotes for serveral thousand £GBP for the job at least one of which was from a very reputable company! The frontend is very complex (large numbers of forms, reports and queries, and tons of code). I hesitate to say this on this newsgroup (!), but if you or anyone you know believes it may be a simple matter that would cost £hundreds instead of £thousands, and would be interested in the job, please let me know. Thanks again Les "John Spencer" wrote in message ... First thing is have you ever run compact on the backend? If not, make a backup copy and then try compacting the backend and see if that decreases the size significantly. You can split the backend into multiple sections with some tables in one backend and other tables in another backend. The problem with this is you can see some performance degradation and you cannot enforce relational integrity between tables in one backend and tables in another backend. You can program around that, but it is a headache to handle. If your data is such that the users can work with separate backends then you can do that. Moving to SQL Server is not too difficult. In most cases, moving the backend to SQL Server database and linking to the tables via ODBC and a DSN or DSN-less connection will work. Sometimes there are problems that will cause a need to redesign some forms. And performance can suffer a bit in some cases. '================================================= === John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County '================================================= === Leslie Isaacs wrote: Hello All I have a very complex, A2K frontend/backend split mdb, which is often now used by 7 users at the same time. The backend sits on a server machine running Windows2K Server, and is approaching 1Gb. I know that sooner or later I should move to SQLserver, but do not have the expertise to do this myself and cannot afford to buy it in yet (hopefully in a year or so!). Until I can move to SQLServer, I was wondering whether a solution might be to split the backend in half (or even into thirds), and get the users then to relink the tables of their frontend to whichever backend they need (from the actual usage point of view of the users this would be perfectly feasible, and would probably only need to be done once - at the start of each day). That way the 7 users would be very unlikely all to be using the same backend at the same time, and each backend would obviously be half (or third) of the size of the original backend. Is there any reason why this idea would not be a good one? Obviously another idea would be simply to have 2 (or 3) separate frontends which link to the 2 (or 3) cutdown backends, but that way I would have to maintain 2 (or 3) frontends and as I am regularly required to make amendments to the frontend this would be tedious and carry the risk of the frontends becoming inconsistent with each other. Hope someone can help. Many thanks Leslie Isaacs |
#9
|
|||
|
|||
Relink tables?
"Leslie Isaacs" wrote:
Apart from its size, it's the number of simultaneous users issue that I think I'd like to tackle by splitting the backend. I seem to remember reading somewhere that access may have problems with more than 6 or 7: is that right? Not at all. I've got clients with 25 users in the back end all the time. Granted half of those are generally doing lookups and reports but the other half are doing heavy duty data entry for hours at a time. Tony -- Tony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ Granite Fleet Manager http://www.granitefleet.com/ |
#10
|
|||
|
|||
Relink tables?
"Leslie Isaacs" wrote:
The way I was thinking of splitting the backend, each frontend would only have linked tables from one backend: sort of, data for clients A to M in backend 1 and data for clients N to Z in backend 2. That's going to be a huge pain in the uhh heck for the users. It could take 10 or 20 seconds to relink the tables to the other back end. And what happens with reports where you need to use both sets of data? shudder That's going to be a *LOT* of extra work. I am mystified that you say "Moving to SQL Server is not too difficult.", as I have had quotes for serveral thousand £GBP for the job at least one of which was from a very reputable company! The frontend is very complex (large numbers of forms, reports and queries, and tons of code). I hesitate to say this on this newsgroup (!), but if you or anyone you know believes it may be a simple matter that would cost £hundreds instead of £thousands, and would be interested in the job, please let me know. Well, that's reasonable enough. A thousand or two thousand pounds would be a weeks worth of consulting work. Just guessing. So sure it could easily take a week or two or three to move a system over to SQL Server and remove the initial bottlenecks. Tony -- Tony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ Granite Fleet Manager http://www.granitefleet.com/ |
Thread Tools | |
Display Modes | |
|
|