automatically update records in an access97/2000 database from .CSV

[jsparks]

I have a task to complete here, and I'm having a hard time finding information on it.

I have an HP-UX server that keeps all of the data that I can use. I have a routine setup that creates 3 flat files with the information I need.

2 of those flat files are used to fill data to one access table, and the other flat file is used to fill data in a different access table.

I am familiar with using update queries to change the information in the fields in a record. What I need to do here is automate the process somehow. I need a way to import data from a .CSV file to update records in a table, and do this without any user intervention, other than perhaps a mouseclick or two to start the process.

The final goal is a batch file on the HP-UX box that creates the 3 flat files, and a timed event that moves these files to our NT server. Another timed event runs the update query. This would happen 2 or 3 times a day.

Any suggestions would be greatly appreciated. Thanks in advance!

 

[jimgrin]

Automating this process (at least the Access bit) is fairly easy, depending on how familiar you are with Visual Basic for Applications.
If you have never used VBA then there is probably a bit of a learning curve here, but it will pay off.
You can import data from spreadsheets with the following:

DoCmd.TransferSpreadsheet [transfertype][, spreadsheettype], tablename, filename[, hasfieldnames][, range]

Have a look at the help and you will see that you can import from a number of different formats. From your VBA code you can run the append or update query after you have imported the spreadsheet.

Jim.

 

[Telsa]

We import csv files that are checksum data on a click of a button... here is the code we use.. try adapting it to work for you.

Function AddFiles()
' this updates the files table with new checksum information

Dim strDb, strCSVPath, strArchPath As String
Dim oDatabase As Database
Dim oWorkspace As Workspace
Dim oRecordSet As Recordset

On Error GoTo ErrorHandler:

strCSVPath = "g:\Projects\Checksum\"
strArchPath = "g:\Projects\zArchive\Checksum\"

Set oDatabase = CurrentDb

Dim MyFile As String

MyFile = ""
MyFile = Dir(strCSVPath & "*.csv&quot
oDatabase.Execute "DELETE * FROM [add_files];"
Do While (MyFile <> &quot;&quot
DoCmd.TransferText , &quot;Files Import Specification&quot;, &quot;add_files&quot;, strCSVPath &amp; MyFile

FileCopy strCSVPath &amp; MyFile, strArchPath &amp; MyFile
Kill strCSVPath &amp; MyFile
MyFile = Dir
Loop
oDatabase.Execute &quot;INSERT INTO [files] SELECT * FROM [add_files];&quot;
oDatabase.Execute &quot;UPDATE [files] INNER JOIN [add_files] ON [files].[name] = [add_files].[name] &quot; _
&amp; &quot;SET [files].[checksum] = [add_files].[checksum], [files].[date] = [add_files].[date];&quot;

Exit Function

'error handler if database file isn't found
ErrorHandler:
If Err.Number = 3021 Then
MsgBox Prompt:=&quot;The &quot; &amp; strDb &amp; &quot; file can't be found &quot;, _
Title:=&quot;Macro is halting...&quot;
End
Else
Dim Msg As String
Msg = &quot;Unexpected error #&quot; &amp; Str(Err.Number) &amp; &quot; occurred: &quot; _
&amp; Err.description
' Display message box with Stop sign icon and OK button.
MsgBox Prompt:=Msg, Buttons:=vbCritical
End
End If
End Function


HTH

Mary