I'd be inclined to do this by setting up a Windows scheduled task that
launches a script that (1) uses DAO to execute a query that exports the
data to a text file and (2) either sends the file to the mainframe or
puts in a folder where the mainframe can find it.
The sample VBS and Perl scripts below show the general approach to (1);
(2) depends on your client's setup, and their IT people should be able
to tell you what's needed.
I have been writing in Access for quite awhile now. My current client wishes
to take some data from one of my databases and move it to a mainframe on a
weekly basis. Does anybody have any ideas out there about how to accomplish
this or maybe can suggest a source of information? Thanks.
'Sample VBScript to export data from an MDB database to a CSV file
'without opening Access
'Modify DB_NAME, QRY_NAME, DATA_DESTINATION as required
Option Explicit
Dim oJet 'As DAO.DBEngine
Dim oDB 'As DAO.Database
Dim oTDef 'As DAO.TableDef
Dim strSQL 'As String
Const DB_NAME = "C:\Temp\Test 2003.mdb"
Const QRY_NAME = "qryTest" 'Query or table to export
'Next line specifies the folder and file for export.
'This example specifies C:\Temp\MyFile.txt
Const DATA_DESTINATION = "[Text;HDR=Yes;Database=C:\Temp\;].MyFile#csv"
Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase(DB_NAME)
strSQL = "SELECT * INTO " & DATA_DESTINATION _
& " FROM " & QRY_NAME & ";"
wscript.echo strSQL
oDB.Execute strSQL, 128 '128 = dbFailOnError
oDB.Close
'END
#Perl
use strict;
use Win32::OLE;
my $Jet; #DAO.DatabaseEngine
my $DB ; #DAO.Database
my $SQL = "SELECT * INTO [Text;HDR=Yes;Database=C:\\Temp\\;].MyFile#csv
FROM MyTable;";
$Jet = Win32::OLE->CreateObject('DAO.DBEngine.36')
or die "Can't create Jet database engine.";
$DB = $Jet->OpenDatabase('C:\\Temp1\\Test 2003.mdb');
$DB->Execute($SQLquery, 128); #128=DBFailOnError
$DB->Close;
#END
