I am relatively new to programming in Access for a multi user
environment, and am having trouble figuring out if there is a way to
accomplish one of our user requests.
I am working on a multi user Access 2002 application. BE db resides on
a LAN, FE on each client. The app imports large .csv files containing
billing info by date and allows users to run various reports. At a
given time, one user may be importing a file into the main table while
another user is running a report off that same table.
I do not want any of the new data to show up on the report unless all
of the records from the file have been imported.
Any suggestions on how to do this would be greatly appreciated.
TIA,
Cheryl 9 1715
Before running any reports, run the Dir command to check for any *.csv
files. If any exist, import the data first, then move or delete the
newly-imported csv file.
Darryl Kerkeslager
"cheryl" <ca*******@opto nline.net> wrote: I am working on a multi user Access 2002 application. BE db resides on a LAN, FE on each client. The app imports large .csv files containing billing info by date and allows users to run various reports. At a given time, one user may be importing a file into the main table while another user is running a report off that same table.
I do not want any of the new data to show up on the report unless all of the records from the file have been imported.
As the .csv files could reside on any number of user PCs, this would
mean that the file would need to be copied into the BE db folder on
the LAN before importing it. Would this provide better performance
than importing from the user PC where the FE resides?
Again, thanks for the info.
"Darryl Kerkeslager" <Ke*********@co mcast.net> wrote in message news:<6Z******* *************@c omcast.com>... Before running any reports, run the Dir command to check for any *.csv files. If any exist, import the data first, then move or delete the newly-imported csv file.
Darryl Kerkeslager
"cheryl" <ca*******@opto nline.net> wrote: I am working on a multi user Access 2002 application. BE db resides on a LAN, FE on each client. The app imports large .csv files containing billing info by date and allows users to run various reports. At a given time, one user may be importing a file into the main table while another user is running a report off that same table.
I do not want any of the new data to show up on the report unless all of the records from the file have been imported.
"cheryl" <ca*******@opto nline.net> wrote in message
news:88******** *************** ***@posting.goo gle.com... As the .csv files could reside on any number of user PCs, this would mean that the file would need to be copied into the BE db folder on the LAN before importing it. Would this provide better performance than importing from the user PC where the FE resides?
Again, thanks for the info.
"Darryl Kerkeslager" <Ke*********@co mcast.net> wrote in message
news:<6Z******* *************@c omcast.com>... Before running any reports, run the Dir command to check for any *.csv files. If any exist, import the data first, then move or delete the newly-imported csv file.
Darryl Kerkeslager
"cheryl" <ca*******@opto nline.net> wrote: I am working on a multi user Access 2002 application. BE db resides on a LAN, FE on each client. The app imports large .csv files containing billing info by date and allows users to run various reports. At a given time, one user may be importing a file into the main table while another user is running a report off that same table.
I do not want any of the new data to show up on the report unless all of the records from the file have been imported.
I would guess the main benefit would not be performance, but the fact that
if the import files were held in a central location, then it would be there
for all to see that (and for your database coding to check) there were
pending imports to be carried out. Your application could then refuse to
open any reports until these outstanding imports had been finished.
On another point, how sophisticated is the import routine? If you need
proper error checking, then you might need to involve transactions to make
sure that either all or records are successfully imported, or that none of
them are. It should also clearly identify the problem to the user (e.g. a
date is missing from a required field).
I assume that the operation would actually be carried out by code in the FE,
so from that regard, the operation should be faster if the .csv is on the
client PC (assuming that the client PC is relatively new). However, in my
experience, it is always faster to do [file-copy then import], rather than
[import then transmit data via SQL]. On the other hand, and perhaps most
importantly, unless these .csv files are very large, I wouldn't think that
the difference would be significant - so I would do it whichever way is
easier.
It is an interesting question as to what others may have found to be most
efficient ...
Darryl Kerkeslager
"cheryl" <ca*******@opto nline.net> wrote: As the .csv files could reside on any number of user PCs, this would mean that the file would need to be copied into the BE db folder on the LAN before importing it. Would this provide better performance than importing from the user PC where the FE resides?
"Darryl Kerkeslager" <Ke*********@co mcast.net> wrote: Before running any reports, run the Dir command to check for any *.csv files. If any exist, import the data first, then move or delete the newly-imported csv file.
"cheryl" <ca*******@opto nline.net> wrote: I am working on a multi user Access 2002 application. BE db resides on a LAN, FE on each client. The app imports large .csv files containing billing info by date and allows users to run various reports.
ca*******@opton line.net (cheryl) wrote in message news:<88******* *************** ****@posting.go ogle.com>... I am relatively new to programming in Access for a multi user environment, and am having trouble figuring out if there is a way to accomplish one of our user requests.
I am working on a multi user Access 2002 application. BE db resides on a LAN, FE on each client. The app imports large .csv files containing billing info by date and allows users to run various reports. At a given time, one user may be importing a file into the main table while another user is running a report off that same table.
I do not want any of the new data to show up on the report unless all of the records from the file have been imported.
Any suggestions on how to do this would be greatly appreciated.
TIA,
Cheryl
You could try importing the csv into a temp table in the FE. This
temp table would have the identical structure as the table in the BE.
You would just be inserting into and deleting from it. Then wrap the
append from the temp table into the final table in a transaction. I
am pretty sure no one will see the new data until the transaction is
complete.
dim wrkCurrent as dao.workspace
dim db as dao.database
dim blnTrans as boolean
Set db = CurrentDb
Set wrkCurrent = DBEngine.Worksp aces(0)
wrkCurrent.Begi nTrans
blnTrans = True
db.execute "INSERT INTO tblBETable (fields) " _
& "SELECT FIELDS " _
& "FROM tblTEMP;"
db.Execute "DELETE tblTEMP.* " _
& "FROM tblTEMP;"
wrkCurrent.Comm itTrans dbForceOSFlush
blnTrans = False
Set db = Nothing
Exit Sub
error_handler:
If blnTrans Then
wrkCurrent.Roll back
MsgBox "Error occurred. transaction not complete."
End If
"Eric Schittlipz" <er**@schittlip z.com> wrote in message news:<cl******* ***@titan.btint ernet.com>... "cheryl" <ca*******@opto nline.net> wrote in message news:88******** *************** ***@posting.goo gle.com... As the .csv files could reside on any number of user PCs, this would mean that the file would need to be copied into the BE db folder on the LAN before importing it. Would this provide better performance than importing from the user PC where the FE resides?
Again, thanks for the info.
"Darryl Kerkeslager" <Ke*********@co mcast.net> wrote in message news:<6Z******* *************@c omcast.com>... Before running any reports, run the Dir command to check for any *.csv files. If any exist, import the data first, then move or delete the newly-imported csv file.
Darryl Kerkeslager
"cheryl" <ca*******@opto nline.net> wrote: > > I am working on a multi user Access 2002 application. BE db resides on > a LAN, FE on each client. The app imports large .csv files containing > billing info by date and allows users to run various reports. At a > given time, one user may be importing a file into the main table while > another user is running a report off that same table. > > I do not want any of the new data to show up on the report unless all > of the records from the file have been imported.
I would guess the main benefit would not be performance, but the fact that if the import files were held in a central location, then it would be there for all to see that (and for your database coding to check) there were pending imports to be carried out. Your application could then refuse to open any reports until these outstanding imports had been finished. On another point, how sophisticated is the import routine? If you need proper error checking, then you might need to involve transactions to make sure that either all or records are successfully imported, or that none of them are. It should also clearly identify the problem to the user (e.g. a date is missing from a required field).
How would you set the flag for "Busy importing"? Create a file on the
server computer and then use Dir() to see if it exists like Barker
does in Access 2000 Power Programming? Are there any other options?
Could you set a flag in the back end that the front end could see? Not
sure how to do that, though. The good thing about creating the file
to indicate a "busy" database is that if the system freezes, deleting
the file is trivial... Come to think of it, I kinda like that solution
better than other alternatives... but does anybody have any other
ideas on how to do this?
thx,
Pieter
"Pieter Linden" <pi********@hot mail.com> wrote in message
news:bf******** *************** ***@posting.goo gle.com... "Eric Schittlipz" <er**@schittlip z.com> wrote in message
news:<cl******* ***@titan.btint ernet.com>... "cheryl" <ca*******@opto nline.net> wrote in message news:88******** *************** ***@posting.goo gle.com... As the .csv files could reside on any number of user PCs, this would mean that the file would need to be copied into the BE db folder on the LAN before importing it. Would this provide better performance than importing from the user PC where the FE resides?
Again, thanks for the info.
"Darryl Kerkeslager" <Ke*********@co mcast.net> wrote in message news:<6Z******* *************@c omcast.com>... > Before running any reports, run the Dir command to check for any
*.csv > files. If any exist, import the data first, then move or delete the > newly-imported csv file. > > > Darryl Kerkeslager > > > "cheryl" <ca*******@opto nline.net> wrote: > > > > I am working on a multi user Access 2002 application. BE db
resides on > > a LAN, FE on each client. The app imports large .csv files
containing > > billing info by date and allows users to run various reports. At
a > > given time, one user may be importing a file into the main table
while > > another user is running a report off that same table. > > > > I do not want any of the new data to show up on the report unless
all > > of the records from the file have been imported.
I would guess the main benefit would not be performance, but the fact
that if the import files were held in a central location, then it would be
there for all to see that (and for your database coding to check) there were pending imports to be carried out. Your application could then refuse
to open any reports until these outstanding imports had been finished. On another point, how sophisticated is the import routine? If you need proper error checking, then you might need to involve transactions to
make sure that either all or records are successfully imported, or that none
of them are. It should also clearly identify the problem to the user (e.g.
a date is missing from a required field).
How would you set the flag for "Busy importing"? Create a file on the server computer and then use Dir() to see if it exists like Barker does in Access 2000 Power Programming? Are there any other options? Could you set a flag in the back end that the front end could see? Not sure how to do that, though. The good thing about creating the file to indicate a "busy" database is that if the system freezes, deleting the file is trivial... Come to think of it, I kinda like that solution better than other alternatives... but does anybody have any other ideas on how to do this? thx, Pieter
I didn't actually mean to use the presence of a file to mean 'busy
importing'. I meant rather that this file should indicate that there are
pending updates to the database and so reports should not be run. The whole
import routine could be wrapped in a transaction where the penultimate step
is to delete (or move) the import file. If file is successfully deleted, or
moved, then the transaction can be committed.
However, if you wanted another way to flag some sort of 'database busy'
information, then I often use a table in the back end called tblDbInfo with
table constraints to allow only a single row with ID=1. You could then have
a field such as tblDbInfo.Impor tInProgress as a yes/no field. Anyway, I
guess from the number of postings you do, that you have tried this or
similar approaches before.
Thanks to everyone for your suggestions. I have been unable to access
the newsgroup for a couple of days, and have not had the chance to try
anything yet.
I am going to try the route of using a FE temp table and then an
append with transactions. Will let you know how it goes.
All your help is greatly appreciated. :) us****@yahoo.co m (Dan Morgan) wrote in message news:<fe******* *************** ****@posting.go ogle.com>... ca*******@opton line.net (cheryl) wrote in message news:<88******* *************** ****@posting.go ogle.com>... I am relatively new to programming in Access for a multi user environment, and am having trouble figuring out if there is a way to accomplish one of our user requests.
I am working on a multi user Access 2002 application. BE db resides on a LAN, FE on each client. The app imports large .csv files containing billing info by date and allows users to run various reports. At a given time, one user may be importing a file into the main table while another user is running a report off that same table.
I do not want any of the new data to show up on the report unless all of the records from the file have been imported.
Any suggestions on how to do this would be greatly appreciated.
TIA,
Cheryl
You could try importing the csv into a temp table in the FE. This temp table would have the identical structure as the table in the BE. You would just be inserting into and deleting from it. Then wrap the append from the temp table into the final table in a transaction. I am pretty sure no one will see the new data until the transaction is complete.
dim wrkCurrent as dao.workspace dim db as dao.database dim blnTrans as boolean
Set db = CurrentDb Set wrkCurrent = DBEngine.Worksp aces(0)
wrkCurrent.Begi nTrans
blnTrans = True
db.execute "INSERT INTO tblBETable (fields) " _ & "SELECT FIELDS " _ & "FROM tblTEMP;"
db.Execute "DELETE tblTEMP.* " _ & "FROM tblTEMP;"
wrkCurrent.Comm itTrans dbForceOSFlush
blnTrans = False
Set db = Nothing Exit Sub
error_handler: If blnTrans Then wrkCurrent.Roll back MsgBox "Error occurred. transaction not complete." End If us****@yahoo.co m (Dan Morgan) wrote: You could try importing the csv into a temp table in the FE.
I agree with your approach except for using the FE as a storage location for the temp
table. Instead I'd create an MDB, place the temp table in there and delete it when
you're done.
See the TempTables.MDB page at my website which illustrates how to use a temporary
MDB in your app. http://www.granite.ab.ca/access/temptables.htm
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Silver |
last post by:
Hi everyone,
my program compiles and executes, but I get an error during run-time. I
guess it has something to do with memory allocation (which I don't seem to
fully control yet).. Here's the code:
#include <iostream>
#include <malloc.h>
#include <iomanip>
using namespace std;
|
by: Shane |
last post by:
Hi,
Thanks in advance for the help. I have been to many websites and tried
several solutions to my problem, but have fixed part of it. It's time to
come humbly to the newsgroups for help :-)
By the way, please don't reply just to tell me that my code isn't optimized
and that I should use a delimited database rather than a line by line
record. If I had my way, I'd do it differently too.
|
by: Tree menu using XML |
last post by:
I have one XML file that has nodes and sub node and each and every node has
the attribute call visible if its value is true then diplay this node else
don't display thid node, but this condition i am able to check using xpath in
asp.net 2.0 till MenuItem node. if i check visible attribute value till
SubMenuLevel0 node then in tree it will not display the MenuItem Node at all
Note: My tree Menu will start from MenuItem node and it will...
|
by: keepyourstupidspam |
last post by:
Anyone know of a reliable design for a Windows C++ Task Scheduler
Class.
The scheduler will expose a member function that will add schedules,
its parameters will be an interval to run the tasks and a function
pointer. This function pointer will be a void* function in other
objects that will use the scheduler.
So when another object calls the addSchedule function the scheduler
will run these tasks at each interval provided. There may...
|
by: mimmo |
last post by:
Hi! I should convert the accented letters of a string in the correspondent
letters not accented. But when I compile with -Wall it give me:
warning: multi-character character constant
Do the problem is the charset? How I can avoid this warning? But the worst
thing isn't the warning, but that the program doesn't work! The program
execute all other operations well, but it don't print the converted
letters: for example, in the string...
| |
by: Jeff Louie |
last post by:
Well I wonder if my old brain can handle threading. Dose this code look
reasonable.
Regards,
Jeff
using System;
using System.Diagnostics;
using System.IO;
using System.Threading;
|
by: Jay |
last post by:
I have a multi threaded VB.NET application (4 threads) that I use to send
text messages to many, many employees via system.timer at a 5 second
interval. Basically, I look in a SQL table (queue) to determine who needs
to receive the text message then send the message to the address. Only
problem is, the employee may receive up to 4 of the same messages because
each thread gets the recors then sends the message. I need somehow to
prevent...
|
by: deezle |
last post by:
Hello,
I am trying to get my calculator GUI to +,-,* and /. I have got all of them to work except my division. I was wondering if someone could helpme figure out the problem. Any input would help and I'll leave my code in here
=========================================================
package chapfour;
/**
*
* @author Deezle
*/
import java.awt.*;
|
by: Kevin McKinley |
last post by:
Below i've put the code for a program that i wrote. I need help on lines 384-403. If you run this program you will notice on the first tab when have it produce an answer the $ is surrounded with {$}. How can i get rid of that?
from Tkinter import *
class MyApp:
def __init__(self, parent):
self.myparent = parent
|
by: Guilherme Polo |
last post by:
On Wed, Sep 3, 2008 at 8:57 PM, Kevin McKinley <kem1723@yahoo.comwrote:
Come on.. "help on lines 384-403", that is not a good way to look for
help. You are supposed to post some minimal code that demonstrates the
problem.
Anyway, this demonstrates what you are getting (independent of python version):
import Tkinter
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |