A fully automated database program

[Guest]

I am using Access 2002 (at least I will if I can do this) with Office 2003.

I have an application that I need help with. It starts with the program
retrieving a text file from an FTP site. The program then reads the comma
sperated text until the end of the line and uses the text as database
entries. I then need to calculate running averages for generating a text
file with the values.

The topics I need to concentrate on are file retrieval, coding a line
reading and database table generator routine, calculating running averages
(although that sounds easy with queries), and writing results to a text file.

I've tried googling file retrieval and info on it is pretty scarce. Anyone
able to point me in the right direction?

 

[Steve]

Check out www.mvps.org/access. Most of what you want to do is there.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Guest]

Hi Carl.

This is actually very easy with some VBA code behind a form. If you want the
database to do this automatically, place the following code in the Form_Load
event:

Private Sub Form_Load()
On Error GoTo Err_Form_Load

'Find out what file the user wants to import.
Dim strFileName As String
strFileName = "C:\MyTextFile.csv"

'Set a connecion to your local database.
Dim cnnLocal As New adodb.Connection
Set cnnLocal = Access.CurrentProject.Connection

'Just a precaution to ensure there is a file to read
' (only necessary if you ask the user for a file name).
If (Len(strFileName) > 0) Then
Dim strLine As String
Dim lngCount As Long
Dim curAverage As Currency

'Initialize your vaiables.
lngCount = 1
curAverage = 0

'Open the selected file.
Open strFileName For Input As #1

'Read each line of this file.
Do Until (EOF(1) = True)
'Get the current line's data.
Line Input #1, strLine

'Split ou the comma-seperated values.
Dim arrData() As String
arrData = Split(strLine, ",")

'Import this line into a new record in this database.
' BE SURE TO KNOW YOUR COLUMNS IN THE CSV FILE,
' I AM JUST USING 3 COLUMNS FOR AN EXAMPLE HERE.
cnnLocal.Execute "INSERT INTO YourTableName (" & _
"Field1, " & _
"Field2, " & _
"Field3) " & _
"VALUES ('" & arrData(0) & "', " & _
"'" & arrData(1) & "', " & _
arrData(2) & ");", , _
adodb.adCmdText + adodb.adExecuteNoRecords

'Now add the value you want to your average variable.
curAverage = curAverage + arrData(2)

'Give the processor a break.
DoEvents
lngCount = lngCount + 1
Loop

'Close the selected file.
Close #1

'Now calculate your true average.
curAverage = curAverage / lngCount

'Now tell the user what happened.
Call MsgBox("All call records in the file you selected were
successfully imported!", _
vbOKOnly, "Import Successful")
End If

Exit_Form_Load:
'Free up some memory.
Set cnnLocal = Nothing

'OK, we're done here.
Exit Sub

Err_Form_Load:
Call MsgBox(Err.Description, vbExclamation, "Form Load Error")
Resume Exit_Form_Load

End Sub

 

[Guest]

For the file retrieval part, you'll need to write a batch file and call
Windows' built-in FTP program to retrieve it for you. Something like this:

Public Sub FTP()
On Error GoTo Err_FTP

'Create a new script file.
Open "C:\ftp.scr" For Output As #1

'Now add in the lines to download everything in your FTP folder.
Print #1, "open ftp://your_server/folder"
Print #1, "username"
Print #1, "password"
Print #1, "prompt"

'Set the transmission mode.
Print #1, "binary"

'Send all of our files.
Print #1, "get ""*.*"""

'And finally, tell the FTP program that we're done.
Print #1, "bye"

'Close the new file.
Close #1

'Now call the Windows FTP program to send out this file.
Dim strDir As String
Dim strExe As String
strDir = Environ$("COMSPEC")
strDir = left$(strDir, Len(strDir) - Len(Dir(strDir)))
strExe = strDir & "ftp.exe -s:""C:\ftp.scr"""
Call Shell(strExe)

'Now the script we just used.
Kill "C:\ftp.scr"

Exit_FTP:
Exit Sub

Err_FTP:
'Let's just assume any error was a file permission
' error on trying to delete the script, and we'll
' ignore it.
Err = 0
Resume Exit_FTP

End Sub