International?

J

James

Hello all.

I have a typical database, table with names and addresses on it, split, mde
front end. The company are opening an office is Australia and want the same
database over there.
Now they do not need to see our records over there, but we would like to see
their records on the system here, but not edit them. Is access able to do
this if I set up an automatic email of new records and updates in csv file or
something? Or is it best to use SQL server or something?

ANy suggestions would be brilliant

Many thanks
James
 
J

John Vinson

Hello all.

I have a typical database, table with names and addresses on it, split, mde
front end. The company are opening an office is Australia and want the same
database over there.
Now they do not need to see our records over there, but we would like to see
their records on the system here, but not edit them. Is access able to do
this if I set up an automatic email of new records and updates in csv file or
something? Or is it best to use SQL server or something?

ANy suggestions would be brilliant

Many thanks
James
Click to expand...

Access databases are not designed to work over a WAN (Wide Area
Network). You may want to look into "Replication" - see the
Replication FAQ at

http://support.microsoft.com/default.aspx?scid=kb;en-us;282977

Read and follow it carefully if you decide on this tack - replication
is complex and tricky and needs to be done with care.

Alternatively, you may want to consider exporting the data to .dBase
or text file format (using the TransferText method in VBA, and a query
to select the desired records), and using SendObject to send the
exported file by email. Quite doable.

John W. Vinson[MVP]
 
S

Steve Schapel

James,

I normally handle this kind of situation by this general approach:
1. Make a folder on a web server for the purpose.
2. Make a .mdb file with just the tables needed for the data you want
to see.
3. Put a procedure (use TransferDatabase) in the Australian office's
application so the relevant data is exported to the above .mdb.
4. Put a procedure in the Australian office's application to transmit
the .mdb file via FTP to the web server (I use this contol:
http://www.chilkatsoft.com/ftp-activex.asp).
5. Put a procedure in your local application to transmit the .mdb file
via FTP from the web server.
6. Put a procedure in your local application to import the Australian
data from the downloaded .mdb.

Depending on your requirements, this can be done via scheduler to make
the whole process happen every so often, or you can do it via a button
click, or... other options as well.

Compared with the emailing of the data in a text file, which is also
certainly viable, this is probably a lot more work to set up, but has
the advantage of not requiring manual steps such as saving the file
attachment of the email to the right place, and also for example can
provide an alert if there is an updated file available on the server or
whatever.
 
M

Marcel

James

that is exactly what we are doing here i Australia (but the other way around)
we use an SQL server and access developed ans an ADP it works a treat. just
make sure you make you transactions roll back(able) and because connections
cant be relied upon to be fast check to connection has been confirmed before
triggering instructions plus put all your queries event the trivial one in
the SQL server as stored procedures

regards

Marcel
 
A

Arvin Meyer [MVP]

One last method: I have used SQL-Server and web-based applications, and
replication and found all three to have some faults.

SQL-Server, unless you use the free MSDE is expensive and requires use of
unbound forms for any speed. Unbound forms require more programming, so will
require rewriting much of what you have.

Web-based applications will require a complete rewriting and will cost
significantly more. They are also prone to breaking with every browser
update.

Replication works well but can lead to corruption if the connection goes
down during a database write.

I haven't used ADPs, but they will also require a rewrite of the forms.

My solution, Windows Terminal Services. Additional cost free, if you only
need 1 or 2 connections between XP and Windows 2000 or 2003 servers. Less
than other solutions if you need more connections because not 1 object will
require rewriting, and you can leverage your current skills and knowledge.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
J

James

HI,

THanks for your reply.

I think I may go for Replication but how does it work with id's and linked
records. In my database each member has examination results linked via
'memid' in a one to many relationship. If they create records in Australia it
will create a new memid, will that be different to the one used for that
record over here? How will the system know which exam is with which record?

Can I set the memid to ReplicationID instead of autonumber?

Many thanks

James
 
J

John Vinson

HI,

THanks for your reply.

I think I may go for Replication but how does it work with id's and linked
records. In my database each member has examination results linked via
'memid' in a one to many relationship. If they create records in Australia it
will create a new memid, will that be different to the one used for that
record over here? How will the system know which exam is with which record?

Can I set the memid to ReplicationID instead of autonumber?
Click to expand...

I'd suggest you read the FAQ - it covers all this, better than my
memory can do so.

In short, though, newly created records in a replica are assigned
either *RANDOM* autonumbers (not sequential, in the range -2^31 to
2^31-1, i.e. plus or minus two billion odd), or if you choose, GUID
values. The latter are pretty much sure to be unique but are not
human-friendly (if you want to see typical GUIDs use RegEdit and look
at an assortment of those long cryptic key values).

Human readable member ID's would require that you change your table
structure - one technique I've used is to have each remote site
assigned a site ID, and to assign ID's sequentially within their site.
The Primary Key of the table, and the displayed member ID, consists of
both the SiteID and the member ID field.

John W. Vinson[MVP]
 
M

Marcel

Arvin
Well for a start you use the access upsizing widard to convert you mdb to
and adp that beeing said there will be some grooming needed.

The benifits of using and adp over mdb are significant performance is very
fast and adp requires far less bandwidth

there isnt any performace issue with using bound forms in ADPs

using splitting the interface from the data creates a smaller distirbution
file. because the data is in an SQL server or MSDE all and the queries and
stored procedures are held on the server/MSDE therefor only the result are
transmitted not the entire unprocessed table which is what happens with MDBs
(there goes the performand and bandwidth)

if you need to modify any of the queries you do it on the server/MSDE and
you dont need to redistridute and new version of the interface, whith MDB you
do.

With ADPs you can change what the clients can/cant see on the fly, with MDB
you would need to redistribure a new version.

ADP/server/MSDE is a scalable solution and is far more reliable and can use
Transaction logging

replication is complex and isnt that reliable it depepends the simpler you
application the better but Ive seen it totally fail on larger and complex
application

Using Access with Terminal services is a workaround at best and due to the
nature of Access it will have a serious impact on performance of application
the traffic and bandwidth of using Access this way will grind the system to
its knees.

Replication and terminal srvices in the situation isnt the way to go either
way you go there will be pain but some will create more pain than others it
just depends whether you want some now or alot latter

good luck

Marcel
 
A

Arvin Meyer [MVP]

Several fallacies abound.

I can't speak to ADPs because my experience is limited to testing only. I
can't see any speed advantage using bound forms. Using ASP forms allows IIS
to access the data the way a stored proc would and is very fast. The problem
is that he would have to rewrite his front-end which is expensive. That is
true of an ADP also, although I'm sure that an ADP is significantly less
expensive than ASP.

Splitting the interface is required for *any* application which is
multi-user, to avoid corruption.

Rewriting all the queries to use stored procs is expensive too, although
depending upon the number of users, it may be slightly less expensive than
rewriting the front end to use an ADP.

The entire unprocessed table, is never transmitted if proper indexes are in
place and are used in the query. Only the index from every record is
transmitted, then only the data that fits the requested index is sent. It is
still significantly slower over a WAN. Too slow to be useful.

Terminal Servers are as fast, or faster, as most other server based schemas.
Performance with an MDB is never a problem with terminal services. Our
Terminal Server returns a few dozen records from an 80,000 row table in a
second or less. Terminal services are even useful with dialup.

Replication's biggest drawbacks are that data isn't always sync'd and
there's an increase risk of corruption, especially over dialup.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
M

Marcel

ADP is the Access Database Project. It dosnt require IIS nor active server
pages nor a total rewrite as a web application as to is access as an
interface to an SQL or MSDE database.
you use the access upsizing wizard to convert the data base from an mdb to
and adp. It not a big deal and aslo means that he dosnt have to rewrite the
interface....

Now he can live with the queries and leave them insitue or he can (overtime
cut them out of his code and paste them into the stored procs ) is not a big
issue.

Access transmits the entire table to the client for processing!!!! with the
ADP/SQL/MSDE it done on the server and the results are transmtitted.

You dont know what you are talking about when it comes to terminal services.
Terminal services needs significant bandwidth to opperate with any sence of
normallity I have unfortunatly been involved in serveral inplementations (for
very large financial institutions in Australia ) of terminal services and
other thin client systems. Terminal Services needs to be understood from the
point that it is a window on the server and every interaction is a new screen
transmitted accross the network this causes absolute grief, Combo/ list boxes
any this that moves or rolls over needs to be changed. In every case where
terminal service was implemented it was replaces within 12 months by more
standard solutions either web or hybrid/client server.

Not one of them lived up to the hype and that is why the world isnt beating
a path the the terminal services door.

the only inplementation for Terminal Services would be where you have a
contained network on a 10/100 baseT network maybe a school and an office but
never in a WAN situation.
 
M

Marcel

Just on another Ive just finished developing an Access ADP with SQL on the
back end and at the same time another developer was building a for another
project but using MDB access format. Mine works interationally and his needs
to be rebuild because it has ground to a halt with only 40 ppl using it
accross several location in the same city! mind you they are all accessing
the application from a shared location


regards

Marcel
 
T

Tom Wickerath

Marcel,
Access transmits the entire table to the client for processing!!!!
Click to expand...

This is a common misperception, and I will say that material published in
various books, and on Microsoft's web site, contributes to this
misinformation. I was corrected a few years ago on this. I refer you to this
thread. Read the entire thread, but pay particular attention to post #10 by
John Viescas:

http://groups.google.com/group/micr...hread/136cc58bf85b8e85/9be23e5e1a7eb151?tvc=1

While I have never used Terminal Server, I suspect that your assertions
about it's performance are a bit misguided. One can read many testimonials
from very well respected regular contributors to this newsgroup, concerning
very good performance using Terminal Server. One can only conclude that
something was not correctly optimized in your case.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

ADP is the Access Database Project. It dosnt require IIS nor active server
pages nor a total rewrite as a web application as to is access as an
interface to an SQL or MSDE database.
you use the access upsizing wizard to convert the data base from an mdb to
and adp. It not a big deal and aslo means that he dosnt have to rewrite the
interface....

Now he can live with the queries and leave them insitue or he can (overtime
cut them out of his code and paste them into the stored procs ) is not a big
issue.

Access transmits the entire table to the client for processing!!!! with the
ADP/SQL/MSDE it done on the server and the results are transmtitted.

You dont know what you are talking about when it comes to terminal services.
Terminal services needs significant bandwidth to opperate with any sence of
normallity I have unfortunatly been involved in serveral inplementations (for
very large financial institutions in Australia ) of terminal services and
other thin client systems. Terminal Services needs to be understood from the
point that it is a window on the server and every interaction is a new screen
transmitted accross the network this causes absolute grief, Combo/ list boxes
any this that moves or rolls over needs to be changed. In every case where
terminal service was implemented it was replaces within 12 months by more
standard solutions either web or hybrid/client server.

Not one of them lived up to the hype and that is why the world isnt beating
a path the the terminal services door.

the only inplementation for Terminal Services would be where you have a
contained network on a 10/100 baseT network maybe a school and an office but
never in a WAN situation.
 
S

Steve Schapel

Good information, Tom. Thanks.

I got a good laugh out of Marcel's assertion that Arvin Mayer doesn't
know what he is talking about!

I guess there is one thing that seems to have got lost here... there is
no such thing as the best solution in all situations. One approach will
be effective (and cost-effective) in one scenario, but another approach
needed in another. I interpreted James's original post to mean that the
data relating to the Australian office is not critical to their own
operations, so wouldn't need live access to the up-to-the-minute
database. I imagined a periodic update would serve the purpose. I was
pre-disposed to this interpretation because I have worked on several
projects lately where daily or weekly data updates from remote sites is
ideal for the business requirements. Normally you can provide this sort
of functionality a lot quicker and cheaper than any solution involving
remote access to the live data. But if access to current data is
required, then Terminal Services would seem the best way to go.
 
T

Tom Wickerath

Hi Steve,

You're welcome. I ate humble pie on that mistake two years ago, which I
continue to find printed in the literature.
I got a good laugh out of Marcel's assertion that Arvin Mayer doesn't
know what he is talking about!
Click to expand...

I did too, but I was trying to correct him by pointing out my own past
mistaken belief.

Ummm.....don't go confusing Arvin Meyer with that other "Mayer" named Oscar!
<smile>
http://www.kraftfoods.com/om/


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Good information, Tom. Thanks.

I got a good laugh out of Marcel's assertion that Arvin Mayer doesn't
know what he is talking about!

I guess there is one thing that seems to have got lost here... there is
no such thing as the best solution in all situations. One approach will
be effective (and cost-effective) in one scenario, but another approach
needed in another. I interpreted James's original post to mean that the
data relating to the Australian office is not critical to their own
operations, so wouldn't need live access to the up-to-the-minute
database. I imagined a periodic update would serve the purpose. I was
pre-disposed to this interpretation because I have worked on several
projects lately where daily or weekly data updates from remote sites is
ideal for the business requirements. Normally you can provide this sort
of functionality a lot quicker and cheaper than any solution involving
remote access to the live data. But if access to current data is
required, then Terminal Services would seem the best way to go.
 
M

Marcel

Im the fact are the fact re terminal services and Iver seen several IT savvy
IT organisations fail to implement it with any level of satisfactory
performance I would maybe they should have come and talked to you LOLROF
 
S

Steve Schapel

Tom said:
Ummm.....don't go confusing Arvin Meyer with that other "Mayer" named Oscar!
Click to expand...

Yeah, I was aware of the spelling error as soon as I'd hit Send on that
one. Sorry, Arvin :)
 
A

Arvin Meyer [MVP]

For some reason, I must have been dislexic reading ADP as DAP. Sorry. Yes, I
do have quite a bit of experience with ADPs, having written several of them
over a 2 1/2 year period starting in the fall of 2001.

Yes, ADPs work nicely over a WAN, but not without extensive changes to
rewrite all the queries and select statements to use views and stored procs.
I did not use the MSDE engine, mostly because I didn't want to deal with a
throttled down server and the Enterprise Manager and Query Analyser tools
are not available without the full SQL-Server. The one serious drawback I
found was that T-SQL has nowhere near the power of VBA and there were many
things that I simply couldn't do. Some of that will be corrected in the
newest version of SQL-Server which can resolve some .NET code.

Sorry, you are incorrect about the entire table being transmitted when
using JET. That is not the case when indexes are properly built and used.
The front-end requests data and gets the entire index, NOT the entire table.
It then goes back to the server using the key from the rows it finds locally
and fetches only those rows. You are correct that the processing takes place
on the workstation, but you are incorrect that the entire table is
transmitted.

We have been heavily involved with terminal servers for the past 3 or 4
years. Two of my clients (1 with 50 concurrent users, and 1 with 8) have
been using it for that amount of time, and I've implemented it on my own
company's network almost a year ago. Our own usage varies from 5 to 20
concurrent users. For those users running Access 2002 on 1.8 GHz machines
with 512 MB of RAM, the terminal server (3.0 dual Xeon with 4 GB RAM) is
significantly faster over broadband, both cable and DSL. The speed
difference is at least 50% on cable. Even my own development machine (3.1
GHz, 1 GB RAM) is out classed when trying to compete against that server.

For under 30 users on a WAN, I'd never recommend spending the money on
SQL-Server rather than on a Terminal Server with JET unless security was of
prime concern. It's far cheaper to build mature MDB front-ends running JET
back-ends than any other DBMS.

As far as not knowing what I'm talking about, sorry but I have to differ
once again. I have 13+ years experience with Access and 24 total years
experience with databases and computers. The 10's of thousands of posts, and
numerous articles I've written speak for themselves, as does my renomination
as an MVP for the 6th straight year. Your experience may differ, but it is
doubtful that it is more extensive.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
A

Arvin Meyer [MVP]

Marcel said:
Im the fact are the fact re terminal services and Iver seen several IT savvy
IT organisations fail to implement it with any level of satisfactory
performance I would maybe they should have come and talked to you LOLROF
Click to expand...

Sorry, I happen to work for a Fortune 500 company, as a lead developer. It
is my experience that many IT decisions are made on the golf course, and not
as a result of extensive work in the field. If I had a nickel for every IT
decision made as a result of mis-information, I'd be living in the lap of
luxury.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
A

Arvin Meyer [MVP]

Without Terminal Services or an ASP front-end, JET will never perform as
fast as any server based DBMS. Performance with even a single user will
probably be unsatisfactory over a WAN. With 40 concurrent users, the full
version of SQL-Server will usually be significantly faster too.

The original poster was asking about what to do with an existing Access MDB
database. In that case, it is significantly cheaper (and faster to get into
production) to buy a terminal server (especially since they'll need at least
1 new server anyway) than to convert to an ADP running SQL-Server.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
J

James

Hello,

Many thanks for all your information, didnt know I would get such a good
response.

I don't need live data as Enland and Australia are 12 hours appart so one
will be asleep when the other is at work. I was thinking of twice a day
refresh or syncronise at the end of the working day for each, so ready for
the morning of the other.

Maybe I am a little over my head. Bearing in mind it has to be quick as the
office is opening begining of January should I look in to Replication,
Terminal Servers or an automated TransferDatabase method? I can look into
ADP's later but short on time at the moment.

Again thankyou all for your responses.

James
 
Top