Re: Lots of Data :-(

> I wrote a simple Visual Basic 6 program that updates a SQL Server table
> with a small stream of text.

I presume that you do not mean a defined "stream" but rather a string of
text that might be able 100 char.

> 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.

It sounds like you are using ADO functionality for friendly needs and you
might want to check out using TSQL instead.

> 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?

Use something like this ... this will check for the existance of your record
and either do an update or an insert as needed. No need to mess with the
faulty FIND method.

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL as String
Dim varReturn as Variant

Set cnn = New ADODB.Connection

With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "Provider=SQLOLEDB.1;User ID=" & txtUID & ";Password=" & txtPWD &
";Initial Catalog=" & txtDatabase & ";Data Source=" & txtServer &
";Network Library=DBMSSOCN"
End With

set rst = cnn.Execute("select count(*) from " & txttable & " where compname
like '" & vbcompname & "%'")

if rst.EOF
cnn.Execute "insert into " & txttable & " (field_x) select 'asdf'"
else
cnn.Execute "update " & txttable & " set field_x = 'asdf'"
end if

rst.Close
cnn.Close

set rst = Nothing
set cnn = Nothing

.

Relevant Pages

  • Re: Getting to SQL Server without ODBC from VBA
    ... Dim cnn As ADODB.Connection ... 'Use for SQL server (OLEDB) ... Set cnn = New ADODB.Connection ...
    (microsoft.public.excel.programming)
  • Re: For XML Problem with IIS6 and W2k3
    ... The sql that gets executed is passed in as a string ... executed then I should get a sql server error. ... >> dim adoConn ... >> dialect for the command stream to be a SQL query. ...
    (microsoft.public.sqlserver.xml)
  • Re: Datatable Column ByteArray Problem
    ... 'Dim utf8encoder As New UTF8Encoding ... ' Crypto functions need a stream to output the encrypted info. ... 'We want to use a memory stream ... >> CaseSensitive False Boolean ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Programmatically changing a SQL view in a ADP
    ... Public Sub TestCreateView() ... Dim cn As ADODB.Connection ... Dim rst As ADODB.Recordset ... to alter the 'Select' statement of a SQL Server view from within a ADP ...
    (microsoft.public.access.modulesdaovba)
  • RE: Error in SQL Enterprise Manager after migrating a user & their WS
    ... it is a SQL Server related issue. ... server, the restoration of these Registry Keys is not something we support, ... Dim oServerGroups ...
    (microsoft.public.windows.server.migration)