Re: Importing text, etc. on remote .mdb file
- From: John Nurick <j.mapSoN.nurick@xxxxxxxxxxxxxx>
- Date: Fri, 29 Apr 2005 07:27:06 +0100
Hi Scott,
On 28 Apr 2005 09:36:02 -0700, "Scott Cooper" <imscoop22@xxxxxxxxx>
wrote:
>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
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
- References:
- Importing text, etc. on remote .mdb file
- From: Scott Cooper
- Importing text, etc. on remote .mdb file
- Prev by Date: Refreshing Linked Tables Affects Performance
- Next by Date: Re: How do I convert a file from mdb. to doc.?
- Previous by thread: Importing text, etc. on remote .mdb file
- Next by thread: How do I convert a file from mdb. to doc.?
- Index(es):
Relevant Pages
|
