Lots of Data :-(

Hello -

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?

Here's the code I'm using:

' Create the connect string from user supplied values
strConnect = "Provider=SQLOLEDB.1" _
& ";User ID=" & txtUID _
& ";Password=" & txtPWD _
& ";Initial Catalog=" & txtDatabase _
& ";Data Source=" & txtServer _
& ";Network Library=DBMSSOCN"

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

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

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

.Find ("compname LIKE '" & vbcompname & "%'")

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

.Close
.Filter = adFilterNone
Set rst = Nothing
End With

Thanks very much,

Greg.

.

Relevant Pages

  • Re: Connect to SQL Server
    ... I've tried Access connecting to SQL Server and that works OK. ... Dim strConnect1 As String ... Dim cnn As ADODB.Connection ... Dim rst As ADODB.Recordset ...
    (microsoft.public.data.ado)
  • Re: Cannot update
    ... not install the updates you referred to. ... highly recommend way for the later one is to install SQL server, ... Microsoft CSS Online Newsgroup Support ...
    (microsoft.public.windows.server.sbs)
  • RE: Ad hoc updates to system catalogs are not enabled
    ... Thank you for choosing Microsoft! ... system tables even after you disable allow updates. ... Prevent other users from accessing SQL Server while you are directly updating system ... This command starts an instance of SQL Server in single-user mode and enables allow ...
    (microsoft.public.sqlserver.security)
  • Re: Bulk inserting into database using ADO.NET... deadly slow?
    ... program (look in your SQL Server documentation for "BULK INSERT"). ... > the DataSet, so to reduce the DB updates, but I found that when reaching ... > about 100K rows the DataSet becomes slower and slower, ... why is it so painfully slow to insert big chunks ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Catastrophic Failure
    ... be a bug in the SQL Server ODBC driver rather than anything in your code. ... Dim cnn As ADODB.Connection ... Dim prm As ADODB.Parameter ...
    (microsoft.public.access.forms)