On Wednesday, October 9, 2002, at 10:39 AM, Jochem van Dieten wrote:
> Dick Applebaum wrote:
>>
>> I guess you could capture the Access database into SQL-Server,
>> offline, if you had:
>>
>> 1) a windows machine
>> 2) MSDE or SQL-Server
>> 3) MS-Office
>>
>> This extra step gives you a mirror of the client's MS-Access db on
>> SQL-Server -- you still need to manipulate it for validation,
>> normalization, conversion to online, etc.
>>
>> What do you do if you need to go to some other (not SQL-Server)
>> RDBMS,
>> that doesn't have the ability to capture Access databases?
>
> - use a utility to move the data (available for most open source
> databases such as PostgreSQL and MySQL).
Can you point me to a reference?
Is it true that you these utilities create a mirror (MySQL or
PostgreSQL) db as an intermediate to the ultimate target RDBMS?
> - make an ODBC datasource for the RDBMS you are going to use, create
> the
> tables in it and use Access linked tables to move the data over.
As I understand this, you are essentially mirroring the Access tables
on equivalent target RDBMS, and copying over the data. Any validation,
normalization then uses the target RDBMS linked tables as a source.
So the target RDBMS Access linked tables are an intermediate step/copy
of the data.
> - export the data as .csv and read it into the new database
>
> The last to can also move data in the other direction.
>
These both involve intermediate files or databases, correct?
>
>> Is this an issue that any of you run into with any frequency?
>
> All the time. We tell people to use PgAdmin to convert from Access to
> PostgreSQL and if they want access from outside the Uni we allow their
> IP to connect to the database server for their specific database.
>
What I am looking for is a solution where I can write a CF program that:
1) runs on a non-win platform
2) can read/write MS-Access dbs directly (no intermediate files or
dbs)
3) can read/write other RDBMS directly (either win or non-win based)
By doing this, I can:
1) convert MS-Access data directly into the target RDBMS
2) eliminate intermediate steps/filesdatabases.
3) programatically (CF) resolve differences between dbs/datatypes,
etc.
4) programatically (CF) perform validation, restructuring,
normalization, etc. as part of the conversion process
5) do the reverse when needed -- convert the target RDBMS data back
to MS-Access (for offline processing)
6) encapsulate all of the above into program(s) that are complete,
repeatable and don't need manual fiddling.
OK, there is at least one way to do this from CFMX running on a
non-windows platform (shown below).
OpenLink Software has a package that allows you to interface MS-Access
through TCP/IP.
Does anyone see a need or advantage for this sort of capability?
Are there any other tools that allow you to do this?
TIA
Dick
+-------------------------------------+
| Windows (Real or Emulated) |
+-------------------------------------+
| |
| MS-Access databases |
+-------------------------------------+
| ^
V |
+-------------------------------------+
| Non-Windows (Linux/Unix/OS X) |
+-------------------------------------+
| |
| CFMX |
+-------------------------------------+
| ^
V |
+-------------------------------------+
| Windows/Linux/Unix/OS X |
+-------------------------------------+
| |
| Target RDBMS |
+-------------------------------------+
> Jochem
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
