Brett
I am doing this now with the OpenLink Software package.
http://www.openlinksw.com
I have tested it only on Mac OS X using VirtualPC, but I am told that
it should also work with 'Nix running VMWare or Wine.
I am new to the 'Nix world and was wondering if there was a better way
to do this that I didn't know about.
Here are the specifics of how the OpenLink solution works:
Mac OS X
VirtualPC
--------------------------------
Emulated Windows
--------------------------------
WinNT or XP
Openlink Server (Consists of the Request Broker and ODBC Agents)
--- OpenLink Request Broker (interfaces TCP/IP and ODBC
--- OpenLink ODBC Agent interfaces Request Broker and db Specific ODBC
Driver
MS-Access ODBC Driver
mdb files
You can have MS-Access running or not
-------------------------------
CFMX OS X Native (Linux port)
--- OpenLink JDBC client to talk to OpenLink Request Broke via TCP/IP
--- JDBC drivers for other RDBMSs
The end result is that a CFMX app running on Mac OS X (or Linux/Unix)
has simultaneous use of MS-Access dbs and other JDBC-compliant DBS
So, whether the target DB is Oracle, SQL-Server, DB/2, Sybase,
PostgreSQL, MySQL, etc -- I can exchange data directly between it and
MS-Access.
I have been playing with this OpenLink Trial software for a couple of
days & it seems to work fine (MS-Access to Sybase).
The OpenLink solution costs $500 which is a little pricey -- but not
too bad compared to the other costs:
$100 VirtualPC Emulator
$240 WinXP
$450 MS Office
I talked to the OpenLink people to see if they had anything for
developers (I explained how CFMX developer works). They are looking
into it to see if they have a way of setting a lower price for
developers.
Even at $500, I could quickly recover the cost by eliminating the extra
steps & fiddling with intermediate files or databases.
But, hey, if I can get a lower developer price---
Dick
On Wednesday, October 9, 2002, at 04:42 PM, Brett Frisch wrote:
> Dick,
>
> If you find such a beast let me know. Currently, I don't think a
> application/driver exists for doing this type of conversion. All I've
> seen
> are the ways I described before (and trust me, I've looked). When I
> stopped searching I found out two things:
>
> 1. most of my clients (small to mid-sized businesses) don't use MS
> Access.
> 2. Someone has to frequently upload a MS Access DB to the server right
> (I'm guessing its not you)? Have them upload it through the MyODBC
> driver
> in Access. Email them the driver/have them install it. Walk them
> through
> the username/password/IP address configuration and test. Its a one
> time
> deal. After that, they open the DB in access, click file - export -
> ODBC
> and its done. MyObcc does all the work and your client is happy and
> you
> don't have to worry about a thing :)
>
> quick note: make sure your columns are the same type when doing the
> conversion the first time. Some column types aren't the same between
> the
> two (this is true of other databases as well). Although I'm not an
> expert
> on all Databases, I can say that you have to export most DB's to a
> txt/csv
> file to go to another type of DB.
>
> Good luck with your search. Please report any findings back here.
>
> Brett
>
> At 03:06 PM 10/9/02 -0700, you wrote:
>> Brett
>>
>> I am posting this topic to CF-Talk, also, so pardon the redundancy
>>
>> 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 |
>> +-------------------------------------+
>>
>>
>>
>>
>>
>>
>> On Wednesday, October 9, 2002, at 01:11 PM, Brett Frisch wrote:
>>
>>> Dick,
>>>
>>> Goes like this:
>>>
>>> If you want to use csv file
>>>
>>> 1. Have MS Access export DB to csv file.
>>> 2. Make a web page that uploads file
>>>
>>> eg:
>>>
>>> <form method="post" action="convertDB.cfm"
>>> enctype="multipart/form-data">
>>> <input type="file" name="your_database">
>>> <input type="submit" value="Upload">
>>> </form>
>>>
>>> Have the convertDB.cfm page do something like this:
>>>
>>> <cffile
>>> action="upload"
>>> filefield="yourdatabase"
>>> destination="/your_database_path/"
>>> nameconflict="Overwrite">
>>>
>>> <cfdirectory
>>> directory="/your_database_path/"
>>> name="your_directory"
>>> sort="datelastmodified">
>>>
>>> <cfoutput query="your_directory" maxrows="1">
>>>
>>> <cfhttp method="get"
>>> url="http://www.your_name.com/your_database";
>>> delimiter=" "
>>> textqualifier=""
>>> columns="column1, column2, column3"
>>> name="Your_Query">
>>> </cfhttp>
>>> </cfoutput>
>>>
>>> Then make a query to write/update the records.
>>>
>>> This converts your MS Access database into the mysql database. To
>>> convert
>>> it back to MS Access, write a cfm page that queries your mysql DB and
>>> into
>>> a csv file.
>>>
>>> Brett
>>>
>>> At 10:52 AM 10/9/02 -0700, you wrote:
>>>> Brett
>>>>
>>>> OK, you go through an intermediste CSV file.
>>>>
>>>> Is the MySQL db an intermediate to another db or the end result?
>>>>
>>>> Dick
>>>>
>>>>
>>>> On Wednesday, October 9, 2002, at 09:55 AM, Brett Frisch wrote:
>>>>
>>>>> I have run into a similar situation as well. I no longer do cf and
>>>>> windows
>>>>> unless the customer has to have it. Anyway, a recent client has a
>>>>> retail
>>>>> store with POS (point of sale) software. We export all the info
>>>>> into a
>>>>> comma delimited text file. I built a interface so the customer
>>>>> uploads the
>>>>> file to the server, i then have cfhttp decipher the text file and
>>>>> put
>>>>> it
>>>>> into mysql DB. Your customer could do the same thing with access.
>>>>> Or
>>>>> they
>>>>> could use the MyODBC driver
>>>>> (http://www.mysql.com/products/myodbc/index.html) to do the trick.
>>>>> Of
>>>>> course you may not want your client to have access to the DB on the
>>>>> server
>>>>> that easily. Maybe option #1 would work better? Either way, these
>>>>> are two
>>>>> options that work well.
>>>>>
>>>>> Brett
>>>>>
>>>>>
>>>>> At 09:06 AM 10/9/02 -0700, you wrote:
>>>>>> There is a situation that I encounter & I wonder how others
>>>>>> handle
>>>>>> it
>>>>>>
>>>>>> It is fairly common (almost a given), that a new client will have
>>>>>> some
>>>>>> or all of his data available in offline MS-Access databases.
>>>>>>
>>>>>> It is usually necessary to manipulate this data (validate,
>>>>>> normalize,
>>>>>> etc) to convert it into a usable online database (storing it in a
>>>>>> more
>>>>>> robust RDBMS).
>>>>>>
>>>>>> I have found that CF is an excellent tool for this.
>>>>>>
>>>>>> I normally just upload the MS-Access mdb file to the hosting
>>>>>> service.
>>>>>> Then I write CF programs that convert the data from MS-Access to,
>>>>>> say
>>>>>> SQL-Server, or Oracle.
>>>>>>
>>>>>> No problem, as long as the host service is running on a windows
>>>>>> platform -- MS-Access is usually supported.
>>>>>>
>>>>>> But what happens if the Host uses a non-windows platform?
>>>>>>
>>>>>> How do you read a MS-Access database on, say a Linux Box.
>>>>>>
>>>>>> 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?
>>>>>>
>>>>>> The problem gets compounded if the client requests periodic
>>>>>> creation
>>>>>> of
>>>>>> an offline copy of the online database
>>>>>> -- for offline analysis, etc.
>>>>>>
>>>>>> Is this an issue that any of you run into with any frequency?
>>>>>>
>>>>>> How do you handle it?
>>>>>>
>>>>>> (Of course the easy out is just host on windows platforms that
>>>>>> support
>>>>>> Ms-Access -- but that is totall the wrong reason to make a hosting
>>>>>> decision).
>>>>>>
>>>>>> TIA
>>>>>>
>>>>>> Dick
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>
______________________________________________________________________
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-linux%40houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_linux or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
