Importing text, etc. on remote .mdb file

[Scott Cooper]

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

 

[John Nurick]

Hi Scott,

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?

See (3)

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?

The paths you specify will relate to the machine on which Access is
running, so C:\ will be the local hard drive. In recent versions of
Access you can use
CurrentProject.Path
to get the folder containing the mdb file containing the code you're
running. You can then use VBA string functions to get from there to the
folder with the import files.

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

The simplest way is to create a macro in the mdb file that calls the
import routine and then closes Access. Then set up a scheduled event (on
a workstation that has Access installed) which launches Access, using
the /x command line switch to pass the name of the macro.

The more efficient and reliable way is to set up a script on the server
which uses the DAO library (or ODBC if you're happier with it) to import
the data. This doesn't require Access to be installed on the server, but
AFAIK it has to be a Windows server.

Here's a VBScript sample, but you can do the same thing in most other
scripting languages:

'Sample VBScript to import data from a textfile into
'a table in an MDB database without opening Access
'Modify strSQL and filenames as required

Option Explicit

Dim oJet 'DAO.DBEngine
Dim oDB 'DAO.Database
Dim strSQL 'String

Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase("D:\Folder\Database.mdb")

strSQL = "SELECT * INTO MyTable FROM " _
& "[Text;HDR=Yes;Database=D:\Folder\;].FileName#txt"
oDB.Execute strSQL

oDB.Close

You can use this technique to run most queries. Perhaps the biggest
difference is that because you're working directly with the Jet database
engine the range of functions you can call in queries is restricted.
Another factor is that you can't use Access import specifications, but
instead have to use a schema.ini file in the same folder as the text
file you're importing. So the script would need to do something like
this:

i) copy the file to be imported into the same folder as schema.ini and
rename it to fit the filename specified in schema.ini

ii) build and run an append query that imports the data direct to the
"permanent" table, adding a calculated ScanDate field as it does so,
e.g. this :

INSERT INTO tblScanArchive
SELECT
'20050429' AS ScanDate, MachineName, Product,
Bulletin, QNumber, Reason, Status
FROM [Text;HDR=Yes;Database=D:\Folder\;].FileName#txt
;

iii) move or rename the original file and delete the copy you just
imported.

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

 

[Scott Cooper]

Thanks John!

I'm sure it will take me some time to disect everything. I'll probably
have a few more questions as I work through it.

Scott