Linking Access to Microsoft SQL

[Guest]

Currently we have a web site which as an SQL database. To update this
database we have to access it via a web interface and we are having a lot of
problems(due to errors in the programming of the web site I think).

I am proposing that this database be minimised to include only the
information necessary for the web site, and that an access database should be
used in house for the admin staff to maintain our membership records.

It would be necessary for the SQL database to be updated using information
from the access database on a frequent basis.

Is such a proposition sound and feasible? and could access cope with a high
number of records (our membership base is close to 1,000)?.

Thanks for any help.

 

[Rick Brandt]

Currently we have a web site which as an SQL database. To update this
database we have to access it via a web interface and we are having a lot of
problems(due to errors in the programming of the web site I think).

I am proposing that this database be minimised to include only the
information necessary for the web site, and that an access database should be
used in house for the admin staff to maintain our membership records.

It would be necessary for the SQL database to be updated using information
from the access database on a frequent basis.

Is such a proposition sound and feasible? and could access cope with a high
number of records (our membership base is close to 1,000)?.

Is the SQL Server on your LAN or off-site? If the former you can easily connect
to the tables with an Access app and do updates in real time rather than making
changes in Access and "applying" them at some interval.

BTW 1000 is a wee, tiny, miniscule number of records. You need to add several
more zeros to that before you would have problems with the data even if it were
stored in an Access/Jet file and if you can work directly with the SQL tables
then the number of records is of no concern at all to Access.

 

[Alex]

Hi Nicola,

Feels like a backward step going from a SQL database into an access
database, my solution to the problem would be

1. write an access front-end for the in-house staff to
insert/update/delete information.
2. Link this access database front-end to your SQL server.

As Nick has said you are talking such small numbers that this is not
the problem with your system. The idea of disconnecting some of your
data then importing/sync'ing data will cause you more trouble in the
long run.

Web pages are generally harder to write than access type forms, access
type forms have a much more rich environment when it comes to events on
screen calculations etc. This is not to say is cannot be written in
HTML/asp/asp.net because in most cases it can, it just takes longer.

You are currently running the daddy of MS databases SQL, don't go back
to access it is a backward step.

I have a question for you, is this database stored locally or on a
webserver?

Regards

Alex

 

[Guest]

Hi Rick and Alex,

Thank you so much for your replies.

The database is stored on a web server, and it already has an inbuilt
facility for adding and maintaining database records as well as maintaining
the web site itself. I consider the design to be quite sophisticated,
although the reporting facility is lacking.

However the staff and membership have no confidence in the database because
there is a problem in the maintenance module, which means that data entered
is not reflected in the database. So much so the database is bypassed and the
office runs on a myriad of excel spreadsheets.

There is a real push to ditch the whole site and buy an off the shelf
membership management system. I aggree with you that it does feel like a
backwards step to move from SQL to access, but my motivation was database
visibility. Adding another layer of complexity with a second database may not
be so good either. I am coming to the conclusion that the real answer is to
fix the database maintenance problem (not that our tech support have had much
success) and to become more familiar with SQL server tools in order to create
new queries.

Thanks again,
Nicola

 

[Alex]

Hi Nicola,

Let me understand this a bit better, your database is not local to your
staff and your only access at the moment is via the web?

One of the problems of systems that don't work or fit the bill is
people will always create solutions to run along side then making the
original system disfunctional. It is an uphill battle to get people
back on board, I spend my life inheriting systems from other developers
and having to convince people to have confidence in the system again.

The ramifications of getting an off the shelf package could be things
like 'it can do 90%' of your what your current system can do and the
other 10% could be a real problem.

If you are willing, the way forward here is to understand the database
schema (layout), get your staff behind the project by making them feel
like they own it, allow them to have some control of how what happens
from here, it is funny how people feel when they are in control and
feel like they own a bit of the system, this can make these things move
forward.

If you answer yes to the top question then the main problem you have is
being near to your data, using access across the internet is not a good
idea, the solution could be if your website is hosted on an MS
webserver like windows 2000 windows 2003 is using terminal services to
get access to the data locally.

If you need anymore help, just post here.

Regards

Alex