OT?: Application Architecture Question

[CJM]

I have a potential client who has a Access-based DB which they use for
recording orders, customer details etc...

They want to provide some of this information online, eg. customer account
details and stock-levels etc, whilst retaining their existing functionality.
They also want to retain the Access front-end but want to migrate the DB to
SQL Server.

AFAIK, they dont have local, dedicated web servers, but use a hosting
package of some description.

I'm trying to think of different ways to tackle this, but no 'silver-bullet'
solution.

1) Host their own web server. Access (via and Access Data Project or ODBC)
could communicate with the same SQL Server as the ASP application. Not sure
if the have the will/money/technical capacity to host their own servers, but
I suspect not.

2) Scrap Access and redevelop all there back-end & UI to use ASP/SQL Server
online - requires a lot of re-work, and means that the whole operation is
vulnerable if the site goes down.

3) Keep existing systems, and periodically update online SQL Server DB with
the needed information. Not sure yet how much data we are talking about
here, and not sure how we could synchronise the online-offline DBs

Can anybody think of any alternatives? Any comment on these?

It seems this is a relatively small org trying to think bigger, but I fear
I'm going to dash their hopes. Without making the project a lot more
complicated and expensive, I'm not sure how we can do what they want.

I'd also be appreciative for comments on the synchronisation idea. How could
we go about synchronising and Access & SQL?

Does anybody have any experience in this whole area that they can share?

Thanks in advance

Chris

 

[Manohar Kamath [MVP]]

Given the situation, I would go with #3. If they were technically savvy, I
would go with #1.

A fourth option would be a combination of 1 and 3. Export data on a regular
basis to an external web server. But then, create a local web service that
gives information on live data such as inventory count. By doing this, you
could upgrade the local DB to SQL server, and still be able to maintain it
locally.

As you mentioned, there is no "silver bullet" for any problem -- most happen
to be the best compromise.

 

[Patty O'Dors]

You need to ask them WHY they want to upgrade to SQL server...
and then build your answer based on that.

 

[CJM]

I think the why is easy.... Access is grinding to a halt.

170,000 customer records plsu 9 years of Sales data... combine that with
multi-user access and you have a quagmire!

 

[Zeke]

I have a potential client who has a Access-based DB which they use for
recording orders, customer details etc...

They want to provide some of this information online, eg. customer account
details and stock-levels etc, whilst retaining their existing functionality.
They also want to retain the Access front-end but want to migrate the DB to
SQL Server.

AFAIK, they dont have local, dedicated web servers, but use a hosting
package of some description.

I'm trying to think of different ways to tackle this, but no 'silver-bullet'
solution.

1) Host their own web server. Access (via and Access Data Project or ODBC)
could communicate with the same SQL Server as the ASP application. Not sure
if the have the will/money/technical capacity to host their own servers, but
I suspect not.

1. I agree this is the best and easiest solution for combining the
Access DB with the SQL info. Expenses are a web server, a db server,
bandwidth, and if necessary a few hours with a security specialist.

The current expense really depends on if the company has spare
hardware and bandwith that will handle the average traffic for their
site. They may have a box or two lying around somewhere, of course
maybe not.

You may run into problems if they have a network guy, and he doesn't
know web or DB servers of course.

2) Scrap Access and redevelop all there back-end & UI to use ASP/SQL Server
online - requires a lot of re-work, and means that the whole operation is
vulnerable if the site goes down.

2. This is harder, I'm used to developing most stuff in ASP to begin
with. I have seen a fully-funcitoning CRM tool that uses an Access
DB, but has the data in it linked to SQL tables.

If the DB architecture isn't going to change much this may be your
silver bullet. The only problem anyone notcied with the setup is that
it took a little bit of fiddling around (resetting the link sometimes)
for architectural changes to propagate to Access.

I still know the guy who's using this system, I may be able to get you
an email of someone experience with this setup.

3) Keep existing systems, and periodically update online SQL Server DB with
the needed information. Not sure yet how much data we are talking about
here, and not sure how we could synchronise the online-offline DBs

3. If as you mention below it is a really small group then hand
updating the online DB may be a good option. It depends what 6 hour
old data is going to do to the business.

Very small businesses are either small enough that there are people
using the data late because the main couple of people at the company
are at the office all the time, or they are just big enough that the
main people that are going to be using the data are done at 4:45 pm.

If the latter is the case then you're set. If the current online SQL
server gives you permissions to use DTS, then set up a DTS package on
a SQL server at their location to pull the data out of Access at
midnight. Then setup another DTS package to ship the data to the
online SQL server at 1am.

Otherwise your job is to hand pick the data every 'business' night up
to the online server, in which case you need some kind of stipend
right?

Can anybody think of any alternatives? Any comment on these?

It seems this is a relatively small org trying to think bigger, but I fear
I'm going to dash their hopes. Without making the project a lot more
complicated and expensive, I'm not sure how we can do what they want.

I'd also be appreciative for comments on the synchronisation idea. How could
we go about synchronising and Access & SQL?

Does anybody have any experience in this whole area that they can share?

Thanks in advance

Chris

Hope that's what you're looking for.

Zeke