query data from acces tables from shell

[The Legend]

Hello,

i am totally new on access but know some of oracle and have the next
challenge to deal with if its possible. I need to get some column data over
to another machine and put this into a mysql database.

So if i am right i need to activate a script by dos or unix shell that
queries some columns from 3 access 2000 tables used by a application.

i know how to do this on unix/oralce/sql
when activating the shell (*.sh)it calls a *.sql that queries the data from
a oracle databe table(s) into a spool file and saves it in the therefore
choosen directory
like with command spool /users/home/sql.lst
spool off
and a ftp shell could transport this file to any particular
part/machine/place where it can be imported again by any activated
shell/*.sql


Is this possible to do on access by a external script or scripts run by a
external source and how is it done technically ?

be very gratefull with any help or links

thanks in adv.

Ed

 

[Joe Fallon]

Answered in conversion newsgroup.
Please don't post separate messages to different groups.
Include all appropriate groups in a single message.
That way responses get sent out to all of them too.

 

[John Nurick]

Hi Legend,

If this is a one-off task, the simplest thing is to open the database
with Access and work with the GUI. Create a query that returns the data
- there's a SQL view in the query designer - and then use File|Export to
save the columns to a textfile which can be ftp'd to where you need it.

If it's a production task to run from a script, it's usually simpler not
to use Access itself. Instead use one of the Windows data libraries:
e.g. for an mdb file use the DAO library. This can't be done direct from
the Windows shell, you have to use a OLE-compatible scripting language
(e.g. VBScript, or Perl with Win32::OLE). VBScript air code follows:

Dim oEngine ' DAO.DBEngine
Dim dbD ' DAO.Database
Dim rsR ' DAO.Recordset
Dim fldF ' DAO.Field


Set oEngine = CreateObject("DAO.DBEngine")
Set dbD = oEngine.OpenDatabase("D:\Folder\File.mdb")
Set rsR = dbD.OpenRecordset("Table1")

Do Until rsR.EOF
For Each fldF In rsR.Fields
fldF.Name, CStr(Nz(fldF.Value, "Null"))
'do stuff with field
Next
rsR.MoveNext
Loop

Set fldF = Nothing
rsR.Close
Set rsR = Nothing
dbD.Close
Set dbD = Nothing
Set oEngine = Nothing