Using Access to generate plain text file - get me started please

[Guest]

OK - I'm an experienced C/Unix/SQL programmer. I'm not an experienced
Windows platform developer. I've been maintaining some data in Access. Now
I have a need to use the data in Access to generate a plain text file
(dynamically create SQL to be used to update an Oracle schema based on the
data in Access, to be exact.)

OK - I've found example code to open a record set, and to manipulate the
results, etc. Great - that I understand. What I don't find is how to run
the code (forms? buttons?) and how to output the formated text (I don't want
a 'report' I want a file.)

Can you get me over the hump? Point me at some sample code?

Thanks a bunch!

 

[Guest]

So, I found the text I/O info here:
http://www.erlandsendata.no/english/index.php?d=envbafileaccesssequential

Of course, it's now occured to me that I could build my Alter Table
statements, then connect directly to the Oracle database, execute it, move on
to the next, and so on, for all 230 tables. Now wouldn't that be cool.

I still need to figure out how to start the code running. Click on Module,
build a form with a button on it - link the code to an 'On Click' action,
then - run the application and click?

Seems so silly to bulid that 'event - callback' logic to execute a single
stream of execution that just need to run a few time, until I'm sure it's
right. Any better way?

Thanks again.

 

[Guest]

Send me an email to (e-mail address removed) if you still need help creating
a text file from your Access data.

 

[We'll be watching you...]

We're watching you...

 

[Ed Warren]

At Least, I'll actually TRY to help!!

You're way ahead of me with SQL etc. But my approach would be use the
upsizing wizard in Access and upsize the tables into SQL Server, or if not
available use the MSDE (Desktop version of SQL server). Once I had my data
there either SQL server DTS (data transport service) or ORACLE should have
utilites to import/export to each other. Another approach would be to hook
the ORACLE database into Access via ODBC. There may be utilities that do
what you want directly, I have to leave that to someone much more
knowledgeable.

Best of luck

Ed Warren

 

[John Nurick]

I still need to figure out how to start the code running. Click on Module,
build a form with a button on it - link the code to an 'On Click' action,
then - run the application and click?

Seems so silly to bulid that 'event - callback' logic to execute a single
stream of execution that just need to run a few time, until I'm sure it's
right. Any better way?

That's one of the standard ways of doing it. New form, button on the
form, Click event procedure for the button.

But if you're a Unix whiz you may find it easier to work in a familiar
scripting language and write to the DAO or ADODB library (e.g. using
Perl and Win32::OLE). Using DAO you can open recordsets and iterate
through their contents to build the SQL script; or using ADODB you can
(I'm pretty sure though I've never had cause to try) create an ODBC
connection to your Oracle database and execute the SQL that way.