Stuck

[Skeen]

Hi everyone,

I want to populate my database with data files from a private ftp site. I've written a program that picks up local files and places them in the database, but I don't know how to use the 'Inet1' control properly. Could anyone give me a brief code example?

If I go to the ftp site a I can incorporate the username and password thus;
"ftp://user[email protected]/Daily files/

Heres m comlete code - Cheers

Skeen

[Skeen]

Private Sub Command1_Click()

onclick.End
End Sub


Private Sub Inet1_StateChanged(ByVal State As Integer)
Dim vtData As Variant
Select Case State
Case icResponseCompleted
Open txtOperation For Binary Access Write As intFileNum
vtData = Inet1.GetChunk(1024, icString)
Do While LenB(vtData) > 0
Put intFileNum, , vtData
vtData = Inet1.GetChunk(1024, icString)
Loop
Put intFileNum, , vtData
Close intFileNum
End Select
End Sub


End Sub



Option Explicit
Option Base 1
Private Sub Form_Load()



Dim FileName As String
Dim FileNumber As Integer
Dim Datak() As String
Dim DirPath As String
Dim FileSpec As String
Dim FindData As WIN32_FIND_DATA
Dim FindHandle As Long
Dim FindNextHandle As Long
Dim filestring As String
Dim TotalFiles As Integer
Dim Arraypos As Integer, i As Integer
Dim StartDateTime As String, EndDateTime As String, DurationSecs As String
Dim CallersNumber As String, DialledNumber As String
Dim TerminatingNumber As String, ValuePence As String
Dim Data As String
Dim Count As Integer
Dim DBconnect As Database
Dim Filenames() As String
Dim FileNumero
Dim FileNom

FileNumber = 1
'On Error Resume Next
TotalFiles = 0
Count = 0

'DirPath = "E:\All_Ed's_shite\Opal_ftp_data\"
DirPath = Inet1.OpenURL("ftp://UsrName:[email protected]/Daily Files/CDRs/")
FileSpec = "*.CRB"

FindHandle = FindFirstFile(DirPath & FileSpec, FindData)

' Loop Through Files
If FindHandle <> 0 Then

Do

DoEvents

FindNextHandle = FindNextFile(FindHandle, FindData)

If FindNextHandle <> 0 Then
If FindData.dwFileAttributes And FILE_ATTRIBUTE_DIRECTORY Then

' Its a directory
If Left$(FindData.cFileName, 1) <> "." And Left$(FindData.cFileName, 2) <> ".." Then
filestring = DirPath & Trim$(FindData.cFileName) & "\"
End If
Else
filestring = DirPath & Trim$(FindData.cFileName)
TotalFiles = TotalFiles + 1
End If
Else
Exit Do
End If

Loop
End If

ReDim Filenames(1 To TotalFiles) As String

'DirPath = "E:\All_Ed's_shite\Opal_ftp_data\"
DirPath = Inet1.OpenURL("ftp://Username:Password.opal.callmanagement.co.uk/Daily Files/CDRs/")
FileSpec = "*.CRB"

FindHandle = FindFirstFile(DirPath & FileSpec, FindData)

If FindHandle <> 0 Then

Do

DoEvents

FindNextHandle = FindNextFile(FindHandle, FindData)

If FindNextHandle <> 0 Then
If FindData.dwFileAttributes And FILE_ATTRIBUTE_DIRECTORY Then
'It' a directory
If Left$(FindData.cFileName, 1) <> "." And Left$(FindData.cFileName, 2) <> ".." Then

filestring = DirPath & Trim$(FindData.cFileName) & "\"
End If
Else
Arraypos = Arraypos + 1
Filenames(Arraypos) = DirPath & Trim$(FindData.cFileName)
End If
Else
Exit Do
End If

Loop
End If

i = 1

For i = i To TotalFiles

'Count = 0

FileName = Filenames(i)
'FileNumber = i 'Freefile

'Read A Line
Open FileName For Input Access Read As #FileNumber
Do Until EOF(FileNumber)

'Count = Count + 1
Line Input #FileNumber, Data
Debug.Print (Data)

Datak = Split(Data, ",", -1)

StartDateTime = Datak(0)
EndDateTime = Datak(1)
DurationSecs = Datak(2)
CallersNumber = Datak(3)
DialledNumber = Datak(4)
TerminatingNumber = Datak(5)
ValuePence = Datak(6)

Call OpalTRANSmodule.WriteToDB(StartDateTime, EndDateTime, DurationSecs, CallersNumber, DialledNumber, TerminatingNumber, ValuePence)


Loop
Close #FileNumber
Next i

ReDim Preserve Datak(Count)
Dim j As Integer
For j = 0 To UBound(Datak())
MsgBox Datak(j)
Next


End Sub

Private Sub cmdAddRecord_Click()
Dim DBconnect 'As Database
Dim Querry As String
Dim Store As String
Dim Var As String
Dim Result As Recordset
' Connect to Database
'Set DBconnect = OpenDatabase("E:\Oracle\oradata\AqAc")
Set DBconnect = CreateObject("ADODB.Connection")
DBconnect.ConnectionString = "Provider=MSDASQL;DSN=AqAc;UID=AcqAcc_owner;PWD=AcqAcc_owner;"
DBconnect.Open


' Standard Query SQL
QuerySQL = "INSERT INTO Opal_Data " _
& "(StartDateTime, EndDateTime, DurationSecs, CallersNumber, " _
& " DialledNumber, TerminatingNumber, ValuePence) " _
& " Values( ToDate('" & StartDateTime & "', 'DD/MM/YYYY HH:MM:SS'), " _
& " To_Date('" & EndDateTime & "', 'DD/MM/YYYY HH:MM:SS'), " _
& " '" & DurationSecs & "','" & CallersNumber & "','" & DialledNumber & "'," _
& " '" & TerminatingNumber & "','" & ValuePence & "') "


DBconnect.Execute (QuerySQL)
DBconnect.Close


End Sub