Solved

VBA module running an API

Posted on 2001-07-20
15
891 Views
Last Modified: 2013-11-27
I have a VBA module which downloads a file using FTPGETFILE. When the transfer is in progress the screen is open to repaint problems. How do I run the API so that windows still has control over the screen.
0
Comment
Question by:AS400John
  • 6
  • 4
  • 3
  • +2
15 Comments
 
LVL 3

Expert Comment

by:nigelrowe
ID: 6301655
What type of VBA module?
0
 

Author Comment

by:AS400John
ID: 6301689
It is called within Access
0
 
LVL 3

Expert Comment

by:nigelrowe
ID: 6301817
Does the DoEvent method, after the API call, make any difference?
0
 

Author Comment

by:AS400John
ID: 6302366
nigel- No, the thread stops at the FTPGETFILE and only starts again when it has finished so the DoEvent will not action until then.
0
 
LVL 3

Expert Comment

by:nigelrowe
ID: 6302449
OK John, What's the API call? Could you use the MSINET OCX for this? This returns immediate control to the calling app and runs in the background?
0
 
LVL 3

Accepted Solution

by:
nigelrowe earned 150 total points
ID: 6302516
I've just been fiddling around with access, you can indeed insert this object in an access form (Insert, ActiveX Control, Microsoft Internet Transfer Control . This object contains probably all of the FTP functionalites provided by API calls. However, I haven't got time to test this as I'm at work. I use this thing in Web pages, I put it in an ActiveXControl of my own with this type of function..

Public Function FTPTransfer(ByVal a_ftpfilelist As String, ByVal a_idproclist As String, ByVal targetpath As String, ByVal a_url As String, ByVal a_idofficer As Long, ByVal file_ext As String) As String
Dim ftpfilelist As Variant, idproclist As Variant, i As Integer, present As String, filename As String, ftp_command As String
    ftpfilelist = Split(a_ftpfilelist, "|")
    idproclist = Split(a_idproclist, "|")
    FTPControl.URL = a_url
    FTPTransfer = ""
    For i = 0 To UBound(ftpfilelist)
        Randomize
        present = CStr(CLng(Rnd * 10000))
        While Len(present) <> 8
            present = "0" & present
        Wend
        filename = a_idofficer & "_" & present & "_" & idproclist(i) & file_ext
        ftp_command = "SEND " & targetpath & ftpfilelist(i) & " ptofiles/" & filename
        FTPControl.Execute , CStr(ftp_command)
        While FTPControl.StillExecuting
            DoEvents
        Wend
        If i = 0 Then
            FTPTransfer = filename
        Else
            FTPTransfer = FTPTransfer & "|" & filename
        End If
    Next
End Function

Public Sub FTPGet(ByVal a_ftpfilelist As String, ByVal targetpath As String, ByVal a_url As String)
Dim ftpfilelist As Variant, i As Integer, filename As String, ftp_command As String
    ftpfilelist = Split(a_ftpfilelist, "|")
    FTPControl.URL = a_url
    For i = 0 To UBound(ftpfilelist)
        filename = ftpfilelist(i)
        ftp_command = "GET ptofiles/" & filename & " " & targetpath & ftpfilelist(i)
        FTPControl.Execute , CStr(ftp_command)
        While FTPControl.StillExecuting
            DoEvents
        Wend
    Next
End Sub

You'll notice that I have a doevents in the two function and sub, as for me, it's exactly the opposite type of problem.
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6304213
You could use a little trick with a timed event.
I mean, in a function your enable the timer and continue working with other code/procedures and so on. In the meantime, FTPGETFILE is called from within timer event and set timer enable to false.
I don't know with access but in excel you have an ontimer method.
If not, it is easy to create a True timer with an API cal and a callback procedure.
Search on MSDN for a working example (Article ID:Q180736 )
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6304216
By the way, it is the best way to create a timer without a form in VB!
And, for the record, it could be used to do asyncronical calls too. Not pretty elegant but works and is less resource eater that timer control.
0
 

Author Comment

by:AS400John
ID: 6308728
Nigel- Thanks, being a relative novice to VB it may take me a while to try your code. I will keep you informed.

Richie- I tried your Ontimer event using SLEEP (Instead of FTPGETFILE) in the timer procedure. After SLEEP, I set the timer interval to 0 (Access does not execute the timer event when the interval = 0). I had a click event on a command button that executed DoEvents until the timer interval= 0. I still had re-paint problems. I will also have a look at MSDN Q180736. Thanks  
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6310030
Well, if you have repainting problem and you have the hwnd for that problematic window, you could send a little message to that window disabling repainting and enabling again when you finished with FTP stuff.
See this link:

http://hjem.get2net.dk/vcoders/cm/tips/wincontrol_lockcontrols2.htm
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6310036
Optionally, Access has a property to do that. I don't remember what but it supposse similar to Excel ScreenUpdating.
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6310050
Echo is the property i just talking about.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7156256
Hi AS400John,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept nigelrowe's comment(s) as an answer.

AS400John, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7157051
Waiting...
0
 
LVL 1

Expert Comment

by:Computer101
ID: 7182268
Comment from expert accepted as answer

Computer101
E-E Moderator
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now