 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / March 2005Running a query on the Back End
I've searched the newsgroups about this, and any posts regarding my particular situation are from 2000 or earlier, so I'm hoping a resolution has come up since then. I have a Back End that is used by several users' identical Front Ends. All Front Ends ONLY use the Back End Access tables. However, I want to update and append these Back End tables using linked ProvideX tables from a separate SCO ERP table set. So, I want to Link the SCO tables to the Back End, and just activate/run the BackEnd's update/append queries to append the Access tables in the Back End which the Front Ends use. I have 2 reasons for doing this: - only having to pay for one set of ODBC drivers for the PC/server where the BackEnd resides - reducing network traffic and Server lag, since the queries on the Back End would fill Access Tables only with usable data, whereas querying the SCO tables per use per FrontEnd user take about 4 minutes for every Form/Data entry action. For example, the forms and queries on the Front end never need to see records from before 2003, whereas the SCO tables themselves have records dating back to 1999. Using update/append queries and linking FrontEnd forms/queries to the Access Tables rather than the SCO tables reduces the records/filters from about 600,000 records to 30,000. Is there any way to just run a BackEnd query using a FrontEnd button click event or something? Again, the query would ONLY update/append the Back End tables from SCO tables linked to that same Back End. If anyone knows the code or whatever that's needed, let's assume a sample SCO Linked table is named Order_Status, the Access Back End table to be updated/appended is OrderStatusAcc, and the path to the Back End is //ServerRoot/C:/BackEnd/AccessBackEnd.mdb. Thanks for any help here, it is much appreciated. Hi, as i understand you - this will not work as you expect. even if BE is located on a server, Jet is still running on a local PC, so in order to connect to SCO tables it need ODBC drives on local PC also solution can be - is to build a separate application to be run to server, which will do import, and which will be intiated from any frontent by some flag in BE table for example HTH  SignatureAlex Dybenko (MVP) http://Alex.Dybenko.com http://www.PointLtd.com > I've searched the newsgroups about this, and any posts regarding my > particular situation are from 2000 or earlier, so I'm hoping a resolution [quoted text clipped - 29 lines] > > Thanks for any help here, it is much appreciated. Thanks for the reply. Is there any kind of example you can give me as to how to do this? All I need is for these tables in the Back End to be updated from SCO tables which would be linked to that same Back End. What sort of application would I make, and what do you mean by flag? Thanks again > Hi, > as i understand you - this will not work as you expect. even if BE is [quoted text clipped - 39 lines] > > > > Thanks for any help here, it is much appreciated. Hi, just make an access application, which runs on server and do following tasks: every 5 mins (1 hour) it checks to a value in a field "run import" of a table "settings" if it=true - is run all your import queries and then set flag back to false so when some user need to run import - in just set this flag field to true Also you can build the same on VB, and run it as a service on your server. then you dont need to have a logged session runnig SignatureAlex Dybenko (MVP) http://Alex.Dybenko.com http://www.PointLtd.com > Thanks for the reply. Is there any kind of example you can give me as to > how to do this? All I need is for these tables in the Back End to be [quoted text clipped - 60 lines] >> > >> > Thanks for any help here, it is much appreciated. Can I check and see if I'm thinking about this right? I make a completely separate .mdb from the Back End .mdb, and place it on the server, or even in the folder, where the Back End resides. I put one table in the new .mdb called Settings. I make one field in the Settings Table called RunImport. I make it a Yes/No or True/False field. I then make a module in the new .mdb that uses the timer event to every 50,000,000 or whatever milliseconds, looks at Settings.RunImport, and if it is False, docmd.runcmd.openquery1, docmd.runcmd.openquery2 etc. etc. and then what? Why would I set the value as different, when it's a timer that drives the queries running? Shouldn't it just be a timer that does it, rather than a field value? I may be thinking about it wrong, I hardly ever use the Timer. Would it be possible to get a code snippet assuming the tablenames above? I'm not sure how to run a query in a separate db using VBA, and I also don't know how to use the timer event except on forms. Thanks again, I appreciate the consideration > Hi, > just make an access application, which runs on server and do following [quoted text clipped - 72 lines] > >> > > >> > Thanks for any help here, it is much appreciated. Hi - just a bit of info I have tried the same thing and ran into a problem when a FrontEnd(FE) was open with a link to the BackEnd(BE), then the BE could not perforn the update. The soultions for me was two applications, one Datapart and a Reportpart per user. When a user opens Datapart it is the same as the BE, but with an user interface to start up an update and on this user interface You can se when the last update has been performed. Opening a Reportpart it is a FE where I do a tableimport with VBA from the BE so I don't need to link to the BE and in the FE users also can se when an update has been performed. Best regards Poul > Can I check and see if I'm thinking about this right? > [quoted text clipped - 102 lines] > > >> > > > >> > Thanks for any help here, it is much appreciated. Hi, yes your idea is correct. timer - you have to use one from the form (for example a hidden one) in timer event you check if Settings.RunImport=true, if yes - then run queries, and then set it back to false SignatureAlex Dybenko (MVP) http://Alex.Dybenko.com http://www.PointLtd.com > Can I check and see if I'm thinking about this right? > [quoted text clipped - 109 lines] >> >> > >> >> > Thanks for any help here, it is much appreciated. I guess the part I'm confused about is setting the value to false after the queries are run. Won't this mean that every check in the future will return false, so the queries would never run? If I want to have the timer check every certain amount of time, doesn't the value need to be "true" at every check? And how do I loop this checking from a form? I looked at the timer help, and didn't see anyhting about looping. Do I need to some extra VBA to start the timer again after it hits the first timer amount set? For instance, to run these queries every 20 minutes, what would the VBA behind the timer look like, and would there be some other code necessary to start the timer over? Sorry if I'm coming across as being thick, I'm just trying stuff here and nothing's working so far (except using ODBC on every user's machine) > Hi, > yes your idea is correct. timer - you have to use one from the form (for [quoted text clipped - 124 lines] >>> >> > >>> >> > Thanks for any help here, it is much appreciated. >I guess the part I'm confused about is setting the value to false after the >queries are run. Won't this mean that every check in the future will >return false, so the queries would never run? If I want to have the timer >check every certain amount of time, doesn't the value need to be "true" at >every check? as i understand you - user can force import to run. so when user need to run import - he just set this flag to true. but if you need to run import say every 20 mins - then you dont need this flag. > And how do I loop this checking from a form? I looked at the timer help, > and didn't see anyhting about looping. Do I need to some extra VBA to > start the timer again after it hits the first timer amount set? For > instance, to run these queries every 20 minutes, what would the VBA behind > the timer look like, and would there be some other code necessary to start > the timer over? you can just set appropriate timer interval (in milliseconds), and timer event will fire automatically. SignatureAlex Dybenko (MVP) http://Alex.Dybenko.com http://www.PointLtd.com Have you thought about loggin onto the server directly and running the query directly from within the backend? If you don't have physically access to the server, a product like PCAnywhere can give you access to the server's desktop. > Hi, > as i understand you - this will not work as you expect. even if BE is [quoted text clipped - 6 lines] > > HTH |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.