Conflict between Access & SQL
Flint@discussions.microsoft.com Peter Flint
problems on our old server which was loaded with Server 2003. We took a
management decision recently to upgrade our software to Syspro which runs on
SQL. In order to accommodate this we have purchased a new server with Small
Business Sever 2008 and SQL loaded.
We obviously cannot change over to Syspro without first testing the software
and also populating all the relevant fields so there is going to be an
overlap where we still need to be able to continue running the Access
software while testing and implementing the Syspro system. At this stage we
anticipate that the changeover will take place end December.
Our problem is that after over a week of trying all sorts of different
settings Microsoft have acknowledged that Access and SQL will not operate
simultaneously.
At this stage we have taken the Access database off the server and loaded it
onto another PC (originally one of our old servers) and the Access database
is running partially so long as we continually compact and repair the
database and also restrict the number of concurrent users accessing Access as
well as disabling SQL.
I have been astounded at the response from Microsoft and they have been
singularly unhelpful so far.
Does anyone have any idea of how to resolve this?
Armen Stein
What do you mean "will not operate simultaneously"? Error message?
Poor performance? Access and SQL Server are completely different
programs and normally won't interfere with each other.
Is your database split where just the Access back-end is on the server
and each user PC has the front-end application? That's the way it
should be. In that case, the Access *program* doesn't need to be on
the server at all, just the back-end database file. It's a file
server scenario where each front-end application just links to the
database file on the server.
More info, please.
[I'll be offline for most of the day, but hopefully others can help
out too.]
Armen Stein
Microsoft Access MVP
www.JStreetTech.com
Mary Chipman [MSFT]
if you follow up with him directly. Who did you speak to at Microsoft,
and what exactly did they say? It's simply not true that "Access and
SQL will not operate simultaneously". Millions of Access-SQL
applications are in use every day.
--Mary
On Fri, 25 Sep 2009 06:36:02 -0700, Peter Flint <Peter
Fl...@discussions.microsoft.com> wrote:
Albert D. Kallal
news:F19C28DA-A606-45E9...@microsoft.com...
>
> Our problem is that after over a week of trying all sorts of different
> settings Microsoft have acknowledged that Access and SQL will not operate
> simultaneously.
>
Do feel free to expand on the above. It not quite clear about the
simultaneously issue you talking about.
in general, in a typical small business environment, ms-access does not even
need to be installed on the server. (only a shared folder).
so, ms-access is typical not installed on the server, and nor is it
necessary.
> At this stage we have taken the Access database off the server and loaded
> it
> onto another PC (originally one of our old servers) and the Access
> database
> is running partially so long as we continually compact and repair the
> database and also restrict the number of concurrent users accessing Access
> as
> well as disabling SQL.
The above sounds like you gone back to your original setup for ms-access.
However, why disable sql when it on a different machine and not being used
for this? Some detail, or some big part of this picture is missing here.
in summary:
An access file sitting on a shared folder that is on the SAME computer as
the sql server should not be a source of problems here. this type of setup
should be workable, and it not a general known source of problems.
Do feel free to expand on you above issues. Most of here are listening and
ARE able to help.
The 1st issue here is that you moved the access database to another computer
now. I assume this is what you had before without problems and before sql
server? I think it important to note that just placing an access file on the
same machine as the sql server is NOT going to be a source of problems. In
other words now that you moved the file off of the sql server, the problems
don't seem to have changed...have they? (in other words, you problems NEVER
was the issue of placing the access mdb file on the server running sql
server).
It been my experience that a access shared file should not interfere with
the operation of your sql server applications. Since you are having problems
and this is NOT normal, then I think some additional details are missing
here.
>
> Does anyone have any idea of how to resolve this?
Sure, lets start to break this down into bite size steps.
The 1st issue is when you say you are placing the ms-access database on the
sql server, what do you mean by this? (placing a word file, or excel or in
this an access mdb file on the server is not even installing any software on
the server). So, this is just a plane Jane file..correct? this file should
not effect sql server operation at all.
Also, are users running some application on their computers that uses this
data file? (and, what kind of network are you using here?).
And, are you running an ms-access application here? Or is this some other
application (software) that is pulling out data of this access file that is
being placed on the server?
So, start by cleaning up some of the above details...we will do our best to
hang in here and get you going, or help move you forward...
(I think there is some confusing here, since some details are being left
out. it not clear if your running an access application, or just using an
access data file).
If this is an access application, then you need to place the application
part on each computer. (this means you have to split the application). This
also a great step forward in preparing this application's data to be
migrated to sql server anyway. So, what is the setup on each client
computer, and how are they reading/using this data? Does each user open up
the access file shared on the folder in the server?
Also, to encourage you...what you mentioned so far seems like these problems
can WELL be resolved with a bit more information, and I don't think you need
any new systems or software here...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOO...@msn.com