Access db on Server

[BillD]

I have a government client that uses a database I designed in MS-Access
2000. He had the database placed on the government server. The government
server folder is mapped on his office desktop which is setup with microsoft
network. His laptop is also on his private office network. He is able to
access the government network server drive (which holds the database) from
both his office desktop or his laptop. Both the desktop and laptop have
MS-Access loaded. Both the laptop and the desktop can work in the database at
the same time. The system seems a little slow but seems to work good. By the
way, the government server is in one city and the office where the desktop is
located is in another town. It is my understanding that the laptop can access
the database from anywhere he is able to get a wireless connection.
My question is: Is this setup ok or is there a problem if there are
disconnects. Will the database become corrupted if there are disconnects. I
am concerned especially with the laptop since it uses a wireless connection.
I am not familiar with networking except within an office setting. I am under
the impression that the laptop and desktop would be loading parts of the
database program into ram and if there is a disconnect then the database will
become corrupted at some point.
Thanking you in advance for your advice.

BillD

 

[Jerry Whittle]

Access does NOT play well with wireless. It could easily corrupt the
database. IMHO if wireless is a requirement, you need either a true
client/server database or a web-based app.

 

[BillD]

What is IMHO?
If the database was split into a back end with the tables and converted to
SQL Server or Server Express and the front end in MS-Access with the queries,
forms and reports etc., would this solve the problem? Have I stated this
correctly?
Would I put the SQL Server database on the Government server and create a
link to the MS-Access Program on both the laptop and Desktop? This way there
would be minimal system changes and no need to rewrite queries and reports.
Is this correct? I appologize, but I am just learning the setup, so would you
be kind enough to clearly state the detailed proper setup and procedure for
the government server, laptop and desktop.
Thanking you in advance for your assistance.
BillD

 

[Douglas J. Steele]

In My Humble Opinion

 

[Jerry Whittle]

Sorry about the IMHO acronym. Doug explained it.

The big problem is that Access is not client/server. It needs to pull all
the data across the network where it is worked on by the PC. Any changes to
the data must go back across the network to the back end file. If there is an
interuption while writing, corruption could occur.

You could very well use SQL Server for the back end and Access as the front
end. However you would need to modify all the queries and record sources for
the forms and reports with pass-through queries so that all the data
manipulation is done by SQL Server.

 

[BillD]

Thanks for explanation of IMHO.
If the database was split into a back end with the tables and converted to
SQL Server or Server Express and the front end in MS-Access with the queries,
forms and reports etc., would this solve the problem? Have I stated this
correctly?
Would I put the SQL Server database on the Government server and create a
link to the MS-Access Program on both the laptop and Desktop? This way there
would be minimal system changes and no need to rewrite queries and reports.
Is this correct? I appologize, but I am just learning the setup, so would you
be kind enough to clearly state the detailed proper setup and procedure for
the government server, laptop and desktop.
Thanking you in advance for your assistance.
BillD

 

[BillD]

I did not know what it meant because I do not use messaging or chat much.

You could very well use SQL Server for the back end and Access as the front end.

Changing the Back end to SQL Server should be easy as I only have 1 main
table for names and addresses etc., 1 other table to record calls/notes and 8
other tiny tables used only to make selections such as( ex.- one small table
is "VoterPreference" -selections are Lib, PC, NDP etc.).

However you would need to modify all the queries and record sources for
the forms and reports with pass-through queries so that all the data
manipulation is done by SQL Server.

I have about 50 Queries and 50 Reports. What type of work are we talking
about here.

 

[Rick Brandt]

You could very well use SQL Server for the back end and Access as the
front end. However you would need to modify all the queries and
record sources for the forms and reports with pass-through queries so
that all the data manipulation is done by SQL Server.

That is not an accurate statement. I have been building Access front ends to
server back ends for years and the vast majority of my forms use plain old
linked tables and/or local queries based on those linked tables. Pass throughs
and stored procedures are *occassionally* required to improve performance, but
that is the exception rather than the rule.

 

[BillD]

Rick:
That's great news.
What about Queries and Reports?
How complicated is the linking procedure?
Is there any site that has steps to follow in setting up a SQL Server Back
End to an Access Front End?
Also, I am currently using Access 2000, and would like to know if I
should upgrade to XP or 2003 before proceeding?
What is the best and cheapest Server? I understand SQL Server Express is
free, Is it acceptable?
The last important question is whether you have any recommendations as to
linking Outlook Contact Manager and MS-Access?

Since you have walked the walk, I would really value your opinion/comments.

Bill Depow
Fredericton, NB, Canada

 

[Tony Toews [MVP]]

It needs to pull all
the data across the network where it is worked on by the PC.

Just to clarify this though. The entire table does *NOT* come down
the wire. Only those pages containing the indexes and data.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[BillD]

I have reviewed this thread and would appreciate some further direction on
the following questions. Thanks Doug, Jerry, Rick and Tony for your help thus
far.

How complicated is the linking procedure? Tony Toews directed me to -Tony
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm. I was not able to see a detailed
procedure here. Am I missing it.
I really will need a detailed procedure for splitting a database and
linling tables to queries, forms, reports? After splitting the database to a
front end back end, I will want to set the back end up in SQL server so I can
use the database on a network server as a file share or on a web server.
Also, I am currently using Access 2000, and would like to know if I
should upgrade to XP or 2003 before proceeding to setup a front end using
MS-Access and a back end using Sql Server or Server Express or whatever is
recommended.
What is the best and cheapest Server system? I understand SQL Server
Express is
free, Is it acceptable?
Thanks,
Bill Depow