append only recordset

rulkin Guy

unread,

Jun 30, 2003, 6:10:45 AM6/30/03

to

i have one big table with many records. I want to use one "append only"
recordset with the oledbprovider. When i open the recordset, this take long
time. Have someone information to open the recordset in append only mode ?
With jet ole db, throught one link in access database, it's work fine but i
want to use the oledb provider.

'in access, the Mvt.Properties("Append-Only Rowset") = True work fine but
not with the sqloledb provider
Set DB = New ADODB.Connection
DB.Open "provider=sqloledb;Connect Timeout=15;Persist Security
Info=False;database=mar;server=guy\visual", "sa", ""
Set Mvt = New ADODB.Recordset
Mvt.ActiveConnection = DB
' Mvt.Properties("Append-Only Rowset") = True
Mvt.Open "mvtstock", , adOpenKeyset, adLockPessimistic

Val Mazur

unread,

Jun 30, 2003, 9:10:42 AM6/30/03

to

Hi,

This is because you open whole table, which means you will get all the
records from that table before you start to do anything in a recordset. What
you could do is to use INSERT SQL statement to add records. In that case you
do not need to open recordset at all and application will not hold expensive
resources to support locking of opened recordset. Another way is to use
query to open recordset. Something like

Mvt.Open "SELECT MyField1....... FROM MyTable WHERE 1=0", , adOpenKeyset,
adLockPessimistic

it will open recordset with no records and you could add new one using same
way as you do right now

--
Val Mazur
Microsoft MVP

"rulkin Guy" <rulk...@visualconcept.be> wrote in message
news:3f000c25$0$1064$ba62...@reader1.news.skynet.be...

rulkin Guy

unread,

Jun 30, 2003, 11:12:49 AM6/30/03

to

Hi,

This work fine but it isn't very beautiful. I found one provider working
with oledb : the MsDataShape. With it, the append-only rowset is supported.
If i use "provider=msdatashape;data provider=sqloledb..." in my connection
string, is it bad ?

Thanks,

Guy RULKIN
Concept Systems S.A.

"Val Mazur" <grou...@hotmail.com> a écrit dans le message de
news:%23Slv4jw...@TK2MSFTNGP10.phx.gbl...

Val Mazur

unread,

Jun 30, 2003, 11:21:11 AM6/30/03

to

Hi,

What do you mean "it isn't very beautiful"? You should expect bad
performance, since you opened the whole table. If you use one of those way,
which I suggested, it will work much faster. SHAPE provider is not a cure in
your case and does not have anything common with your problem

--
Val Mazur
Microsoft MVP

"rulkin Guy" <rulk...@visualconcept.be> wrote in message

news:3f0052fc$0$1050$ba62...@reader1.news.skynet.be...

rulkin Guy

unread,

Jul 1, 2003, 10:36:48 AM7/1/03

to

Hi,

I think running one query with one condition never true to add data in the
table is a tip but not the best solution. If only this way works to add
data to the sqloledb provider, i'll use it but i would prefeer the property
like the jet ole db or something like this.

In Microsoft Access Project, Microsoft use this Shape provider by default to
connect to sql server. When use or not use this shape?

Thanks,

Guy RULKIN
Concept Systems S.A.

"Val Mazur" <grou...@hotmail.com> a écrit dans le message de

news:%23jqzysx...@TK2MSFTNGP10.phx.gbl...

Val Mazur

unread,

Jul 2, 2003, 8:52:26 AM7/2/03

to

Hi,

If you read my first posting carefully, then you should see that you could
use INSERT SQL statement directly, rather than ADO recordset. Using INSERT
is a preferable way to add data

--
Val Mazur
Microsoft MVP

"rulkin Guy" <rulk...@visualconcept.be> wrote in message

news:3f019bfe$0$313$ba62...@reader0.news.skynet.be...