DB is ReadOnly?

Hi all,
I am using DAO 3.6 to connect to a SQL Server db on a remote machine.
I can open and close the connection, read records, run stored procedures
but when I try to add a record I get "object or database is read only"
If I connect to the database using ADO I can add records.
Here is my code:
' ----
Dim wrk_DAO As DAO.Workspace
Dim cnn_DAO As DAO.Connection
Dim rec_DAO as DAO.Recordset
Set wrk_DAO = CreateWorkspace("MyWorkspace", "sa", "pw", dbUseODBC)
Set cnn_DAO = wrk_DAO.OpenConnection("MyConnection", dbDriverNoPrompt,
False, _
"ODBC;DATABASE=SQLTest;UID=sa;PWD=pw;DSN=odbc_test ")
Set rec_DAO = cnn_DAO.OpenRecordset(strSQL, dbOpenDynamic)
rec_DAO.AddNew '<<< ERROR occurs here
rec_DAO!Field1 = 1
rec_DAO!Field2 = "Test"
rec_DAO.Update
rec_DAO.Close
cnn_DAO.Close
' ----
If I use the same code but only read, it works fine.
What am I doing wrong?
Thanks.
kpg
"kpg" <ipost@.thereforeiam.com> wrote in message
news:%23Oremk8pEHA.2588@.TK2MSFTNGP12.phx.gbl...
> I am using DAO 3.6 to connect to a SQL Server db on a remote machine.
> I can open and close the connection, read records, run stored procedures
> but when I try to add a record I get "object or database is read only"
> If I connect to the database using ADO I can add records.
> Here is my code:
> ' ----
> Dim wrk_DAO As DAO.Workspace
> Dim cnn_DAO As DAO.Connection
> Dim rec_DAO as DAO.Recordset
> Set wrk_DAO = CreateWorkspace("MyWorkspace", "sa", "pw", dbUseODBC)
> Set cnn_DAO = wrk_DAO.OpenConnection("MyConnection", dbDriverNoPrompt,
> False, _
> "ODBC;DATABASE=SQLTest;UID=sa;PWD=pw;DSN=odbc_test ")
> Set rec_DAO = cnn_DAO.OpenRecordset(strSQL, dbOpenDynamic)
> rec_DAO.AddNew '<<< ERROR occurs here
> rec_DAO!Field1 = 1
> rec_DAO!Field2 = "Test"
> rec_DAO.Update
> rec_DAO.Close
> cnn_DAO.Close
> ' ----
> If I use the same code but only read, it works fine.
> What am I doing wrong?
It may not be anything you are doing "wrong", other than using a possibly
incompatible, old library to access SQL Server. DAO was written an intended
to be a library for Jet. The fact that your code works in ADO speaks to the
proper library to use...
Steve
|||"Steve Thompson" wrote
> It may not be anything you are doing "wrong", other than using a possibly
> incompatible, old library to access SQL Server.
<snip>
Yes, thanks for the reply. I solved my little problem however...
If I set the locking type to 'dbOptimisticValue' it works fine:
Re:
'--
Set rec_DAO = cnn_DAO.OpenRecordset(strSQL, dbOpenDynamic, _
dbExecDirect, dbOptimisticValue)
'--
I guess the default was a snapshot?
BTW: I would never really consider using DAO for a SQL Server DB,
I was mainly interested in benchmarking the difference between Access, SQL
Server, ADO and DAO, for use in a management proposal.
I use a SQL Server and an Access db located on the same machine on my LAN.
This way the netword has to be involved in all IO.
My test is not very rigid from a scientific standpoint and the results are
not very
suprising, but here the are:
Add 1000
Add x100
Read
SP
Delete
Total
ADO ACCESS
0.07
1.66
0.03
0.03
0.02
1.81
DAO ACCESS
0.06
1.2
0.03
0.02
0.01
1.32
ADO SQL
1.54
1.63
0.03
0.03
0.02
3.25
DAO SQL
0.66
2.67
0.05
0.04
0.03
3.45
times are in seconds.
Add 1000: adds 1000 small records by opening the db once, looping, then
closing.
Add x100: adds the same recodes bu7t opens and closes the db for each record
(100 times)
Read: reads the 1100 records by opening once, reading in a while loop,
closing.
SP: run a stored procedure instead of a SELECT Query, otherwise the same as
Read
Delete: execute a DELETE FORM Table command.
kpg
In theory, there is no difference between theory and practice.
But, in practice, there is - Jan L.A. van de Snepscheut
|||I'm glad you found that -- the default is snapshot from what I remember (and
that is digging back a while).
BTW, speed is only one factor, I would be more concerned about stability,
portability and support of your code. While DAO is fast (and it has always
been known for speed), DAO is not the way to go for a strategic software
development with SQL Server. You can achieve higher performance in SQL
Server by taking advantage of VIEWS and Stored Procedures (for data
manipulation).
Steve
"kpg" <ipost@.thereforeiam.com> wrote in message
news:upbmBF$pEHA.596@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> "Steve Thompson" wrote
possibly
> <snip>
> Yes, thanks for the reply. I solved my little problem however...
> If I set the locking type to 'dbOptimisticValue' it works fine:
> Re:
> '--
> Set rec_DAO = cnn_DAO.OpenRecordset(strSQL, dbOpenDynamic, _
> dbExecDirect, dbOptimisticValue)
> '--
> I guess the default was a snapshot?
> BTW: I would never really consider using DAO for a SQL Server DB,
> I was mainly interested in benchmarking the difference between Access, SQL
> Server, ADO and DAO, for use in a management proposal.
> I use a SQL Server and an Access db located on the same machine on my LAN.
> This way the netword has to be involved in all IO.
> My test is not very rigid from a scientific standpoint and the results are
> not very
> suprising, but here the are:
>
> Add 1000
> Add x100
> Read
> SP
> Delete
> Total
> ADO ACCESS
> 0.07
> 1.66
> 0.03
> 0.03
> 0.02
> 1.81
> DAO ACCESS
> 0.06
> 1.2
> 0.03
> 0.02
> 0.01
> 1.32
> ADO SQL
> 1.54
> 1.63
> 0.03
> 0.03
> 0.02
> 3.25
> DAO SQL
> 0.66
> 2.67
> 0.05
> 0.04
> 0.03
> 3.45
>
> times are in seconds.
> Add 1000: adds 1000 small records by opening the db once, looping, then
> closing.
> Add x100: adds the same recodes bu7t opens and closes the db for each
record
> (100 times)
> Read: reads the 1100 records by opening once, reading in a while loop,
> closing.
> SP: run a stored procedure instead of a SELECT Query, otherwise the same
as
> Read
> Delete: execute a DELETE FORM Table command.
> --
> kpg
> In theory, there is no difference between theory and practice.
> But, in practice, there is - Jan L.A. van de Snepscheut
>
>