"Read Only" problem from Access 2003 to SQL Server

I am unable to add/update/delete data in the SQL Server database from Access
2003. I can connect just fine using either an ODBC link or via an ADO
connection in VBA. However, any time I try to change any SQL Server data (or
run a stored procedure) it either "times out" or gives me a message to the
effect that "...another user is attempting to change the data at the same
time...". Since I am the only one on the network, I know this is not the
real problem. I have SQL Profiler running in order to see what may be
happening when I try to run a stored procedure. The SQL Server doesn't get
the request at all. Access seems to be blocking any calls to stored
procedures and blocking any attempt to change data.
I can change data with no problem from within Enterprise Manager. The SQL
Server User (in the ADO connection) has db owner rights and rights to all
objects being accessed.
Here is the configuration: I am running Win 2K Pro with SQL Server 2000 on
PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 database
application on PC-2 that needs to modify the SQL data on PC-1. I have tried
Windows authentication and SQL Server Authentication (modifying the SQL
Server settings and ADO connection string settings as needed). Both ways of
connecting give the same result - read only.
When I installed Access 2003 I saw a message relating to security and Access
not allowing potentially destructive code to run. I took no action on the
message although I did remember seeing something about a necessary update to
2003 for the issue (I figured I'd revisit it later). This issue may be
related to the problem, but I don't know. There are no outstanding updates
available for Access 2003 from MS. Now I can't figure out what it was
talking about. I may uninstall the software and reinstall to see if I can
catch it again.
I have tried everything I can think of, including soliciting help from
peers, searching the knowledge databases and reading many posts. No luck.
It appears as though no one has had this particular issue come up. I tried
to ask Microsoft (using my one free question) but my (valid) Product ID was
rejected by their system.
Anyone out there have any experience with this one? Three days stuck on one
issue is getting old.
Thanks and regards,
--
Ken Wells
President
Smart Computing Solutions, LLCAll it took was a post. I have, for all intents and purposes, solved this
problem myself.
I broke down and rewrote the code (inherited from another developer), basing
it on code that I had done in the past. I can now execute a stored procedure
and update or add records in the SQL database from Access 2003 using an ADO
connection and SQL Server Authentication.
I still cannot directly modify ODBC-linked SQL table data from the database
window, but that is a minor consideration at this point.
"SmartComputing" wrote:
> I am unable to add/update/delete data in the SQL Server database from Access
> 2003. I can connect just fine using either an ODBC link or via an ADO
> connection in VBA. However, any time I try to change any SQL Server data (or
> run a stored procedure) it either "times out" or gives me a message to the
> effect that "...another user is attempting to change the data at the same
> time...". Since I am the only one on the network, I know this is not the
> real problem. I have SQL Profiler running in order to see what may be
> happening when I try to run a stored procedure. The SQL Server doesn't get
> the request at all. Access seems to be blocking any calls to stored
> procedures and blocking any attempt to change data.
> I can change data with no problem from within Enterprise Manager. The SQL
> Server User (in the ADO connection) has db owner rights and rights to all
> objects being accessed.
> Here is the configuration: I am running Win 2K Pro with SQL Server 2000 on
> PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 database
> application on PC-2 that needs to modify the SQL data on PC-1. I have tried
> Windows authentication and SQL Server Authentication (modifying the SQL
> Server settings and ADO connection string settings as needed). Both ways of
> connecting give the same result - read only.
> When I installed Access 2003 I saw a message relating to security and Access
> not allowing potentially destructive code to run. I took no action on the
> message although I did remember seeing something about a necessary update to
> 2003 for the issue (I figured I'd revisit it later). This issue may be
> related to the problem, but I don't know. There are no outstanding updates
> available for Access 2003 from MS. Now I can't figure out what it was
> talking about. I may uninstall the software and reinstall to see if I can
> catch it again.
> I have tried everything I can think of, including soliciting help from
> peers, searching the knowledge databases and reading many posts. No luck.
> It appears as though no one has had this particular issue come up. I tried
> to ask Microsoft (using my one free question) but my (valid) Product ID was
> rejected by their system.
> Anyone out there have any experience with this one? Three days stuck on one
> issue is getting old.
> Thanks and regards,
> --
> Ken Wells
> President
> Smart Computing Solutions, LLC|||"SmartComputing" <kennethwells@.NO_SPAM_yahoo.com> wrote in message
news:9F4BA364-B69B-4026-AD67-CF024DE7FEDD@.microsoft.com...
> All it took was a post. I have, for all intents and purposes, solved this
> problem myself.
> I broke down and rewrote the code (inherited from another developer),
> basing
> it on code that I had done in the past. I can now execute a stored
> procedure
> and update or add records in the SQL database from Access 2003 using an
> ADO
> connection and SQL Server Authentication.
> I still cannot directly modify ODBC-linked SQL table data from the
> database
> window, but that is a minor consideration at this point.
I reckon it's the connection parameters are most likely the problem.
Go into the linked table manager.
If you hover over the connected tables you should see a connection string in
there.
Whilst you're there, refresh the connections and check that doesn't help.
Check out your DSN or connection strings.
Oh...
Access wants unique keys to allow updates.
Looking at the table definition in access, you see a primary key on the
tables?
There's also joins can make a query not updateable but if nothing works this
seems unlikely.
--
Regards,
Andy O'Neill

"Provider not found" error

I have an application that works just fine on several computers.
{Delphi6 with ADO Components }
We have a MSSQL2K DB with MDAC 2.81.1117 on EVERY machine.
However, on one of them, when I try to run the program, I get a
"Provider not found. It may not be properly installed" error,
then an "Error in the DLL" message box.
We can access database through Enterprise manager AND I can create an
ODBC DSN to the database in question on the problem machine just fine.
Let me explain here what we've attempted.
1. Reinstalled SQL Server2K
2. Reinstalled XP SP2
3. Installed Delphi fully
4. Tried MDAC 2.8 install, won't because of XP SP2,
says already installed
5. tried using DSN file .vs connectionstring
6. tried clearing the provider from connectionstring
7. tried creating ODBC DSN and connecting that way.
All of these result in same "Provider not found" error. When I opened
the source on this machine in Delphi and tried to build the
connectionstring there, I got an error stating that the provider was not
installed at all.
I can verify that sqloledb.dll IS installed in the
C:\Program Files\Common Files\System\Ole DB directory and registered
I can verify that mdasql.dll IS installed in the
C:\Program Files\Common Files\System\Ole DB directory
I can verify that mdatl3.dll IS installed in the
C:\Program Files\Common Files\System\Ole DB directory
I can verify that registry entires are correct for SQLOLEDB provider.
I can also verify that the directory
C:\Program Files\Common Files\System\Ole DB is in the search path.
This is a critical time accounting appplication for our employees
that works on dozens of other machines and for it to not work on one
certain machine is very puzzling. We can not understand what is so
"special" about this one.
Any ideas?
The Cleaning Wonder Boy wrote:
> *I have an application that works just fine on several computers.
> {Delphi6 with ADO Components }
> We have a MSSQL2K DB with MDAC 2.81.1117 on EVERY machine.
> However, on one of them, when I try to run the program, I get a
> "Provider not found. It may not be properly installed" error,
> then an "Error in the DLL" message box.
> We can access database through Enterprise manager AND I can create
> an
> ODBC DSN to the database in question on the problem machine just
> fine.
> Let me explain here what we've attempted.
> 1. Reinstalled SQL Server2K
> 2. Reinstalled XP SP2
> 3. Installed Delphi fully
> 4. Tried MDAC 2.8 install, won't because of XP SP2,
> says already installed
> 5. tried using DSN file .vs connectionstring
> 6. tried clearing the provider from connectionstring
> 7. tried creating ODBC DSN and connecting that way.
> All of these result in same "Provider not found" error. When I
> opened
> the source on this machine in Delphi and tried to build the
> connectionstring there, I got an error stating that the provider was
> not
> installed at all.
> I can verify that sqloledb.dll IS installed in the
> C:\Program Files\Common Files\System\Ole DB directory and registered
> I can verify that mdasql.dll IS installed in the
> C:\Program Files\Common Files\System\Ole DB directory
> I can verify that mdatl3.dll IS installed in the
> C:\Program Files\Common Files\System\Ole DB directory
> I can verify that registry entires are correct for SQLOLEDB
> provider.
> I can also verify that the directory
> C:\Program Files\Common Files\System\Ole DB is in the search path.
> This is a critical time accounting appplication for our employees
> that works on dozens of other machines and for it to not work on one
> certain machine is very puzzling. We can not understand what is so
> "special" about this one.
> Any ideas? *
Hi
I too have faced the same problem on one of my clients machine
i have treid all that you have described above but nothing seems to
work.
But the solution is simple
You first need to delete the folder
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\Curr entVersion\Setup\ExceptionComponents
in the Registry and then try re-installing MDAC2.6SP2
Reboot and the problem is gone.
It has worked for me on Win 2000 OS, i hope this works for you
Thanks
Ganta.
Ganta
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message974581.html

"Provider not found" error

I have an application that works just fine on several computers.
{Delphi6 with ADO Components }
We have a MSSQL2K DB with MDAC 2.81.1117 on EVERY machine.
However, on one of them, when I try to run the program, I get a
"Provider not found. It may not be properly installed" error,
then an "Error in the DLL" message box.
We can access database through Enterprise manager AND I can create an
ODBC DSN to the database in question on the problem machine just fine.
Let me explain here what we've attempted.
1. Reinstalled SQL Server2K
2. Reinstalled XP SP2
3. Installed Delphi fully
4. Tried MDAC 2.8 install, won't because of XP SP2,
says already installed
5. tried using DSN file .vs connectionstring
6. tried clearing the provider from connectionstring
7. tried creating ODBC DSN and connecting that way.
All of these result in same "Provider not found" error. When I opened
the source on this machine in Delphi and tried to build the
connectionstring there, I got an error stating that the provider was not
installed at all.
I can verify that sqloledb.dll IS installed in the
C:\Program Files\Common Files\System\Ole DB directory and registered
I can verify that mdasql.dll IS installed in the
C:\Program Files\Common Files\System\Ole DB directory
I can verify that mdatl3.dll IS installed in the
C:\Program Files\Common Files\System\Ole DB directory
I can verify that registry entires are correct for SQLOLEDB provider.
I can also verify that the directory
C:\Program Files\Common Files\System\Ole DB is in the search path.
This is a critical time accounting appplication for our employees
that works on dozens of other machines and for it to not work on one
certain machine is very puzzling. We can not understand what is so
"special" about this one.
Any ideas'

quote:

---

Originally posted by The Cleaning Wonder Boy
I have an application that works just fine on several computers.
{Delphi6 with ADO Components }
We have a MSSQL2K DB with MDAC 2.81.1117 on EVERY machine.
However, on one of them, when I try to run the program, I get a
"Provider not found. It may not be properly installed" error,
then an "Error in the DLL" message box.
We can access database through Enterprise manager AND I can create an
ODBC DSN to the database in question on the problem machine just fine.
Let me explain here what we've attempted.
1. Reinstalled SQL Server2K
2. Reinstalled XP SP2
3. Installed Delphi fully
4. Tried MDAC 2.8 install, won't because of XP SP2,
says already installed
5. tried using DSN file .vs connectionstring
6. tried clearing the provider from connectionstring
7. tried creating ODBC DSN and connecting that way.
All of these result in same "Provider not found" error. When I opened
the source on this machine in Delphi and tried to build the
connectionstring there, I got an error stating that the provider was not
installed at all.
I can verify that sqloledb.dll IS installed in the
C:\Program Files\Common Files\System\Ole DB directory and registered
I can verify that mdasql.dll IS installed in the
C:\Program Files\Common Files\System\Ole DB directory
I can verify that mdatl3.dll IS installed in the
C:\Program Files\Common Files\System\Ole DB directory
I can verify that registry entires are correct for SQLOLEDB provider.
I can also verify that the directory
C:\Program Files\Common Files\System\Ole DB is in the search path.
This is a critical time accounting appplication for our employees
that works on dozens of other machines and for it to not work on one
certain machine is very puzzling. We can not understand what is so
"special" about this one.
Any ideas'

---

Hi
I too have faced the same problem on one of my clients machine
i have treid all that you have described above but nothing seems to work.
But the solution is simple
You first need to delete the folder HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\W
indows\CurrentVersion\Setup\ExceptionCom
ponents
in the Registry and then try re-installing MDAC2.6SP2
Reboot and the problem is gone.
It has worked for me on Win 2000 OS, i hope this works for you
Thanks
Ganta.

"Prepare" in SQL Server through ODBC/ADO

Hi,
When using an interface like ADO or ODBC, there is a call named
"Prepare" which supposedly sent the statement to the server which in
turn should parse it, save the query plan and return a handle to the
client so subquent calls just refer to this handle/id and get faster
execution in batches... is this correct?
Well, my question is, when this Prepare or Parse is done, is there any
physical check being performed by SQL Server ? I mean, does it check
the column names, table existance, etc, or does it just check pure
syntax ?According to the ODBC 3.0 SDK, a SQLPrepare call to an ODBC driver sends the
statement to the data source for preparation and stores the returned access
plan identifier for later execution; or returns any errors including
SQLSTATE 42000 (Syntax error or access violation) and SQLSTATE 42S02 (Base
table or view not found). It also notes that some drivers do not return
errors after SQLPrepare is called, but rather when the statement is executed
later. So "SQLPrepare might appear to have succeeded when in fact it has
failed."
It would seem that you can't rely on a SQLPrepare call to return errors,
since not all drivers support that functionality via SQLPrepare.
Thanks,
Mike C.
<craigkenisston@.hotmail.com> wrote in message
news:1109726936.969442.224550@.o13g2000cwo.googlegroups.com...
> Hi,
> When using an interface like ADO or ODBC, there is a call named
> "Prepare" which supposedly sent the statement to the server which in
> turn should parse it, save the query plan and return a handle to the
> client so subquent calls just refer to this handle/id and get faster
> execution in batches... is this correct?
> Well, my question is, when this Prepare or Parse is done, is there any
> physical check being performed by SQL Server ? I mean, does it check
> the column names, table existance, etc, or does it just check pure
> syntax ?
>|||Thank you !!

"Cannot start more transactions on this session" - SQL 2005 via ADO

Hi all,
I've just installed a new server, and migrated across our SQL 2000
database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
Premium).
I migrated the databse via a backup/restore.
We have an ASP application which connects to the database from our
intranet. When we issue a "connection.begintrans" we get a hard error:
"cannot start more transactions on this session"
We *know* this is not a nested transaction as we only have one
instance of begintrans in our code, and it's only being called once.
At least, it's not a nesting that WE have introduced.
If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
do not experience the error. But if we set the "SQL Compatibility"
option to "SQL Server 2005 (90)" we do experience the error.
Can anyone suggest what has changed (or what needs to be changed) to
resolve this? Our connection string to the database is:
pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
"User ID=" & pUser & _
";Password=" & pPassword & _
";Database=" & pDatabase & _
";Server=" & pServer
Many thanks in advance,
Jim> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
I'm not aware of anything related to the database compatibility level that
would cause these symptoms. I haven't been able to repro this error
(VBScript below) so it may be related to the specifics of your data access
within the client transaction. You might try running a Profiler trace to
see if you can spot differences based on the compatibility level. If that
doesn't help, try posting code that can be run to reproduce the issue.
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial
Catalog=Test;Integrated Security=SSPI"
conn.BeginTrans
conn.Execute "INSERT INTO dbo.MyTable VALUES(1) SELECT 1"
'conn.BeginTrans 'causes error if comment removed
conn.Execute "INSERT INTO dbo.MyTable VALUES(1)"
conn.CommitTrans
conn.Close
MsgBox "Done"
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim" <jim@.nospam.com> wrote in message
news:l9h2539dc08v081npkeu9ot7d5fo4e97jh@.4ax.com...
> Hi all,
> I've just installed a new server, and migrated across our SQL 2000
> database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
> Premium).
> I migrated the databse via a backup/restore.
> We have an ASP application which connects to the database from our
> intranet. When we issue a "connection.begintrans" we get a hard error:
> "cannot start more transactions on this session"
> We *know* this is not a nested transaction as we only have one
> instance of begintrans in our code, and it's only being called once.
> At least, it's not a nesting that WE have introduced.
> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
> Can anyone suggest what has changed (or what needs to be changed) to
> resolve this? Our connection string to the database is:
> pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
> "User ID=" & pUser & _
> ";Password=" & pPassword & _
> ";Database=" & pDatabase & _
> ";Server=" & pServer
>
> Many thanks in advance,
>
> Jim

"Cannot start more transactions on this session" - SQL 2005 via ADO

Hi all,
I've just installed a new server, and migrated across our SQL 2000
database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
Premium).
I migrated the databse via a backup/restore.
We have an ASP application which connects to the database from our
intranet. When we issue a "connection.begintrans" we get a hard error:
"cannot start more transactions on this session"
We *know* this is not a nested transaction as we only have one
instance of begintrans in our code, and it's only being called once.
At least, it's not a nesting that WE have introduced.
If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
do not experience the error. But if we set the "SQL Compatibility"
option to "SQL Server 2005 (90)" we do experience the error.
Can anyone suggest what has changed (or what needs to be changed) to
resolve this? Our connection string to the database is:
pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
"User ID=" & pUser & _
";Password=" & pPassword & _
";Database=" & pDatabase & _
";Server=" & pServer
Many thanks in advance,
Jim> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
I'm not aware of anything related to the database compatibility level that
would cause these symptoms. I haven't been able to repro this error
(VBScript below) so it may be related to the specifics of your data access
within the client transaction. You might try running a Profiler trace to
see if you can spot differences based on the compatibility level. If that
doesn't help, try posting code that can be run to reproduce the issue.
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial
Catalog=Test;Integrated Security=SSPI"
conn.BeginTrans
conn.Execute "INSERT INTO dbo.MyTable VALUES(1) SELECT 1"
'conn.BeginTrans 'causes error if comment removed
conn.Execute "INSERT INTO dbo.MyTable VALUES(1)"
conn.CommitTrans
conn.Close
MsgBox "Done"
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim" <jim@.nospam.com> wrote in message
news:l9h2539dc08v081npkeu9ot7d5fo4e97jh@.
4ax.com...
> Hi all,
> I've just installed a new server, and migrated across our SQL 2000
> database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
> Premium).
> I migrated the databse via a backup/restore.
> We have an ASP application which connects to the database from our
> intranet. When we issue a "connection.begintrans" we get a hard error:
> "cannot start more transactions on this session"
> We *know* this is not a nested transaction as we only have one
> instance of begintrans in our code, and it's only being called once.
> At least, it's not a nesting that WE have introduced.
> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
> Can anyone suggest what has changed (or what needs to be changed) to
> resolve this? Our connection string to the database is:
> pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
> "User ID=" & pUser & _
> ";Password=" & pPassword & _
> ";Database=" & pDatabase & _
> ";Server=" & pServer
>
> Many thanks in advance,
>
> Jim

"Cannot start more transactions on this session" - SQL 2005 via ADO

Hi all,
I've just installed a new server, and migrated across our SQL 2000
database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
Premium).
I migrated the databse via a backup/restore.
We have an ASP application which connects to the database from our
intranet. When we issue a "connection.begintrans" we get a hard error:
"cannot start more transactions on this session"
We *know* this is not a nested transaction as we only have one
instance of begintrans in our code, and it's only being called once.
At least, it's not a nesting that WE have introduced.
If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
do not experience the error. But if we set the "SQL Compatibility"
option to "SQL Server 2005 (90)" we do experience the error.
Can anyone suggest what has changed (or what needs to be changed) to
resolve this? Our connection string to the database is:
pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
"User ID=" & pUser & _
";Password=" & pPassword & _
";Database=" & pDatabase & _
";Server=" & pServer
Many thanks in advance,
Jim
> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
I'm not aware of anything related to the database compatibility level that
would cause these symptoms. I haven't been able to repro this error
(VBScript below) so it may be related to the specifics of your data access
within the client transaction. You might try running a Profiler trace to
see if you can spot differences based on the compatibility level. If that
doesn't help, try posting code that can be run to reproduce the issue.
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial
Catalog=Test;Integrated Security=SSPI"
conn.BeginTrans
conn.Execute "INSERT INTO dbo.MyTable VALUES(1) SELECT 1"
'conn.BeginTrans 'causes error if comment removed
conn.Execute "INSERT INTO dbo.MyTable VALUES(1)"
conn.CommitTrans
conn.Close
MsgBox "Done"
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim" <jim@.nospam.com> wrote in message
news:l9h2539dc08v081npkeu9ot7d5fo4e97jh@.4ax.com...
> Hi all,
> I've just installed a new server, and migrated across our SQL 2000
> database to SQL 2005 Workgroup Edition (bundled with SBS 2003 R2
> Premium).
> I migrated the databse via a backup/restore.
> We have an ASP application which connects to the database from our
> intranet. When we issue a "connection.begintrans" we get a hard error:
> "cannot start more transactions on this session"
> We *know* this is not a nested transaction as we only have one
> instance of begintrans in our code, and it's only being called once.
> At least, it's not a nesting that WE have introduced.
> If we set the "SQL Compatibility" option to "SQL Server 2000 (80)" we
> do not experience the error. But if we set the "SQL Compatibility"
> option to "SQL Server 2005 (90)" we do experience the error.
> Can anyone suggest what has changed (or what needs to be changed) to
> resolve this? Our connection string to the database is:
> pCn.ConnectionString = "Provider=SQLOLEDB.1;" & _
> "User ID=" & pUser & _
> ";Password=" & pPassword & _
> ";Database=" & pDatabase & _
> ";Server=" & pServer
>
> Many thanks in advance,
>
> Jim