ftp-ado

[Lyle Fairfield]

Recently we've had some posts about ftp. I wrote my own ftp class a few
years ago and it works satisfactorily for me.

Today for a simple writing of text or binary to a file on a server I use
ADO rather than FTP (I'm using 2.8 and am not sure when this capability
occurred).

This creates or overwrites a file on my server. Then it writes some text to
that file.

A little investigation would allow one to modify this to transfer a file,
and/or to change the direction of transfer.

Sub temp()
Dim r As ADODB.Record
Dim s As ADODB.Stream
Set r = New ADODB.Record
Set s = New ADODB.Stream
' open the file for writing
' over write if it exists
' else create it
r.Open "http://www.ffdba.com/temporary.txt", , adModeWrite,
adCreateOverwrite
' windows xp asks for username and password here
' but will "remember" them if prompted
' (you can include them in your code, of course)
With s
' open a stream
' associated with the record
.Open r, , adOpenStreamFromRecord
' write some text
.WriteText "This is a test!"
.Close
End With
r.Close
End Sub

On http://www.ffdba.com a file called temporary.txt appears and its
contents are "This is a test!".

MS examples show setting r and s to nothing. I know of no reason to set
anything except DAO objects to nothing, but maybe you would like to do
this.

--
Lyle
--

[Darryl Kerkeslager]

"Lyle Fairfield" <Look...@FFDBA.Com> wrote

> MS examples show setting r and s to nothing. I know of no reason to set
> anything except DAO objects to nothing, but maybe you would like to do
> this.

Lyle,

I'm definitely going to save your FTP code for later - no use today, but
tomorrow who knows - but I am interested now in the quoted part. Most ADO
code read sets the Recordset = Nothing; but other things I've read say that
the resources will be returned regardless. I've vacillated between strictly
setting all recordsets to Nothing and not bothering.

Can you give me your best case for it NOT being necessary? A lot of the PCs
that run my app only have 128 MB memory, so I am somewhat concerned about
the potential for any memory leaks.

Darryl Kerkeslager

Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.
See: www.adcritic.com/interactive/view.php?id=5927

[Lyle Fairfield]

Darryl Kerkeslager wrote:
> "Lyle Fairfield" <Look...@FFDBA.Com> wrote
>
>
>>MS examples show setting r and s to nothing. I know of no reason to set
>>anything except DAO objects to nothing, but maybe you would like to do
>>this.
>
> Lyle,
>
> I'm definitely going to save your FTP code for later - no use today, but
> tomorrow who knows - but I am interested now in the quoted part. Most ADO
> code read sets the Recordset = Nothing; but other things I've read say that
> the resources will be returned regardless. I've vacillated between strictly
> setting all recordsets to Nothing and not bothering.
>
> Can you give me your best case for it NOT being necessary? A lot of the PCs
> that run my app only have 128 MB memory, so I am somewhat concerned about
> the potential for any memory leaks.

As I said, TTBOMK, there is no reason whatever to think that VBA does
not look after garbage collection as efficiently as any other
programming language (that is when a variable is no longer reachable -
no variable, object, function whatever refers to it, including the
global object - the memory to which it points is released) EXCEPT for
DAO objects, and I think not all DAO objects, perhaps just the Database
and the Recordset, but since it's a long time since I've used DAO I
could well be wrong here. I haven't set anything to nothing (except in
reused legacy code) for years and I have had zero problems with memory
or ghosts.

[Trevor Best]

Nice one.

I have one client in Baku with a regular 40MB download via FTP from the
States. ATM I use the Windows CLI FTP client driven by a script file and
redirecting output to a text file that my program reads and looks for
errors. This has the effect of a dos box saying "downloading, please
wait..." and sitting there for 1 or 2 hours on a good day (overnight
procedure of course).

I hope this will give me more control over handling errors and retries
and even give the office cleaner something to look at by way of a
progress meter :-)

Thanks.

--
This sig left intentionally blank

[jimfo...@compumarc.com]

Lyle Fairfield wrote:
>
> As I said, TTBOMK, there is no reason whatever to think that VBA does

> not look after garbage collection as efficiently as any other
> programming language (that is when a variable is no longer reachable
-
> no variable, object, function whatever refers to it, including the
> global object - the memory to which it points is released) EXCEPT for

> DAO objects, and I think not all DAO objects, perhaps just the
Database
> and the Recordset, but since it's a long time since I've used DAO I
> could well be wrong here. I haven't set anything to nothing (except
in
> reused legacy code) for years and I have had zero problems with
memory
> or ghosts.

For me it's not an issue of whether VBA will release the memory or not.
It's a habit from preventing leaks using other languages. That seems
like a good habit to get into whether or not the language releases the
memory. But I don't mind when other programmers rely on the default
behavior. It's similar to textbox.Value vs. textbox. MS will likely
never change the default but they recommend using .Value in case they
do. BTW, your ADO solution is excellent. I have big plans for using
it.

James A. Fortune

[Darryl Kerkeslager]

Thanks, Lyle. I think "Set rs = Nothing" occurs about 150 times in my code.
I just would like to be convinced that I can do away with it by some
reliable test. For now, memory being more precious than other resources on
the computers we use, I'll stick with it.

--