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 > >>>> > >>>> > >>>> > >>>> > >>>> > >>>> > >>> > >> > > > ______________________________________________________________________ Get the mailserver that powers this list at http://www.coolfusion.com ------------------------------------------------------------------------------ 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.
