Suggestions on speeding up Access

[CAM]

Hello,

I need help. I build a claims inventory database, which I splited in two
databases. The first database is the front end (queries, forms, reports,
macro) the second database is where the tables are. Both databases are
located in our server in the main branch in Los Angeles it is password
protected, shortcuts were applied from the server side (Front-end) onto the
individual user PC desktop (about 8 users). - I did this because I would not
have to go to the indidivual user's PC and applied any new updates on there
desktop computer (if there is a better, please let me know) I know for
processing speed the front-end should reside on the user's PC, but I think
it maybe a pain to do any updates. Everything works fine for our Los
Angeles branch, but I recently gone to Florida our East coast branch and put
shortcuts onto about 10 users PC's where they can access the Los Angeles
branch Front-End as well as the Back-End, but it took almost a minute to
open up a form and later we had some corruptions issues on the form. We are
using T1 lines both in the Los Angeles and Flordia branches. I was forced
to create another front-end and back end for our Florida branch, which work
well for them. The Los Angeles branch could not even access the Florida
branch server (time out issues - Network problem?) My question is there a
better or faster way both branches can access the database? Is there
something a network tech can do to speed up the process? All I want is to
have both branches accessing the back-end database on a single server. If I
put the front-end onto the individual users PC and have both branches access
the backend on a single server will this be any faster or somewhat? We even
have a branch in Texas (mid point) so we can put the back-end there if that
would help. I am trying to make this an enterprise wide database, but
Access doesn't seem to work well. Would using Visual Basic Net for the
front end and using Access as the back end will be any better? or Visual
Basic Net as the front-end and use SQL server? or What about Access Web
Page will that be any better? Is there such a thing call replication in
Access and would it work and where do I find out how to do that? All I
want is to have both branches accessing from the same server so they can
both see the same data - they can have the front-end reside on the indivdual
user PC's. We are only talking about 6,000 records a month. I would
appreicate help from any professional who expericed this and what did they
did to overcome this problem or website to visit to get more information.
Thank you in advance.

 

[RuralGuy]

Hello,

I need help. I build a claims inventory database, which I splited in two
databases. The first database is the front end (queries, forms, reports,
macro) the second database is where the tables are. Both databases are
located in our server in the main branch in Los Angeles it is password
protected, shortcuts were applied from the server side (Front-end) onto the
individual user PC desktop (about 8 users). - I did this because I would not
have to go to the indidivual user's PC and applied any new updates on there
desktop computer (if there is a better, please let me know) I know for
processing speed the front-end should reside on the user's PC, but I think
it maybe a pain to do any updates. Everything works fine for our Los
Angeles branch, but I recently gone to Florida our East coast branch and put
shortcuts onto about 10 users PC's where they can access the Los Angeles
branch Front-End as well as the Back-End, but it took almost a minute to
open up a form and later we had some corruptions issues on the form. We are
using T1 lines both in the Los Angeles and Flordia branches. I was forced
to create another front-end and back end for our Florida branch, which work
well for them. The Los Angeles branch could not even access the Florida
branch server (time out issues - Network problem?) My question is there a
better or faster way both branches can access the database? Is there
something a network tech can do to speed up the process? All I want is to
have both branches accessing the back-end database on a single server. If I
put the front-end onto the individual users PC and have both branches access
the backend on a single server will this be any faster or somewhat? We even
have a branch in Texas (mid point) so we can put the back-end there if that
would help. I am trying to make this an enterprise wide database, but
Access doesn't seem to work well. Would using Visual Basic Net for the
front end and using Access as the back end will be any better? or Visual
Basic Net as the front-end and use SQL server? or What about Access Web
Page will that be any better? Is there such a thing call replication in
Access and would it work and where do I find out how to do that? All I
want is to have both branches accessing from the same server so they can
both see the same data - they can have the front-end reside on the indivdual
user PC's. We are only talking about 6,000 records a month. I would
appreicate help from any professional who expericed this and what did they
did to overcome this problem or website to visit to get more information.
Thank you in advance.

Here's a link you may find useful:
http://www.granite.ab.ca/access/performancefaq.htm

Remember that a T1 line is *only* 1.5Mb/sec. Your LAN is probably 100 Mb/sec ot
66 times faster as a reference. You may want to look onto terminal servers to
use the Internet and get acceptable performance.

Look around on Tony's site. There is a lot of knowledge documented there
including automatically updating a front end:
http://www.granite.ab.ca/access/autofe.htm

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[Albert D.Kallal]

As the other poster mentioned...you are using a very slow connection...at
least 60 to 100 times slower...

So, 10 seconds in your office LAN can become 1000 seconds....(16 minutes)

So, the #1 answer is you are using a very very slow network as compared to
your office network.

I did this because I would not have to go to the indidivual user's PC and
applied any new updates on there

Yes, but for every other piece of software on your machine, you installed
the software
on EACH pc..and in those cases, for any software updates...you have to
install the
updates to each machine. It makes absolute NO sense to drag the appcation
across the network. I explain in detail WHY you split here:

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

Pay careful attention to the above notes as to why you will NOT HAVE
ANY stability in the application if you don't place a copy on each machine.

As for the connection, and improving the speed of remote users, you can read
the following article, and in this article I also give solutions to the
performance
problem you are experiencing.

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

 

[CAM]

Thanks Rural Guy.

Here's a link you may find useful:
http://www.granite.ab.ca/access/performancefaq.htm

Remember that a T1 line is *only* 1.5Mb/sec. Your LAN is probably 100
Mb/sec ot
66 times faster as a reference. You may want to look onto terminal
servers to
use the Internet and get acceptable performance.

Look around on Tony's site. There is a lot of knowledge documented there
including automatically updating a front end:
http://www.granite.ab.ca/access/autofe.htm

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[CAM]

Thanks Albert.

As the other poster mentioned...you are using a very slow connection...at
least 60 to 100 times slower...

So, 10 seconds in your office LAN can become 1000 seconds....(16 minutes)

So, the #1 answer is you are using a very very slow network as compared to
your office network.


Yes, but for every other piece of software on your machine, you installed
the software
on EACH pc..and in those cases, for any software updates...you have to
install the
updates to each machine. It makes absolute NO sense to drag the appcation
across the network. I explain in detail WHY you split here:

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

Pay careful attention to the above notes as to why you will NOT HAVE
ANY stability in the application if you don't place a copy on each
machine.

As for the connection, and improving the speed of remote users, you can
read
the following article, and in this article I also give solutions to the
performance
problem you are experiencing.

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[Archidrb]

Albert,

I read your article last year and put it to use almost immediately.
However, those guys down in the CT department have pushed us to a SQl backend
now. Okay, I'm fine with that, but I cannot get my front ends to work with
the SQL backend. I can make a single front end work perfectly with the BE,
but as soon as there is a replica also connecting to the SQL BE I lose all
ability to, well, do anything. I get errors, connection issues, and each
time have to back up and start again.

Any suggestions for using the SQL BE and multiple Access front ends? I like
the synchronization/replication process for making sure the users are using
the most recent update, but I have read a few articles today that there are
other ways to do that without using the built in replication features.

Darren

 

[Albert D. Kallal]

Albert,

I read your article last year and put it to use almost immediately.
However, those guys down in the CT department have pushed us to a SQl
backend
now. Okay, I'm fine with that, but I cannot get my front ends to work
with
the SQL backend. I can make a single front end work perfectly with the
BE,
but as soon as there is a replica also connecting to the SQL BE I lose all
ability to, well, do anything.

What do you mean by replica? Your front end is an appcation, and as such
like any program, you don't use replication for that front end. Replication
is for data only, and certainly not for the application part that you write.
Perhaps you using the term replicas loosely, but if you talking about
replication, then you CAN NOT be talking about the front end part. Further,
there is no seamless way to replicate data from a back end mdb to sql
server. (you have to roll you own update code, or simply have the front end
always connect to the sql back end). You might wan to clear up what you mean
by replication, but for the font end in ms-access, replication is never
used, and since the front end has no data, then it should never have been a
issue anyway...

The idea here is that you setup a font end. When you deploy this front end
to each machine, you have to have something that connects to the back end
(in this case, we assuming sql server).

What this means is that when your application starts (and, you are deploying
a mde..right? ), you need to ensure that users workstation connects to sql
server.

the best way to accomplish this connection is to use a dsn-less connection.
there example here:

Using DSN-Less Connections
http://www.accessmvp.com/djsteele/DSNLessLinks.html

 

[Archidrb]

Well, I've been looking for external confirmation of this. I have suspected
for several years that in order to accomplish many of the goals of my three
databases I would need the Developers Edition. This settles it. I even had
a Help Desk (****) once try to tell me there was no such product and another
tell me that there is never a need for that version.

As soon as have the correct version of Access, I will try all of these
things again.

Remind me at some point in the future to explain what Replication in Access
is and how it is completely opposite of what it is in SQL. There is a lot of
back and forth arguments on this board that demonstrates that SQL DBAs don't
know about Access and Access DBAs need to learn about SQL.

Next time you are in a .mdb file, go to Tools>Replication>Create Replica.
It is a completely different process that you think and totally opposite of
SQL.

Darren

 

[Aaron Kempf]

a) don't listen to these MDB kids
b) move to Access Data Projects
c) learn the enterprise level tools that come with SQL Server DEvelopers
Edition for tuning indexes, etc

 

[Aaron Kempf]

this SQL DBA _DOES_ know Access like the back of his hand and he HATES mdb

 

[Aaron Kempf]

best way to share is to use Access Data Projects

use SQL Server replication if you must.. but that's a great option.. SQL
Server Replication is VERY reliable

 

[Albert D. Kallal]

Well, I've been looking for external confirmation of this. I have
suspected
for several years that in order to accomplish many of the goals of my
three
databases I would need the Developers Edition.

NO!...

the developers edition of ms-access DOES NOT change any of the features, or
process in which you develop ms-access applications.

In fact, the developers edition simply gives you the runtime. You can deploy
that runtime to the target machine, and then from that point on, you can
simply copy a mde (or perhaps a mdb) file to that computer, and it will run.
There is no special "change" when you purchase access developers tools, and
there not some "special" feature that you somehow gain, or get, or use when
you have the developers edition. I repeat, the developers edition does NOT
requite you change your current mdb that you are developing right now. THE
ONLY THING that the developers edition gives you is that runtime, and once
that
runtime is installed, you simply copy your mde to that target machine, and
it
works. There is NOTHING special in that edition that gives you an EXTRA
ability to solve your problems.

This settles it. I even had
a Help Desk (****) once try to tell me there was no such product and
another
tell me that there is never a need for that version.

The above advice is correct IN A GIVEN context. In other words, if you
already have ms-access, then obtaining the developers edition will
ACCOMPLISH ZERO
for you.

Now, of course the delvers edition is GREAT if you going to deploy your mde
to a machine that does not yet have ms-access (so, you would install the
runtime, and then simply copy your existing mdb to that target machine. The
only real differences in the runtime edition is that you can't (obviously)
have
forms opened in design mode, and you have to provide your own menus (or use
a
switchboard).

As soon as have the correct version of Access, I will try all of these
things again.

Once again, you need to clarify what you mean by the above, but obtaining
the
"correct" version of ms-access will accomplish zero for you in terms of your
stated goals.

Next time you are in a .mdb file, go to Tools>Replication>Create Replica.
It is a completely different process that you think and totally opposite
of
SQL.

Yes, it is. However, you in one part of your post mentioned sql server, and
then mention replication? What replication are you talking about ? (jet
based,
access replication, or sql server replication? -- you need to be aware that
if
you drop a standard mdb for your back end, and replace it sql sever, then
you CAN NOT use jet (ms-access) based replication anymore. JET replication
only works when you have a back end that is a mdb. If you stated goal is to
move that back end to sql server, then you can't use jet based replication
anymore.

and, please accept my apologies for the other knob who butted into this
thread, hi-jacked it..and made a mess (that fellow is a known disruption
person, and loves to damange converatsions here. He is making this thread
VERY difficult for you and me to have a sensible conversation.

 

[Guest]

ability to, well, do anything. I get errors, connection issues, and each

google groups for

"ACCESS 2003 AND SQL 2005 AND REPLICATION"

(david)

 

[Larry Linson]

Well, I've been looking for external confirmation of this.
I have suspected for several years that in order to accomplish
many of the goals of my three databases I would need the
Developers Edition. This settles it. I even had a Help Desk
(****) once try to tell me there was no such product and another
tell me that there is never a need for that version.

There is an Office Developer Edition for Office XP, and some earlier
versions. If you are using Access 2003, the Help Desk was correct -- there
is no Developer Edition for Access 2003 or 2007.

My question is: what do you expect to gain from the Developer Edition?
Generally, its usefulness for Access developers is to allow packaging your
database with runtime support so users do not have to have Access installed
on their machine. Different editions of the Developer Edition also included
a few ActiveX Controls, but few that were essential to even complex Access
applications.

Larry Linson
Microsoft Access MVP

 

[Tony Toews [MVP]]

There is a lot of
back and forth arguments on this board that demonstrates that SQL DBAs don't
know about Access and Access DBAs need to learn about SQL.

Umm, not quite. There is a lot of argument between A a r o n K e m p
f and his alter ego's and the rest of the folks in this newsgroup.

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

 

[Tony Toews [MVP]]

Far too many
start out in databasing at the SQL level and never look back, thus missing
this funnest part of the whole DB process - the user interface. Is it really
all that fun writing constraints for data management in comparison to the joy
of making something be as easy to use as a pen?

That's what I enjoy the most. Make Access follow the business process
so the user just uses the tool as part of their job. Tweak it so that
it is easy to use. Remove the irritants. Save the user time. But
the business rules in Access.

And I do very well at that.

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

 

[Aaron Kempf]

how can I hate a front end product?

I don't hate Access as a front end.

As a front end... Access is wonderful.

As a DATABASE
As an ETL tool

Access MDB sucks balls

 

[Aaron Kempf]

wtf don't use JET REPLICATION dude
get MSDE on your desktop; tell the network admin kid that MSDE 2.0 is an
optional component ON THE OFFICE DISK. IF YOU DO NOT GIVE IT TO ME, YOU
NEED TO GIVE ME A DECENT EXPLANATION WHY I CANNOT HAVE AN OPTIONAL COMPONET
ON THE OFFICE DISK.

Take it to management.

If they have a clue; they'd _LOVE_ for you to 'grow into SQL Server'

 

[Aaron Kempf]

yeah.. there are a lot of arguments

because you kids are a bunch of worthless dorks; you're all stuck in the 1st
grade of the technology world

MDB is for kids and retards

 

[David W. Fenton]

Further,
there is no seamless way to replicate data from a back end mdb to
sql server. (you have to roll you own update code, or simply have
the front end always connect to the sql back end).

That is not entirely true, Albert. Jet 4 added the capability to
participate as a merge replication subscriber to a SQL Server
replicated database. This is called heterogeneous replication and is
a really great way to have laptop users be able to take an app into
the field that has a SQL Server back end -- no need to install SQL
Server on the laptops, just use an MDB that is a subscriber to the
SQL Server replicated database.

Now, all that said, I've never used it myself -- I just know it
exists.

But I do know for a fact that your statement is not true. It may be
so that it isn't seamless, but you don't have to do it yourself at
all.