S
Scott Cooper
Posted this in .externaldata too. Thought I get more help here...
First off, many thanks to John Nurick, Ken Snell, and Joe Fallon. I am
a complete newbie to VBA and was able to piece together enough info
from their posts to do exactly what I wanted.
Now, that I have something working, I need some help taking it to the
next level. Here's the backgroud:
I use Microsoft Baseline Security Analyzer to scan my Windows domain
and generate text files of the results. I save the files named as the
date on which the scan was run. I am using the code below to import
the scans into a database and archive the files. Now I have the
following questions:
1. Does anyone see anything in the code that could be done better/more
efficiently?
2. I want to store the .mdb file on a remote server, not my
workstation. In doing so, I think when I open the file and run the
code, it will be using the directory paths on my local machine. The
server does not have Access installed. How can I modify the path
strings so that the code knows they are local to where the .mdb file is
located?
3. Is there an easy way to schedule the imports of the text files so I
don't have to open the file and run the code each time I want to import
new files? Maybe through ODBC???
Sorry for the long post. Any help is appreciated.
Public Sub ImportMyFiles()
Dim strFileName As String, strNewName As String, strSQL As String
Dim dbs As DAO.Database
Const strPath As String = "C:\MBSA_Scans\Archive\"
On Error GoTo Err_Code
Set dbs = CurrentDb()
strFileName = Dir(strPath & "*.txt")
'Ensure that no records exist in Temp table
strSQL = "DELETE * FROM tblTemp;"
dbs.Execute strSQL, dbFailOnError
Do While strFileName <> ""
strNewName = Left(strFileName, Len(strFileName) - 4)
'Import text from first file into Temp table
DoCmd.TransferText acImport, "Current_Scan Link
Specification", "tblTemp", strPath & strFileName, True
'Use name of file to update the ScanDate field
strSQL = "UPDATE tblTemp SET tblTemp.ScanDate = " & "'" &
strNewName & "'" & ";"
dbs.Execute strSQL, dbFailOnError
'Insert records from Temp table into Archive table
strSQL = "INSERT INTO tblScanArchive ( ScanDate, [Machine
Name], Product, Bulletin, [Q Number], Reason, Status ) " & _
"SELECT ScanDate, [Machine Name], Product,
Bulletin, [Q Number], Reason, Status " & _
"FROM tblTemp;"
dbs.Execute strSQL, dbFailOnError
'Clear Temp table in preparation for next file
strSQL = "DELETE * FROM tblTemp;"
dbs.Execute strSQL, dbFailOnError
'Copy file and remove so it won't be imported on next run
FileCopy strPath & strFileName, strPath & "Imported\" &
strFileName
Kill strPath & strFileName
strFileName = Dir()
Loop
Exit_Code:
On Error Resume Next
dbs.Close
Set dbs = Nothing
Exit Sub
First off, many thanks to John Nurick, Ken Snell, and Joe Fallon. I am
a complete newbie to VBA and was able to piece together enough info
from their posts to do exactly what I wanted.
Now, that I have something working, I need some help taking it to the
next level. Here's the backgroud:
I use Microsoft Baseline Security Analyzer to scan my Windows domain
and generate text files of the results. I save the files named as the
date on which the scan was run. I am using the code below to import
the scans into a database and archive the files. Now I have the
following questions:
1. Does anyone see anything in the code that could be done better/more
efficiently?
2. I want to store the .mdb file on a remote server, not my
workstation. In doing so, I think when I open the file and run the
code, it will be using the directory paths on my local machine. The
server does not have Access installed. How can I modify the path
strings so that the code knows they are local to where the .mdb file is
located?
3. Is there an easy way to schedule the imports of the text files so I
don't have to open the file and run the code each time I want to import
new files? Maybe through ODBC???
Sorry for the long post. Any help is appreciated.
Public Sub ImportMyFiles()
Dim strFileName As String, strNewName As String, strSQL As String
Dim dbs As DAO.Database
Const strPath As String = "C:\MBSA_Scans\Archive\"
On Error GoTo Err_Code
Set dbs = CurrentDb()
strFileName = Dir(strPath & "*.txt")
'Ensure that no records exist in Temp table
strSQL = "DELETE * FROM tblTemp;"
dbs.Execute strSQL, dbFailOnError
Do While strFileName <> ""
strNewName = Left(strFileName, Len(strFileName) - 4)
'Import text from first file into Temp table
DoCmd.TransferText acImport, "Current_Scan Link
Specification", "tblTemp", strPath & strFileName, True
'Use name of file to update the ScanDate field
strSQL = "UPDATE tblTemp SET tblTemp.ScanDate = " & "'" &
strNewName & "'" & ";"
dbs.Execute strSQL, dbFailOnError
'Insert records from Temp table into Archive table
strSQL = "INSERT INTO tblScanArchive ( ScanDate, [Machine
Name], Product, Bulletin, [Q Number], Reason, Status ) " & _
"SELECT ScanDate, [Machine Name], Product,
Bulletin, [Q Number], Reason, Status " & _
"FROM tblTemp;"
dbs.Execute strSQL, dbFailOnError
'Clear Temp table in preparation for next file
strSQL = "DELETE * FROM tblTemp;"
dbs.Execute strSQL, dbFailOnError
'Copy file and remove so it won't be imported on next run
FileCopy strPath & strFileName, strPath & "Imported\" &
strFileName
Kill strPath & strFileName
strFileName = Dir()
Loop
Exit_Code:
On Error Resume Next
dbs.Close
Set dbs = Nothing
Exit Sub