| Author |
Message |
|
Martin Leg
#1 / 15
|
 SQL Server dynaset not updatable. Quote:
> I have created a table on SQL server. I can insert records
> using executesql but when I open a dynaset it is not updatable
> and when I try to open a table I get an error. Does anyone
> have any ideas?
Dynasets are not supported by ODBC (which I assume is the way you are connecting) - only Snapshot type Recordsets. If you access your SQL Server tables via an attached Access table however you may use Dynasets (but just make sure you've got a unique index on the appropriate table). -- Regards,
|
| Sat, 23 May 1998 03:00:00 GMT |
|
 |
|
Ed Johns
#2 / 15
|
SQL Server dynaset not updatable. I have created a table on SQL server. I can insert records
using executesql but when I open a dynaset it is not updatable
and when I try to open a table I get an error. Does anyone
have any ideas?
Thanks,
Ed Johnson
|
| Sat, 23 May 1998 03:00:00 GMT |
|
|
|
Joe Foster of Bo
#3 / 15
|
SQL Server dynaset not updatable. Quote:
> I have created a table on SQL server. I can insert records
> using executesql but when I open a dynaset it is not updatable
> and when I try to open a table I get an error. Does anyone
> have any ideas?
> Thanks,
> Ed Johnson
Does the table have a primary key and a unique index corresponding to that key? --
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!
|
| Sun, 24 May 1998 03:00:00 GMT |
|
|
|
Ray Port
#4 / 15
|
SQL Server dynaset not updatable. Quote:
>Subject: Re: SQL Server dynaset not updatable. >Date: Tue, 05 Dec 1995 18:46:10 +0000
>> I have created a table on SQL server. I can insert records
>> using executesql but when I open a dynaset it is not updatable
>> and when I try to open a table I get an error. Does anyone
>> have any ideas?
>Dynasets are not supported by ODBC (which I assume is the way you are
>connecting) - only Snapshot type Recordsets.
>If you access your SQL Server tables via an attached Access table
>however you may use Dynasets (but just make sure you've got a unique
>index on the appropriate table).
>--
>Regards,
Not quite true. We have a fairly large Sybase SQL Server database (around 1 gig and about 6 million rows in 29 tables) and we use dynasets via ODBC throughout our application. However, the dynasets you create via ODBC are not updatable as far as I know so in that respect, these dynasets are a little like snapshots. =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Ray Porter
Applications Analyst Programmer
Administrative Data Processing
University of North Carolina at Chapel Hill
Phone: 919/966-5878 Fax: 919/962-0900
Home Page: http://www.adp.unc.edu/~lrp/lrp.html
|
| Sun, 24 May 1998 03:00:00 GMT |
|
|
|
Andrew Cart
#5 / 15
|
SQL Server dynaset not updatable. Quote:
>Dynasets are not supported by ODBC (which I assume is the way you are >connecting) - only Snapshot type Recordsets. >If you access your SQL Server tables via an attached Access table >however you may use Dynasets (but just make sure you've got a unique >index on the appropriate table). >-- >Regards,
Does this mean that Dynasets cannot be used to change a SQL Server record? If so I assume a Data Control cannot be used either. I've been using VB and SQL Server for about 8 months and have struggled trying to write to a SQL table. How can one do this? Is it in the manual or knowledge base? Andrew Carter
Oak Ridge National Laboratory
Oak Ridge, TN
/*
Oak Ridge National Laboratory
http://www.ornl.gov
*/
|
| Sun, 24 May 1998 03:00:00 GMT |
|
|
|
Martin Leg
#6 / 15
|
SQL Server dynaset not updatable. Quote:
> >Dynasets are not supported by ODBC > Not quite true. We have a fairly large Sybase SQL Server database (around 1
> gig and about 6 million rows in 29 tables) and we use dynasets via ODBC
> throughout our application. However, the dynasets you create via ODBC are not
> updatable as far as I know so in that respect, these dynasets are a little
> like snapshots.
That's what I meant Ray - the dynamic aspect of the recordset isn't supported. But if I remember correctly it's better to use Dynasets anyway if you are retrieving more than 500 or so records ? --
Regards,
|
| Sun, 24 May 1998 03:00:00 GMT |
|
|
|
James Colli
#7 / 15
|
SQL Server dynaset not updatable. Quote:
>If you access your SQL Server tables via an attached Access table
>however you may use Dynasets (but just make sure you've got a unique
>index on the appropriate table).
On the SQL side that is... :) Indexing the Access table via Access doesn't do any good as far as I know. James
|
| Mon, 25 May 1998 03:00:00 GMT |
|
|
|
ffe
#8 / 15
|
SQL Server dynaset not updatable. Quote:
>I have created a table on SQL server. I can insert records
>using executesql but when I open a dynaset it is not updatable
>and when I try to open a table I get an error. Does anyone
>have any ideas?
>Thanks,
>Ed Johnson
You need long integer primary keys in attached tables if SQL Server or Oracle. Access creates dynasets using long integer keys to reference the actual records. If you don't have a key in the server table, the dynaset is read only.
|
| Mon, 25 May 1998 03:00:00 GMT |
|
|
|
Ray Port
#9 / 15
|
SQL Server dynaset not updatable. Quote:
>Subject: Re: SQL Server dynaset not updatable. >Date: Wed, 06 Dec 1995 23:29:27 +0000
>> >Dynasets are not supported by ODBC >> Not quite true. We have a fairly large Sybase SQL Server database (around 1
>> gig and about 6 million rows in 29 tables) and we use dynasets via ODBC
>> throughout our application. However, the dynasets you create via ODBC are not
>> updatable as far as I know so in that respect, these dynasets are a little
>> like snapshots.
>That's what I meant Ray - the dynamic aspect of the recordset isn't
>supported. But if I remember correctly it's better to use Dynasets
>anyway if you are retrieving more than 500 or so records ?
Right. If I remember correctly, a snapshot tries to return all the data to the client where a dynaset only returns about +/- 100 rows around the current row. In other words, using a dynaset for relatively large result sets places much less strain on the network and on the work station. Quote: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Ray Porter Applications Analyst Programmer Administrative Data Processing University of North Carolina at Chapel Hill Phone: 919/966-5878 Fax: 919/962-0900
Home Page: http://www.adp.unc.edu/~lrp/lrp.html
|
| Mon, 25 May 1998 03:00:00 GMT |
|
|
|
Ronak Sh
#10 / 15
|
SQL Server dynaset not updatable. Quote: >Right. If I remember correctly, a snapshot tries to return all the data to
>the client where a dynaset only returns about +/- 100 rows around the current
>row. In other words, using a dynaset for relatively large result sets places
>much less strain on the network and on the work station.
Does the dynaset actually bring across data in that fashion? I know it gets references to all the rows, but does it bring across the actual data itself for a number of them? I imagine it must, and this number can probably be changed by CacheSize. However, does the dynaset bring across the memo/ole fields as well for the 100 rows? ---------------------------------------------------------------------------
MS Access Programming and WWW Development ---------------------------------------------------------------------------
|
| Fri, 29 May 1998 03:00:00 GMT |
|
|
|
Roberto Nhu
#11 / 15
|
SQL Server dynaset not updatable. Quote:
>>That's what I meant Ray - the dynamic aspect of the recordset isn't
>>supported. But if I remember correctly it's better to use Dynasets
>>anyway if you are retrieving more than 500 or so records ?
>Right. If I remember correctly, a snapshot tries to return all the data to
>the client where a dynaset only returns about +/- 100 rows around the current
>row. In other words, using a dynaset for relatively large result sets places
>much less strain on the network and on the work station.
So, what's the advantage of using snapshot, anyway? Even for small databases, there wouldn't be much difference, right?
|
| Sat, 30 May 1998 03:00:00 GMT |
|
|
|
Chris Dod
#12 / 15
|
SQL Server dynaset not updatable. There seems to be quite a bit of vague fumbling about in this thread. These & other issues are covered in the Knowledge Base. Check out ...
http://198.105.232.5:80/KB/DEVELOPR/vb/Q103442.htm
ChrisD
|
| Sat, 30 May 1998 03:00:00 GMT |
|
|
|
Myakl
#13 / 15
|
SQL Server dynaset not updatable.
writes: Quote: >So, what's the advantage of using snapshot, anyway?
>Even for small databases, there wouldn't be much difference, right?
A snapshot is faster in terms of navigation using JET. Moving to the end and such is quick when the records exist on the local client. With a DS, its dynamic nature, means the data has to be refreshed occasionally from the server. Speedwise this can slow it down, but if it is important that changes from
other users be reflected in other recodsets, then the DS is the answer.
- Mike
|
| Tue, 02 Jun 1998 03:00:00 GMT |
|
|
|
Myakl
#14 / 15
|
SQL Server dynaset not updatable. Quote:
>Does the dynaset actually bring across data in that fashion? I know
>it gets references to all the rows, but does it bring across the
>actual data itself for a number of them?
I do not think so. JET 2.x, anyway just stores unique pointers (based on the ID/time stamp etc) for each row on the client. When you .MoveLast, you are simply forcing JET to build the pointers; otherwise 1 row is current with a number of others (CacheSize) present on the client. Snapshots will bring the actual data back to te server, as will recordets
with certain types of Access SQL statements (Left$() etc) in them.
- Mike
|
| Tue, 02 Jun 1998 03:00:00 GMT |
|
|
|
James Shiel
#15 / 15
|
SQL Server dynaset not updatable. Quote:
>writes:
>>So, what's the advantage of using snapshot, anyway?
>>Even for small databases, there wouldn't be much difference, right?
>A snapshot is faster in terms of navigation using JET. Moving to the end
>and such is quick when the records exist on the local client. With a DS,
>its dynamic nature, means the data has to be refreshed occasionally from
>the server.
Bear in mind that a Snapshot is only faster if you are scanning the dataset more than once. If you are creating a recordset and scanning through it just once, a dynaset is faster. Another point to be aware of is that a dynaset needs to be refreshed too if
it is to contain new records.
Hope I'm not being a smart-alec!
James Shields
|
| Fri, 12 Jun 1998 03:00:00 GMT |
|
| |
