Synching databases (Access MDBs)
PW
Mom is in one state, daughter is in another. Mom mostly takes
reservations over the phone, and once in a while the daughter will.
Both can be entering in new clients.
I am trying to think if synching the two databases (most like will be
emailing them back and forth or using Remote Desktop) would be
possible.
One main table:
Clients!ClientID (Parent)
Then multiple supporting child tables:
Reservations!ReservationID which links to Clients!ClientID
RoomAssignments!AssignmentID which contains both ClientID and
ReservationID
Activities!ActivityID which relates to Reservations!ReservationID
(using Activities!ReservationID)
And various other child records.
All ID fields are autonumbers so they can not be relied on to relate
properly to both databases. ID fields will not always match up, at
least with new records.
I am trying to think how ActiveSync, for instance, synchs with mobile
devices. User specifies if "mom" overides "daughter", or "daughter"
overides "mom" or both synch together.
Any ideas if this is possible (and how)?
-paulw
Rich P
a sql server backend. Access is file based and is not well suited for
wide area network operations.
Rich
*** Sent via Developersdex http://www.developersdex.com ***
Arvin Meyer
user is still using their own copy of the front-end.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access
"PW" <emailad...@ifIremember.com> wrote in message
news:pbv0u5h908e4u73fr...@4ax.com...
PW
wrote:
>For just 2 users, remote desktop is the way that I'd go. Make sure that each
>user is still using their own copy of the front-end.
So I would just use my table reattachment module which brings up a
window for the user to point to where the data file is and she would
be able to see the mdb on the remote pc?
Is that how it would work in this case with only the front-end on one
PC?
-paulw
PW
Thanks Rich
-paulw
David W. Fenton
news:pbv0u5h908e4u73fr...@4ax.com:
> Mom is in one state, daughter is in another. Mom mostly takes
> reservations over the phone, and once in a while the daughter
> will. Both can be entering in new clients.
>
> I am trying to think if synching the two databases (most like will
> be emailing them back and forth or using Remote Desktop) would be
> possible.
I think for this purpose, I'd go with hosted Sharepoint and use it
to synch between databases. Dunno if that works reliably and
efficiently with what's currently available (A2007), but what's
coming with A2010 and Sharepoint 2010 with Access Services would
serve the purpose quite well.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Arvin Meyer
have a folder with a second copy of the front-end. Remember, there is no
difference (effectively) between a local user and a remote user. I would
think that without a terminal services server, there may be some performance
issues. What I've always done in that situation is to connect to a running,
but unused PC (like logging into my workstation at work, from a laptop while
on the road)
Two people cannot really use the same workstation remotely. They wind up
fighting for control of the mouse, keyboard, etc. There are PC thin client
programs that function like a server though. Here's 1:
http://www.thinsoftinc.com/product_thin_client_winconnect_server_vs.aspx
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access
"PW" <emailad...@ifIremember.com> wrote in message
news:f1k1u5ta4noq8dsqq...@4ax.com...
PW
Hi Arvin,
>If you are connecting through a PC instead of a server, make sure that you
>have a folder with a second copy of the front-end.
I am not sure what you mean or why that is necessary. That would be
on the PC connecting? The data would most likely be somewhere else
than the PC that she would be connected to (another PC, or a server)
as there may be mutliple users there.
Thanks,
-paulw
PW
<XXXu...@dfenton.com.invalid> wrote:
>PW <emailad...@ifIremember.com> wrote in
>news:pbv0u5h908e4u73fr...@4ax.com:
>
>> Mom is in one state, daughter is in another. Mom mostly takes
>> reservations over the phone, and once in a while the daughter
>> will. Both can be entering in new clients.
>>
>> I am trying to think if synching the two databases (most like will
>> be emailing them back and forth or using Remote Desktop) would be
>> possible.
>
>I think for this purpose, I'd go with hosted Sharepoint and use it
>to synch between databases. Dunno if that works reliably and
>efficiently with what's currently available (A2007), but what's
>coming with A2010 and Sharepoint 2010 with Access Services would
>serve the purpose quite well.
Thanks David. I don't know anything about Sharepoint but will do some
research.
-paul
Arvin Meyer
cause a corruption.
A server is a standalone machine that stores and serves files. No one works
on a server. That means in a 2 user situation you have 3 machines, a server
and 2 PCs. If you are using a peer to peer network (only 2 machines) only
one user should be working at a time (for terminal services). On a
peer-to-peer LAN there can be 2 machines without a server, and both users
can work at the same time.
In ALL cases you MUST use a separate front-end, linked to the data, for EACH
user. Anyone who tells you otherwise is wrong. If you do not have separate
front-ends you will corrupt eventually.
I can't make it any plainer than that.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access
"PW" <emailad...@ifIremember.com> wrote in message
news:dur1u5lvka7g7c1b5...@4ax.com...
PW
wrote:
>It is necessary because multiple users on the same front-end WILL eventually
>cause a corruption.
>
>A server is a standalone machine that stores and serves files. No one works
>on a server. That means in a 2 user situation you have 3 machines, a server
>and 2 PCs. If you are using a peer to peer network (only 2 machines) only
>one user should be working at a time (for terminal services). On a
>peer-to-peer LAN there can be 2 machines without a server, and both users
>can work at the same time.
>
>In ALL cases you MUST use a separate front-end, linked to the data, for EACH
>user. Anyone who tells you otherwise is wrong. If you do not have separate
>front-ends you will corrupt eventually.
We do have the code seperated from the data. Multiple users are using
it on a LAN for the most part, some have a server.
I did not understand what you meant by " make sure that you
have a folder with a second copy of the front-end."
Maybe if you reread what I said it will make it through this time.
Arvin Meyer
"David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
news:Xns9D6EE93A589E2f9...@74.209.136.90...
> I think for this purpose, I'd go with hosted Sharepoint and use it
> to synch between databases. Dunno if that works reliably and
> efficiently with what's currently available (A2007), but what's
> coming with A2010 and Sharepoint 2010 with Access Services would
> serve the purpose quite well.
Sharepoint is more complex, and is not fully relational. AAMOF, only Access
2010 has any relational capability with Sharepoint at all. Sharepoint
hosting is also expensive for a small operation. There are no free
Sharepoint hosts. and the cheapest I've seen is $20 per month. A fully
operation Sharepoint server with licensing is $80 per month + $7.50 per
user.
In Contrast, a cheap PC running as a server with WinConnect, might cost a
total of $700 to $800 for up to 3 users. And just running Remote services
for a single user costs nothing.
Roger
>
> - Show quoted text -
your original post talks of 2 people, now you're are talking of many
people, some on a lan, some with servers
lets go back to just 2 people
right now they have their own computer, with a frontend and a backend
MDB, correct ?
if you want them to both work at the same time, you need a third
computer to act as a server
that computer will have the backend MDB and 2 copies of the front end
mdb (one for mom and on for sis)
that computer can physically be at mom's house, sis's house or your
house
Arvin's link to http://www.thinsoftinc.com/product_thin_client_winconnect_server_vs.aspx
will allow you to set up this 'server' so that both users can RDP to
it and run their copy of the frontend
you could allow RDP to it to do backups, updates, etc
David W. Fenton
news:oLadndZyCpgOMXzW...@earthlink.com:
>
> "David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
> news:Xns9D6EE93A589E2f9...@74.209.136.90...
>
>> I think for this purpose, I'd go with hosted Sharepoint and use
>> it to synch between databases. Dunno if that works reliably and
>> efficiently with what's currently available (A2007), but what's
>> coming with A2010 and Sharepoint 2010 with Access Services would
>> serve the purpose quite well.
>
> Sharepoint is more complex, and is not fully relational.
Sharepoint 2010 has enough features to make it relational enough,
and where it's weak, you can implement triggers.
> AAMOF, only Access
> 2010 has any relational capability with Sharepoint at all.
Er, what? Are you saying the regular Sharepoint database doesn't
have it?
> Sharepoint
> hosting is also expensive for a small operation. There are no free
> Sharepoint hosts. and the cheapest I've seen is $20 per month. A
> fully operation Sharepoint server with licensing is $80 per month
> + $7.50 per user.
I haven't looked into it. I assumed it would be priced similarly to
Exchange Server, which is closer to $20/month for full service.
> In Contrast, a cheap PC running as a server with WinConnect, might
> cost a total of $700 to $800 for up to 3 users. And just running
> Remote services for a single user costs nothing.
I would agree that some version of Terminal Services is probably the
easiest to implement, but I'm not sure it's the right solution for
really small groups of users (like two).
Roger
wrote:
> "Arvin Meyer" <arv...@invalid.org> wrote innews:oLadndZyCpgOMXzW...@earthlink.com:
>
>
>
> > "David W. Fenton" <XXXuse...@dfenton.com.invalid> wrote in message
just wondering, what other solutions allow you to have 2 people share
the current ms-access application ?
terminal services ?
data syncing ? how reliable is this ms-access feature ?
hosted sharepoint ? how much would that cost / month ?
how reliable in the access2007 version ?
if PW's app, is pre 2007, s/he'd need to
upgrade
what other solution is there ?
David W. Fenton
news:30b74056-58e8-4733...@i10g2000yqh.googlegroups.co
m:
> just wondering, what other solutions allow you to have 2 people
> share the current ms-access application ?
>
> terminal services ?
This is what I would consider the most efficient solution. It's
really cheap to implement in an environment where there's already a
terminal server -- not so much for two users.
> data syncing ? how reliable is this ms-access feature ?
It's perfectly reliable if done correctly. But for the kind of
scenario you identify, it's complex to set up and needs to be
designed and implemented by someone who understands all the issues.
Jet Replication Wiki:
http://dfenton.com/DFA/Replication/
> hosted sharepoint ? how much would that cost / month ?
This is the direction I plan on investigating this summer because I
believe it's the best replacement for Jet replication.
> how reliable in the access2007 version ?
> if PW's app, is pre 2007, s/he'd need to
> upgrade
So far as I'm aware, it's just as reliable as previous versions of
Access. To use the full features of current versions of Sharepoint,
and upgrade would be needed.
> what other solution is there ?
Outside of a forum for Access developers, you'll find that the only
thing people will recommend is the "obvious" solution to re-write
the application as a browser-based app. They don't seem to
understand how complex that is and how much functionality you give
up.
One of the fabulous things about Access Services with Sharepoint
2010 and an Access web app run in the browser is that you give up
nothing at all -- the result is identical to the same app running
within Access.
This is huge and ultimately seems to me that it will be very
popular, even among those who'd never have considered Access in the
past. But I could be overoptimistic in that.
PW
I guess I was getting greedy <g>. Nah, just trying to cover all
scenerios.
>lets go back to just 2 people
>right now they have their own computer, with a frontend and a backend
>MDB, correct ?
Yes.
>
>if you want them to both work at the same time, you need a third
>computer to act as a server
>that computer will have the backend MDB and 2 copies of the front end
>mdb (one for mom and on for sis)
>
>that computer can physically be at mom's house, sis's house or your
>house
Got ya. I will check with them.
>Arvin's link to http://www.thinsoftinc.com/product_thin_client_winconnect_server_vs.aspx
>will allow you to set up this 'server' so that both users can RDP to
>it and run their copy of the frontend
>
>you could allow RDP to it to do backups, updates, etc
Thanks very much. I understand now. I just did not understand
Arvin's two folder thing. I will check out his article.
Thanks guys!
-paul
PW
wrote:
>
Thanks Arvin. Good stuff.
-paulw
PW
<XXXu...@dfenton.com.invalid> wrote:
But it might be for our larger clients. Good to know in case they
ask.
Thanks.
-pw
rkc
something to consider.
Cheap. Easy to set up. Don't have to change anything but the links in
the frontend .mdb file.
Sharepoint would be a ridiculously complicated and expensive solution
for 2 users.
Arvin Meyer
"PW" <emailad...@ifIremember.com> wrote in message
> Thanks very much. I understand now. I just did not understand
> Arvin's two folder thing. I will check out his article.
If you are using RDP (a remote client) each person must log into their own
copy of the front-end. SHARING A FRONT-END IS A RECIPE FOR CORRUPTION, on
any server, even on a terminal server. To avoid that on a terminal server
you place the copy of the front-end into a folder that ONLY that person will
access. So, there's a front-end.mdb in the PW folder, and a front-end.mdb in
the Arvin folder. Arvin logs on ONLY uses the copy of in his folder. If you
go into my folder, I breaka you hands, capish?
David W. Fenton
news:1e36u5ls7li96daei...@4ax.com:
Had you described a different situation, I would have given a
different answer. Pardon me for providing an answer for the question
you asked!
Arvin Meyer
"David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
> One of the fabulous things about Access Services with Sharepoint
> 2010 and an Access web app run in the browser is that you give up
> nothing at all -- the result is identical to the same app running
> within Access.
I think that you are being overly optimistic. It's not the same at all. It
is somewhat similar, with a lot more work to build, and more work to
implement. It's also orders of magnitude slower to sync more than a few
records for multiple users with any sized update or insert operation.
> This is huge and ultimately seems to me that it will be very
> popular, even among those who'd never have considered Access in the
> past. But I could be overoptimistic in that.
For Access sized operations (1 to 50 users) I think Terminal Services is the
best solution. Beyond that, it seems to me that an asp/asp.net solution with
a SQL-Server database is more appropriate, since it's likely to need further
scaling anyway.
David W. Fenton
>
> "David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
> news:Xns9D7088D88BF36f9...@74.209.136.93...
>
>> One of the fabulous things about Access Services with Sharepoint
>> 2010 and an Access web app run in the browser is that you give up
>> nothing at all -- the result is identical to the same app running
>> within Access.
>
> I think that you are being overly optimistic. It's not the same at
> all. It is somewhat similar, with a lot more work to build, and
> more work to implement. It's also orders of magnitude slower to
> sync more than a few records for multiple users with any sized
> update or insert operation.
I was speaking only of UI. Performance would, of course, be
different based on the speed of your connection. It's the web versus
a LAN connection, so I think it's rather odd for you to think it
needs to be noted.
>> This is huge and ultimately seems to me that it will be very
>> popular, even among those who'd never have considered Access in
>> the past. But I could be overoptimistic in that.
>
> For Access sized operations (1 to 50 users) I think Terminal
> Services is the best solution. Beyond that, it seems to me that an
> asp/asp.net solution with a SQL-Server database is more
> appropriate, since it's likely to need further scaling anyway.
Terminal Services and converting to a browser-based application does
not in any way address the issue of disconnected users, whereas
Sharepoint does. To me, that's more important than anything else,
because I've been dealing with that issue for 13 years, providing
replicated solutions to clients. Sharepoint makes Jet replication
obsolete for all purposes (WTS made it obsolete for anything but
disconnected users). The ease of use and lack of setup required to
use an Access app with Sharepoint is a big win, while still
providing the disconnected user the ability to work and synch.
PW
wrote:
>
>"PW" <emailad...@ifIremember.com> wrote in message
>news:r136u5hmo53sr3ohe...@4ax.com...
>
>> Thanks very much. I understand now. I just did not understand
>> Arvin's two folder thing. I will check out his article.
>
>If you are using RDP (a remote client) each person must log into their own
>copy of the front-end. SHARING A FRONT-END IS A RECIPE FOR CORRUPTION, on
>any server, even on a terminal server. To avoid that on a terminal server
>you place the copy of the front-end into a folder that ONLY that person will
>access. So, there's a front-end.mdb in the PW folder, and a front-end.mdb in
>the Arvin folder. Arvin logs on ONLY uses the copy of in his folder. If you
>go into my folder, I breaka you hands, capish?
I capish now!!! Grazie!
PW
<XXXu...@dfenton.com.invalid> wrote:
>PW <emailad...@ifIremember.com> wrote in
>news:1e36u5ls7li96daei...@4ax.com:
>
>> On 6 May 2010 02:27:27 GMT, "David W. Fenton"
>><XXXu...@dfenton.com.invalid> wrote:
>
>>>I would agree that some version of Terminal Services is probably
>>>the easiest to implement, but I'm not sure it's the right solution
>>>for really small groups of users (like two).
>>
>> But it might be for our larger clients. Good to know in case they
>> ask.
>
>Had you described a different situation, I would have given a
>different answer. Pardon me for providing an answer for the question
>you asked!
My wife just told me that one client IS using Terminal Services with
our application without any problems! Thanks.
-paulw
PW
>An odbc connection to a hosted MySql or SQL Server database might be
>something to consider.
>Cheap. Easy to set up. Don't have to change anything but the links in
>the frontend .mdb file.
>
So I would not have to change any of the code that access the data
(recordsets, dlookups, etc...)? Of course, as long as the table and
field names are the same.
I just installed MySQL Server 5.1 but can't for the life of me figure
out how to access or build a database! I guess I need something else.