Upsizing Existing DB

[S Jackson]

Now that I've developed this "nifty" and "way-cool" db for use here in our
office over a LAN, the "powers-that-be" want to know if we can deploy my DB
over our WAN so it can be remotely accessed by other office locations. I am
a lowly legal assistant and don't know didley about networking other than
fiddling with my own home LAN, but for some reason, they think I have all
the answers (I guess I have them fooled). But, here is what I understand so
far:

1. Our office WAN is over a T1 line using Novell.
2. There will be probably 50-60 users that will need permission to access
the DB, but the reality would be that at the very most only about 10-12
people would be accessing the DB at one time.
3. I have no idea how big the DB will get, but can assume that we need to
plan on something huge as if it is deployed state-wide, it will grow big
over the years.
4. My existing DB is split - fe and be.
5. I understand that I need to convert my file-share DB into a SQL server
(client-based) DB.

Question: how do I do this??? Do I simply use the upsizing wizard? If so,
where do I start (step-by-step here, please). Here is the perfect world I
want to live in:

- Have a backend that sits on a server containing all the data (tables);
- Have a frontend that is NOT web-based - I want to use Access and all the
forms, reports, etc., I've created because I want the users to have control
over this. Plus, I don't know jack about data access pages -
projects -whatever you call them.

I also understand from the little bit I have read that I will need to
manually convert code to use ADO recordset (shaking in my boots).

Sorry if this long and confusing and inappropriate for this group, but I
just don't know where to start and you folks are always a superb source of
help!
TIA
S. Jackson

 

[Guest]

Shelley,

Access over a WAN is a big no-no. The fastest solution is
to setup terminal server(s), which causes all processing
to be done on the local LAN, while appearing to be WAN
based.

Alternatively, convert the Access .mdb FE to an .ADP FE
pointing to a SQL Server back-end. Based on your
description of your skills, this is probably not going to
be easy.

I will not be checking this thread often, as I am in a
training class...but if you post back, others can answer
additional questions as well as I can.

Good luck,

Kevin3NF
www.3nf-inc.com

 

[mcnewsxp]

the quickest way to do this is to use citrix winframe or metaframe. users
can access your app via dialup or web.
you can export all of your tables to sql db and link them to your front-end
app witout having to change anything.

NOTE - make sure all tables have primary keys and that all bit fields allow
nulls and are defaulted to 0 before you link.

not saying this is the best solution or cheapest, but you won't have to
learn too much.

 

[S Jackson]

Thanks for your response. So, here is my understanding of what you are
telling me:

1. Set up a terminal server: from the little I have read, however, this
would NOT allow me to deploy a FE on individual user's computers that they
would be able to freely modify, correct? That this is a remote control
technology that would manipulate an existing DB on a server?

OR,

2. Convert the Acces .mdb FE to an .ADP FE pointing to a SQL Server
back-end: YIKES! You are right - not experienced of trained enough to
understand how to do this. First, how do I convert the FE and where to I
get the SQL be?--- I know I can't do this myself and I am not expecting you
to actually answer this, but can you point in the direction of where I can
get more information about the process so that I can provide it to the
"powers-that-be"? Second, will this give me a FE that will be deployed on
all the users' computers that I can maintain, distribute and that they can
modify if they so choose?

THANK YOU: What you response does do for me is provide a half-way
intelligent response to the "powers-that-be." It sounds like we will need
to implement Option No. 2. Hopefully, the "powers-that-be" (who live in
what we underlings refer to as the "Reality Distortion Dome") will realize
that we are going to need MIS support and that MIS will understand what I
want.

Thanks so much for your help.

 

[Kevin3NF]

Option 1:
In a TS environment, each user gets a profile, just like
any other Windows system. Each profile has its own copy
of the Access FE, so you are deploying it and they can
manipulate it if you allow them to.

Option 2:
Way more expensive (development time), and I'm not really
sure how well an ADP works over a WAN. never tested it.

If your "powers that be" RDD guys ;-) want to chat with me
(or if you do), that can be arranged. I have set up the
TS solution, and am 95% through an mdb to ADP conversion,
but am not deploying that over a WAN.

See my site for contact information if needed.

Kevin3NF
www.3nf-inc.com

 

[S Jackson]

It is my understanding from further research is that I have 3 options:
- file-server solution
- client/server solution
- DB Replication.

Does using a TS environment fall under the file-server solution? If so, is
this a stable and secure way to implement a shared db? Can I backup up the
data? Is the data vulnerable to corruption if it is being accessed by
various users in different locations?

Thanks
S. Jackson
P.S. My "powers-that-be" is a dept. of the local state government -
unfortunately they do not contract with private contractors (except they are
currently implementing the biggest gov. consolidation in the history of the
US and are "out-sourcing" a number of things - such as HR), but have their
own MIS dept that for some odd reason charges its employer $$$/hr for any
project sent their way - which means we have to have money in our budget -
which we don't - as always . . . heavy sigh. I wished I could recommend a
professional firm such as yours to "do this."

 

[S Jackson]

One more thing:

Option 2 - you said was expensive b/c of development time - so that means
Access little upsizing wizard is not going to the job here then?

S. Jackson

 

[Guest]

Access MDB to ADP will not convert everything, depending
of course on how complicated your application is. I don't
even use the upsizing wizard...misses too much.

 

[Guest]

see below...

-----Original Message-----
It is my understanding from further research is that I have 3 options:
- file-server solution (MDB/SQL Server via Terminal Server)
- client/server solution (ADP/SQL Server)
- DB Replication.(No...don't go here...)

Does using a TS environment fall under the file-server

solution?

Yes, pretty much. All processing is done over the local
LAN, which is the issue we started with.

If so, is

this a stable and secure way to implement a shared db?

Works very well in the environments I have set up.

Can I backup up the data?

Yes, of course.

Is the data vulnerable to corruption if it is being accessed by
various users in different locations?

If the backend is SQL Server, corruption is far less
likely, but all db systems can be corrupted. Put a proper
backup and recovery plan in place.

Thanks
S. Jackson

You're welcome....

Also...I was offering up a "sanity-check" type phone
call...not a paid "let me do it for you" type thing.

 

[david epsom dot com dot au]

all bit fields DO NOT allow nulls

(david)

 

[david epsom dot com dot au]

There is a wizard to upsize to sql server. The wizard can create an ADP.
It is simple to try, if you have SQL Server or SS Express installed.
It works for some people (if their database is simple enough).
If you have SQL Server, go ahead and give it a try.

Whatever you decide, YOU WILL NEED SOME ONE WHO KNOWS SOMETHING ABOUT
SQL SERVER or TERMINAL SERVER/CITRIX. If that someone will be you,
plan to spend some time on it. Do you know how to truncate the logs
on a SQL Server? Do you know how to set up local printers on TS?
Some one will have to do this.

I think either way can be made to work. I think that if you have
both options, you can try both ways and see which you like. If you
can't try both ways, I recommend that you go with the way that you
CAN try, that is, with the way where you get most support and least
resistance from your IT supplier.

(david)