Dick, I finally understand where you're coming from on this. I do have a quick question for you:
In your initial question you stated that you made a cf script convert the access DB into ms sql or oracle. How did you convert that info back to the access DB file? I haven't used access in a long time and never heard of that. I could actually use that for one client right now. Brett At 06:43 PM 10/9/02 -0700, you wrote: >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 > >>>>>> > >>>>>> > >>>>>> > >>>>>> > >>>>>> > >>>>>> > >>>>> > >>>> > >>> > >> > > > ______________________________________________________________________ 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 ------------------------------------------------------------------------------ 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.
