query data from external 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]

I wouldn't go down that road.

Access is extremely flexible and you should be able to do everyhting from
within it.

The general idea is to link to the Oracle DB and then write a query in
Access to extract the data.
You may need to put it into a local Access table (use a Make-Table query the
first time and then use Delete and Append queries.) Then link to MySQL and
write an Append query to move the data from Access to MySQL.
(You may be able to do it directly from Oracle to MySQL using Access as the
controller but I am not 100% sure on that. I usually only link to a single
external DB at a time so I am not sure if you can link to 2 different ones
and write queries directly from one to the other. Even if you can, you just
save a step in the middle.)

Use ODBC drivers and set up DSN connections (or use connection string that
don't require a DSN.)
You may need Oracle client installed to link to Oracle.

Good luck!

 

[david epsom dot com dot au]

Using VB Script, you would declare an ADO or DAO object,
and use the Execute method of the object to select
data from one table into another table.

Using DAO, you need to have a current database (even
if you don't use it at all): with ADO you need to have
a current connection.


something like this:

Dim objEngine
Dim objWS
Dim objDB

Set objEngine = wscript.CreateObject("DAO.DBEngine.36")
objEngine.DefaultPassword = G_PWD
objEngine.DefaultUser = G_USER
objengine.systemdb = G_SYSDB

Set objWS = objEngine.CreateWorkspace("",g_user,g_pwd)
Set objDB = objWS.OpenDatabase(GDB_SCRIPT)

objDB.Execute "INSERT INTO [ODBC;DSN=fred].[t1] ( f1,f2,f3 ) SELECT * FROM
[ODBC;DSN=martha].[t2]


(david)