Help with multi-user db

[cheryl]

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

[Darryl Kerkeslager]

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*******@optonline.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]

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*********@comcast.net> wrote in message news:<6Z********************@comcast.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*******@optonline.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.

[Eric Schittlipz]

"cheryl" <ca*******@optonline.net> wrote in message
news:88**************************@posting.google.c om...

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*********@comcast.net> wrote in message

news:<6Z********************@comcast.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*******@optonline.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).

[Darryl Kerkeslager]

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*******@optonline.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*********@comcast.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*******@optonline.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.

[Dan Morgan]

ca*******@optonline.net (cheryl) wrote in message news:<88**************************@posting.google. 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.Workspaces(0)

wrkCurrent.BeginTrans

blnTrans = True

db.execute "INSERT INTO tblBETable (fields) " _
& "SELECT FIELDS " _
& "FROM tblTEMP;"

db.Execute "DELETE tblTEMP.* " _
& "FROM tblTEMP;"

wrkCurrent.CommitTrans dbForceOSFlush

blnTrans = False

Set db = Nothing
Exit Sub

error_handler:
If blnTrans Then
wrkCurrent.Rollback
MsgBox "Error occurred. transaction not complete."
End If

[Pieter Linden]

"Eric Schittlipz" <er**@schittlipz.com> wrote in message news:<cl**********@titan.btinternet.com>...

"cheryl" <ca*******@optonline.net> wrote in message
news:88**************************@posting.google.c om...

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*********@comcast.net> wrote in message

news:<6Z********************@comcast.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*******@optonline.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

[Eric Schittlipz]

"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...

"Eric Schittlipz" <er**@schittlipz.com> wrote in message

news:<cl**********@titan.btinternet.com>...

"cheryl" <ca*******@optonline.net> wrote in message
news:88**************************@posting.google.c om...

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*********@comcast.net> wrote in message

news:<6Z********************@comcast.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*******@optonline.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.ImportInProgress as a yes/no field. Anyway, I
guess from the number of postings you do, that you have tried this or
similar approaches before.

[cheryl]

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.com (Dan Morgan) wrote in message news:<fe**************************@posting.google. com>...

ca*******@optonline.net (cheryl) wrote in message news:<88**************************@posting.google. 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.Workspaces(0)

wrkCurrent.BeginTrans

blnTrans = True

db.execute "INSERT INTO tblBETable (fields) " _
& "SELECT FIELDS " _
& "FROM tblTEMP;"

db.Execute "DELETE tblTEMP.* " _
& "FROM tblTEMP;"

wrkCurrent.CommitTrans dbForceOSFlush

blnTrans = False

Set db = Nothing
Exit Sub

error_handler:
If blnTrans Then
wrkCurrent.Rollback
MsgBox "Error occurred. transaction not complete."
End If

[Tony Toews]

us****@yahoo.com (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