Solved

VBA module running an API

Posted on 2001-07-20
15
887 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
Comment Utility
What type of VBA module?
0
 

Author Comment

by:AS400John
Comment Utility
It is called within Access
0
 
LVL 3

Expert Comment

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

Author Comment

by:AS400John
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 16

Expert Comment

by:Richie_Simonetti
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Echo is the property i just talking about.
0
 
LVL 49

Expert Comment

by:DanRollins
Comment Utility
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
Comment Utility
Waiting...
0
 
LVL 1

Expert Comment

by:Computer101
Comment Utility
Comment from expert accepted as answer

Computer101
E-E Moderator
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

772 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

10 Experts available now in Live!

Get 1:1 Help Now