?
Solved

VBA module running an API

Posted on 2001-07-20
15
Medium Priority
?
902 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses
Course of the Month13 days, 7 hours left to enroll

801 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