Queries VS VBA Design

My partner and I developed an access database using queries instead of VBA
because we will not be the ones who will edit and update it and the people
who will do not write VBA but do work extensively with queries. We have now
been told through other people that an experienced designer stated our system
will NOT work, will overload the system and will cause the linked table to
corrupt and disrupt all of the other programs (which are VBA code based)
because of the database being query based. The databasehas been converted to
mde. Is there any problem running the database with approximately 6,000
records and maybe 100 users working on it at one time with a query based
database and the shared records also being accessed by VBA written database?

I manage a one million record database set up just that way. The tables are
all on a sql server and then users have an Access front end on their
machines. They log into the server and use the data they need through
queries, forms and reports. I probably have somewhere around 50-60 queries
that are used to power different forms and reports that various users use.
The tables are backed up each day and the users are responsible for backing
up their forms or reports that they use. We have no problem at all with that
system, provided the server soesn't go down, which is very infrequent.

"AccessNewbie" wrote:

My partner and I developed an access database using queries instead of VBA
because we will not be the ones who will edit and update it and the people
who will do not write VBA but do work extensively with queries. We have now
been told through other people that an experienced designer stated our system
will NOT work, will overload the system and will cause the linked table to
corrupt and disrupt all of the other programs (which are VBA code based)
because of the database being query based. The databasehas been converted to
mde. Is there any problem running the database with approximately 6,000
records and maybe 100 users working on it at one time with a query based
database and the shared records also being accessed by VBA written database?

Would there be a problem using a query based front end database with 2 VBA
based front end databases? They all use the same backend database.

"Golfinray" wrote:

I manage a one million record database set up just that way. The tables are
all on a sql server and then users have an Access front end on their
machines. They log into the server and use the data they need through
queries, forms and reports. I probably have somewhere around 50-60 queries
that are used to power different forms and reports that various users use.
The tables are backed up each day and the users are responsible for backing
up their forms or reports that they use. We have no problem at all with that
system, provided the server soesn't go down, which is very infrequent.

"AccessNewbie" wrote:

My partner and I developed an access database using queries instead of VBA
because we will not be the ones who will edit and update it and the people
who will do not write VBA but do work extensively with queries. We have now
been told through other people that an experienced designer stated our system
will NOT work, will overload the system and will cause the linked table to
corrupt and disrupt all of the other programs (which are VBA code based)
because of the database being query based. The databasehas been converted to
mde. Is there any problem running the database with approximately 6,000
records and maybe 100 users working on it at one time with a query based
database and the shared records also being accessed by VBA written database?

No. You would need separate icons for users to go into the 3 different
databases unless you somehow wanted to link them all together, which is
possible. I would recommend just staying with the 3 separate for ease of use
on your part. Just go to the desktop of each user and put a shortcut to that
database or however you want to set it up. Just be sure they have access to
all the tables they need, most likely on a lan, and you should be good to go.

"AccessNewbie" wrote:

Would there be a problem using a query based front end database with 2 VBA
based front end databases? They all use the same backend database.

"Golfinray" wrote:

I manage a one million record database set up just that way. The tables are
all on a sql server and then users have an Access front end on their
machines. They log into the server and use the data they need through
queries, forms and reports. I probably have somewhere around 50-60 queries
that are used to power different forms and reports that various users use.
The tables are backed up each day and the users are responsible for backing
up their forms or reports that they use. We have no problem at all with that
system, provided the server soesn't go down, which is very infrequent.

"AccessNewbie" wrote:

My partner and I developed an access database using queries instead of VBA
because we will not be the ones who will edit and update it and the people
who will do not write VBA but do work extensively with queries. We have now
been told through other people that an experienced designer stated our system
will NOT work, will overload the system and will cause the linked table to
corrupt and disrupt all of the other programs (which are VBA code based)
because of the database being query based. The databasehas been converted to
mde. Is there any problem running the database with approximately 6,000
records and maybe 100 users working on it at one time with a query based
database and the shared records also being accessed by VBA written database?

I'm sorry I was not clear with my description. All of my users do share the
databases across the LAN and have their own icons on their desktops. -- I was
told by an access guru that 2 VBA written access databases and 1 query
designed access database cannot share the same backend files without a lot of
errors and degradation. I do not know of this as a problem but would like to
know if anyone else has seen this problem.

"Golfinray" wrote:

No. You would need separate icons for users to go into the 3 different
databases unless you somehow wanted to link them all together, which is
possible. I would recommend just staying with the 3 separate for ease of use
on your part. Just go to the desktop of each user and put a shortcut to that
database or however you want to set it up. Just be sure they have access to
all the tables they need, most likely on a lan, and you should be good to go.

"AccessNewbie" wrote:

Would there be a problem using a query based front end database with 2 VBA
based front end databases? They all use the same backend database.

"Golfinray" wrote:

I manage a one million record database set up just that way. The tables are
all on a sql server and then users have an Access front end on their
machines. They log into the server and use the data they need through
queries, forms and reports. I probably have somewhere around 50-60 queries
that are used to power different forms and reports that various users use.
The tables are backed up each day and the users are responsible for backing
up their forms or reports that they use. We have no problem at all with that
system, provided the server soesn't go down, which is very infrequent.

"AccessNewbie" wrote:

My partner and I developed an access database using queries instead of VBA
because we will not be the ones who will edit and update it and the people
who will do not write VBA but do work extensively with queries. We have now
been told through other people that an experienced designer stated our system
will NOT work, will overload the system and will cause the linked table to
corrupt and disrupt all of the other programs (which are VBA code based)
because of the database being query based. The databasehas been converted to
mde. Is there any problem running the database with approximately 6,000
records and maybe 100 users working on it at one time with a query based
database and the shared records also being accessed by VBA written database?

AccessNewbie wrote:
I'm sorry I was not clear with my description. All of my users do
share the databases across the LAN and have their own icons on their
desktops. -- I was told by an access guru that 2 VBA written access
databases and 1 query designed access database cannot share the same
backend files without a lot of errors and degradation. I do not know
of this as a problem but would like to know if anyone else has seen
this problem.

Then he is not an "Access guru".

Multiple front ends hitting a shared back end can possibly step on each
other with respect to record locking depending on what they are doing, but
it has nothing to do with mixing front ends that access the data from code
versus from queries.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

AccessNewbie
I develeoped a system here where there are 73 backends and 30 front ends.
I use both VBA and Queries. It has been running extreamly well for 9 years
now.
Its all in the design.

If you want some tip/tricks just email



"Rick Brandt" wrote:

AccessNewbie wrote:
I'm sorry I was not clear with my description. All of my users do
share the databases across the LAN and have their own icons on their
desktops. -- I was told by an access guru that 2 VBA written access
databases and 1 query designed access database cannot share the same
backend files without a lot of errors and degradation. I do not know
of this as a problem but would like to know if anyone else has seen
this problem.

Then he is not an "Access guru".

Multiple front ends hitting a shared back end can possibly step on each
other with respect to record locking depending on what they are doing, but
it has nothing to do with mixing front ends that access the data from code
versus from queries.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

=?Utf-8?B?U2NvdHQgQnVya2U=?=
wrote in :

I develeoped a system here where there are 73 backends and 30
front ends. I use both VBA and Queries. It has been running
extreamly well for 9 years now.
Its all in the design.

You are insane.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"David W. Fenton" wrote:

=?Utf-8?B?U2NvdHQgQnVya2U=?=
wrote in :

I develeoped a system here where there are 73 backends and 30
front ends. I use both VBA and Queries. It has been running
extreamly well for 9 years now.
Its all in the design.

You are insane.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/


It was fun and I learned a lot.
I will NOT do that again!

I hear that SQL is eayier.
Scott Burke

=?Utf-8?B?U2NvdHQgQnVya2U=?=
wrote in :

"David W. Fenton" wrote:

=?Utf-8?B?U2NvdHQgQnVya2U=?=
wrote in
:

I develeoped a system here where there are 73 backends and 30
front ends. I use both VBA and Queries. It has been running
extreamly well for 9 years now.
Its all in the design.

You are insane.

It was fun and I learned a lot.
I will NOT do that again!

I can't understand why there could ever be any justification
whatseover for multiple back ends to any front end -- it sounds like
the data has been split up incorrectly to me.

I hear that SQL is eayier.

For what? What do you use to develop the front end?


--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/