running a .sql script on an access db?

[Guest]

we have an access db/application, & i need to import some data from an oracle
table structure. the table structures are different, so i was hoping to
spool the data from oracle & create scripts to input the data into access -
but I don't see a place to run something like: @ C:\myscript.sql on the
access database.
does anyone know if this is possible? thanks in advance!

 

[Phillip Windell]

First make sure the destination table and script containing the query
"agree" with each other (fieldnames, datatypes, etc). It also needs to be
the right type of query in the script (Select, Insert, Update, ect).

Open the SQL script in Notepad. Do a "Select All" then "Copy"

Create and Open a blank Query in Access and switch it to Design view,...then
SQL View. Paste the script from notepad into it and run it. That's how you
"run" a query script in Access.

Access Querys will respect most of the standard SQL syntax but you may run
into some things to adjust such as Dates. In Access the dates must be
enclosed between the number sign #3/25/2006# but normal SQL Server syntax
doesn't use the #.

 

[Bill Mosca, MS Access MVP]

If you link to the Oracle tables you can run whatever queries you want to
massage the data.

 

[Granny Spitz via AccessMonster.com]

the table structures are different, so i was hoping to
spool the data from oracle & create scripts to input the data into access

the table structures are different, so i was hoping to
spool the data from oracle & create scripts to input the data into access

You can't make the database engines do all the work by spooling the table
structure and data into a script from Oracle because Jet can't run scripts,
at least not the type you're used to. Jet can only run one SQL statement at
a time. You can still use dynamic SQL to spool the DDL script (resulting in
*one* SQL statement). When it's done open the text file and copy/paste it
into a new query (SQL view) and run it to create the table in Access. If the
Access database is connected to the same network as the Oracle server, link
to the Oracle view of the table and append the records from the Oracle schema
to the new table. If it's not connected use dynamic SQL to spool to a CSV
file (with column headers) and you can use a query in Access later to append
from the CSV file into your new table (or use the wizard to import it).