MS Access Import and is there a Memo Attribute Type

gmckay

Hey I just wanted to know if:

Is there a way or utility that import MS Access Data Base structures into AWARE and
What Attribute Type can I use that is Similar to Memo or BLOB types that is so prevalent in Desktop Databases

Thanks, this is truly a wonderful product, keep up the great work

Gary 8-)

tford

Gary,

re #1 -- I have an AwareIM application that uses data from Access. The AwareIM server is not located on the server in our office where the Access database resides.

Here are my steps:
1- Access created a series of CSV files & drops them on our local server

2- Automatic FTP process takes the CSV files & drops them on the server running AwareIM.

3- AwareIM has a process with a series of IMPORT actions that refreshes the AwareIM data on a regular basis.

Tom

gmckay

Tford

Wow, thanks I will try that and see what happens

Thanks!
Gary

gmckay

Is their a way to pickup the Table structure, i.e. Attributes

Gary

aware_support

Yes, there is. Aware IM can create business objects automatically from existing database tables. For Access database you will need to find a JDBC driver to enable this functionality (you can find plenty of free ones on the Internet). Please see the User Guide for more details.

tazzmaxx

I have been looking for a JDBC driver for Access. So far, the only ones I find are $80-$150.

Can someone point me in the direction to a free MS Access JDBC driver?

Thanks

aware_support

Go to the Sun's database of JDBC drivers http://developers.sun.com/product/jdbc/drivers

and select MS Access

If you can't find a free driver in this list then none is available probably.

tazzmaxx

Wow...their site is pretty out of date.

Unfortunately...the site links listed that did work have offerings that are $120 and up. That is a little pricey for connecting to an Access database. If anyone has come across a free one please let the forum know as they are not very common in the marketplace (as I have been on about 45 websites with no luck).

Thanks Guys

tazzmaxx

Found one!

No I just need to figure out how to make it work 😃

http://www.infozoom.de/en_jadoZoom.shtml

It is free for developers but it will start a "buy me" prompt after 30 days but it does allow for use without having to pay any money up front.

I'll post how to use this if I get it working (for you Access guys)

gmckay

Thanks, the JBDC Driver work.

Also, I took one of my Clients Access Databases and brought it into MySql and just use AWARE IM Persistence database module under Bizz Object

Gary 8-)

tazzmaxx

Very Cool Gary!

Could you share the details you entered to connect to the MDB file. I tried and came up short.

gmckay

Here is what I did to get MS Access, MySQL working with AWARE IM

It’s easier to work IHMO with MySQL because of the built in AWARE support. This requires converting your Accesses Database(s) into MySQL Tables.
Download and install MdbToMySQL Xp, just Google it, I got mine from http://www.freewarefiles.com/MdbToMySQL-XP_program_9230.html What this Tool does is read your “.MDBs” and creates DDL/SQL scripts for use in MySQL. Note, if you are using Access 2007 you will have to convert to 2003 version of Access
Next run MdbToMySQL, load the Access Database that you want to migrate over to MySQL, select the tables and then save the script.
Next, go into MySQL using the Query Browser and created a Data Base to which you will be creating your Tables in. so it looks something like CREATE SCHEMA IF NOT EXISTS `database name’ .
Exit out of the Query Browser application, then log back in using the Database you just created
Then, loaded the SQL Script that was generated using MdbToMySQL, Run the script; make sure your tables and data are all in place.
Next, get into AWARE IM, added a new Bizz Object. At the bottom of the Bizz Object screen you will see the “Persistence” section, select External Database, then select “Settings”
Select “Add” then input all of the login Credentials for MySQL, select “OK”
At the “Database External Existence” Screen, Input the table name that you created within MySQL, select “Discover Attributes” and Boom the Table Attributes will be populated, hit Ok,

I hope this helps

Gary 8-)

tazzmaxx

Hi Support,

I have been trying to get the jadazoom JDBC driver to work natively for MS Access. The only reason why I would like to do this is that I have several customers that have exisiting MS Access dbs and they do not want to convert to MySQL or any other DB environment.

I would love to be able to connect to their databases but I am having a problem with understanding the proper way to write the Classpath and URL String for the driver to work.

A little background:

I added the izmado.jar file to the following location C:\AwareIM\JDK\lib\izmado.jar
I then updated the "setend.bat in the BIN directory as follows: SET CLASSPATH=%JDK_HOME%\lib\tools.jar;%JDK_HOME%\lib\izmado.jar;%JBOSS_HOME%\server\bas\lib\jboss-j2ee.jar;%JBOSS_HOME%\server\bas\lib\mail.jar;%JBOSS_HOME%\server\bas\lib\activation.jar;%TOMCAT_HOME%\common\lib\servlet-api.jar;%TOMCAT_HOME%\common\lib\jsp-api.jar;%JBOSS_HOME%\client\jnp-client.jar;%JBOSS_HOME%\client\jnet.jar;%JBOSS_HOME%\client\jboss-common-client.jar;%JBOSS_HOME%\client\jbossmq-client.jar;%JBOSS_HOME%\client\concurrent.jar;%AWARE_HOME%\openAdaptor.jar;%AWARE_HOME%\mysql-connector-java-3.1.12-bin.jar;%AWARE_HOME%\xerces.jar;%AWARE_HOME%\commons-digester.jar;%AWARE_HOME%\commons-collections.jar;%AWARE_HOME%\commons-beanutils.jar;%AWARE_HOME%\jasperreports-0.5.0.jar;%AWARE_HOME%\jgraph.jar;%AWARE_HOME%\jacob.jar;%AWARE_HOME%\itext-0.96.jar;%AWARE_HOME%\log4j-1.2.8.jar;%AWARE_HOME%\regexp.jar;%AWARE_HOME%\jakarta-poi-1.5.1.jar;%AWARE_HOME%\axis.jar;%AWARE_HOME%\commons-discovery.jar;%AWARE_HOME%\commons-logging.jar;%AWARE_HOME%\jaxrpc.jar;%AWARE_HOME%\saaj.jar;%AWARE_HOME%\wsdl4j.jar;%AWARE_HOME%\derby.jar;%AWARE_HOME%\jhall.jar;%AWARE_HOME%\looks-1.3.1.jar;%AWARE_HOME%\jtds-1.0.3.jar;%AWARE_HOME%\jfreechart-1.0.2.jar;%AWARE_HOME%\jcommon-1.0.5.jar;%AWARE_HOME%\ldap.jar;%AWARE_HOME%\ojdbc14.jar;..\docs\help.jar;%AWARE_HOME%\awareim.jar;

I then used izmado.jar as the classname and jdbc:izmado😛rovider=Microsoft.Jet.OleDB.4.0;data source=c:\test\Reports_BE.mdb; as the connection URL

I am getting an error message "Aware IM server encountered the following error .Unable to initialise the JDBC driver. Make sure that the driver's class name is correctly specified"

I'm sure that I have done many things wrong but I am at a loss of how to proceed. Im sure that this JDBC driver will be a huge benefit to many users (especially me :o) if it works. I would love to use your Existing External persistence feature but I can't seem to make it work with what I have.

Any assistance would be greatly appreciated.

aware_support

I then used izmado.jar as the classname and jdbc:izmado😛rovider=Microsoft.Jet.OleDB.4.0;data source=c:\test\Reports_BE.mdb; as the connection URL

This is wrong. You cannot use the name of the jar as the class name. You need to specify the fully classified Java name of the class WITHIN the jar that implements the driver, for example:

com.driverpackage.DriverImplementation

Then you need to specify the connection URL

The documentation of your driver should clearly specify how to do this.

tazzmaxx

Hi Support,

I have reviewed their documentation and I can find nothing that refers to a fully qualified java name.

I will say that I am very new to Java and most of this does not make a lot of sense to me.

I am not sure where to go at this point. If a need to ask the JadaZoom company for what you need how exactly should I phrase the question?

Thanks

aware_support

This should definitely be in the documentation of your driver. If you want to phrase the question ask this:

"What should be the fully qualified Java name of the driver's class that my vendor wants specified as part of the JDBC URL?"

They will know what it means.

rocketman

Hi guys, I'm a newbie - just checking out the trial version. I had the same need, to convert an access mdb file.

The really PAINLESS way to convert is to use MYSQL's own Migration toolkit to recreate the whole schema with or without data in MYSQL as an intermediate step. Then use awareIM to create a database external persistent BO, acquire the attributes, Change the BO to a native Aware IM BO, then import the data.

5 minutes - job done.

tford

Thanks for sharing this. Could you provide link to the toolkit you used?

Tom

tazzmaxx

The really PAINLESS way to convert is to use MYSQL's own Migration toolkit to recreate the whole schema with or without data in MYSQL as an intermediate step. Then use awareIM to create a database external persistent BO, acquire the attributes, Change the BO to a native Aware IM BO, then import the data.

Our needs were definitely different as I did not want to convert an Access DB...I wanted to connect to an existing one. So far...I don't think anyone has been able to do it with a free ODBC driver...if I am wrong, PLEASE CORRECT ME. Unfortunately, my situation was one where the existing Access database had a front-end that could not be moved from Access. I say unfortunately because I had an opportunity to make something pretty cool in Aware for a client but they were not willing to pay for my time, the licensing and the cost for a JDBC driver (the cheapest I found was $150 that was any good) or for any support tickets that I would have been forced to purchase since I was apparently an annoyance and too stupid to figure out how to make the connection with the spectacular documentation in the Aware manual.

I respectfully disagree with it being the "PAINLESS" way to go in all situations as there are many existing applications that cannot be moved from the current DB platform to one that is convenient for Aware's supported ODBC...and yes...I know any DB with an ODBC driver is supported...AT A PRICE.[/url]

rocketman

Hi Tford,

URL is http://dev.mysql.com/downloads/gui-tools/5.0.html

Did u design the access db initially, if not watch out for some no-no's like spaces in field name, als the - which Aware doesn't like. You might need to do some massaging.

sorry my idea didn't help you Taazz - If I stumble across anything I'll let u know