Re: Lots of Data :-(

> I wrote a simple Visual Basic 6 program that updates a SQL Server table
> with a small stream of text. The problem I'm having is that, even
> though it's only about 100 characters of text I'm sending, the
> transaction is about 2.5mb in size. I can tell this with a sniffer
> trace, but I can't figure out what extra information is being sent to
> make the transfer size so large.
>
> The other problem I'm having involves using FIND to find the record
> before I write it, because I want to update the record if it already
> exists. If it doesn't already exist I create a new record. According
> to the sniffer trace, the FIND command is returning all of the data in
> the entire SQL table. Can't I just find a record in the table without
> returning all of the records in the entire table?

Not the way you have done it.

Your code is amazingly inefficient.
In the code below you have not shown what txttable is but if it is just the
name of the table, then eventually (no 5th parameter) ADO will figure this
out and the recordset will consist of server-sided recordset consisting of
all records in the table, CacheSize being 1.

Open() for a RecordSet is _THE_ most important call of a RecordSet,
defaulting on 5th argument is criminal. Tell ADO what the Source Parameter
is, otherwise it will have to work.

Find is best used if you have a client-sided recordset, it is extremely poor
if server-sided.
It will have to keep fetching records, 1 at a time, and examining them to
see if the match the Find clause.
The same could be said about Sort, Filter.
Far better is using SQL's WHERE clause. Get the database to do the work, not
ADO.
And a ForwardOnly cursor is fastest, you are not scrolling backwards.

> ' Open the connection.
> Set cnn = New ADODB.Connection
> With cnn
> .Provider = "Microsoft.Jet.OLEDB.4.0"
> .Open strConnect
> End With

What is going on here? You have Provider as SQLOLEDB.1 so what is
Jet.OLEDB.40 doing here?

SourceTxt = "SELECT TOP 1 * FROM yourtable WHERE compname LIKE '%" &
vbcompname &"%'"

Set rst = New ADODB.Recordset
With rst
' Open the table by using a scrolling Recordset object.
.Open Source:= SourceTxt, _
ActiveConnection:=cnn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockOptimistic, adCmdText

If .BOF && .EOF Then
.AddNew
End If
!DateTime = vbdatetime
!compname = LCase(vbcompname)
.Update

.Close
Set rst = Nothing
End With

Stephen Howe


.

Relevant Pages

  • Re: AcXP MS-SQL --> Sperrungen
    ... Wenn Du ein Recordset aufmachst ruft Access zunächst alle Primärschlüssel ... Und der SQL Server verwendet Fällen, wo alle Daten einer Tabelle ...
    (microsoft.public.de.access.clientserver)
  • RE: TransferText from a table with a selection-parameter
    ... > temporary table where I store the recordset inside because there may be ... Each of your users can use separate temp tables in your SQL Server database. ...
    (microsoft.public.access.modulesdaovba)
  • Re: VB-ADO-SQL Server : SQL Server performs logins after some queries
    ... The reason that I use Object types is that my code should work ... with a SQL Server 2K database (at a internet server) as well as MS Access ... recordset to a ADODB.recordset or a DAO recordset prior to creating the ...
    (microsoft.public.vb.database.ado)
  • Re: Zugreifen auf eine Stored Procedure
    ... >> wie kann ich denn auf eine Stored Procedure die auf dem SQL Server ... Die Stored Procedure soll mir eine Recordset ... > User und Passwort sind Eigenschaften der Verbindungszeichenfolge ...
    (microsoft.public.de.sqlserver)
  • Recordset is Not Updateable
    ... The query is a 4 table join. ... Server all have defined PK. ... Now with a SQL Server 2000 backend, the recordset is not ...
    (microsoft.public.access.queries)