DB is ReadOnly?

DB is ReadOnly?

Post by kpg » Sun, 03 Oct 2004 00:13:10


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
 
 
 

DB is ReadOnly?

Post by Steve Thom » Sun, 03 Oct 2004 04:15:27


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

 
 
 

DB is ReadOnly?

Post by kpg » Sun, 03 Oct 2004 05:00:15

"Steve Thompson" wrote

<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
 
 
 

DB is ReadOnly?

Post by Steve Thom » Wed, 06 Oct 2004 04:17:25

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



possibly
record
as