Queries VS VBA Design

[AccessNewbie]

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?

 

[Golfinray]

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]

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]

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]

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.

 

[Rick Brandt]

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.

 

[Scott Burke]

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 "(e-mail address removed)

 

[David W. Fenton]

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.

 

[Scott Burke]

You are insane.

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

I hear that SQL is eayier.
Scott Burke

 

[David W. Fenton]

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?

 

[Scott Burke]

Hi David,
Picture this, A clipper programmer is told he has to develop the
new version of the inhouse software in Access. At this point I did not know
about the news groups and I could not find anyone who had develop software in
access. So I winged it! Grant you I split up the Tables (databases) more
than I needed too.

However, The program was relativily fast, easy to read, easy to
maintain. When we updated the software last year the company we hired
(Pinnicle) got a copy of the software and they were impressed. they really
did not think such a large program could be done in access.

Looking at it now I would do things differently. I wonder how
fast, easy to read, easy to maintain it would be?

How about you David, every look backup and wonder what you were thinking
when you wrote program "X"?

Scott Burke

 

[David W. Fenton]

How about you David, every look backup and wonder what you were
thinking when you wrote program "X"?

Yes, of course. Just this week I've had my nose rubbed in two apps I
wrote back in 1997, and one that dates from before that. Ugly, ugly,
ugly. And incredily hard to maintain because of that ugliness.

 

[Guest]

We maintain the Retail Banking module separate from the Fixed
Interest module. They both lock into the same framework, and
have a shared library. There wouldn't be any particular advantage
to putting the instruments into a shared table, and there would
certainly be no advantage to putting the two tables into the same file.

If they use an ODBC back-end, they are required to put both
tables on the same server, because Access can't handle multiple
ODBC servers, but we don't let that entirely dictate our design.

(david)