Retrieving Long Text into Access Database

Hi,

I am using an Access Database to host my Filemaker data. I found out that Access does not except Varchar datatype in text form, so I created a memo field. I got no problem inserting text into the memo but hit error when I was retrieving them. My question is, how to use SQL Statement to retrieve long text values from a memo field of Access?

Thanx.
LVL 10
adriankohwsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

billmercerCommented:
A simple select statement should work. What is the error you get when you try to retrieve the results?
0
adriankohwsAuthor Commented:
I can't. I am using a plug-in, the destination field to store the result shown error without details. When I use Select x,xxx,xxx, MEMO as Expr1 from tablename and export it to a text file. When I import data from this file, all data exist excluding those with memo datatype.
0
billmercerCommented:
Some more information would be helpful. What version of FileMaker is it? What plugin are you using? If you use a plug-in function, it should return a value. What value is it showing?
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

lesouefCommented:
Does it work using the regular import/ODBC ? ODBC drivers always issue en error number while plug-ins...
0
adriankohwsAuthor Commented:
I have simulated by using importing feature and it works perfectly. I think you guys are correct, damn, I paid for an unworkable plug in.

The error just show error with a full stop....
0
lesouefCommented:
which plug-in is that? PDM? fontbase?
0
lesouefCommented:
As a test, maybe you could also setup FM as the ODBC server, and have Access writing data in it this way?
Also you could use an intermediate database, like loading data from Access to mySQL or µsoft SQL, then have FM to read them back.
0
billmercerCommented:
Another option would be to set up an automated import/export process that will export the records from one and import them into the other on a scheduled basis. That's actually quite easy to do. Also, if you have an older version of Filemaker, it will be much faster to do that than it would be to use ODBC. FileMaker's ODBC driver for versions 6 and earlier is notoriously slow. If you have version 7, that's not an issue.

0
adriankohwsAuthor Commented:
Hi Guys,

Lesouef,

Yes, it is the Frontbase. Anyone used the PDM before, I tried but the select statement takes a long time which I don't understand, like 5 seconds. It is not the FileMaker script as I have put a pause script between importing and the Select Statement, the select statement regardless of the record set is huge or just one two rows of records, is extremely slow.

Thanks for all your recommendations however, schedule importing and exporting is not an option to me. It is basically something like a CRM system which upon one user updates the database, immediately, the other users should be able to get the changes.

The funny thing is, this plug in when issuing select statement to a table when a memo field exist in the table will crash your FMP.

I have decided to shift to using MSDE when this program is to be distributed but now, since the program is at the beginning stage whereby my company is going to use it ourselves, I will live with Access first. I did that funny thing to solve this problem. I used a text based field to store long text. I created like for example a field named "Pending_Remarks", I will have "Pending_Remarks_x" where x = 1,2,3,4 some kind of this sort.

I make my application when saving extract the long text into many 250 characters and insert into the Pending_Remarks_x.

That's the only workaround at the present moment to stop me from delaying the development.

Adrian
0
adriankohwsAuthor Commented:
Actually. I would love to have FileMaker Developer 7.0. It's not just about the speed but the new features within. Especially this simple thing like allow or disallow entry of a field based on whether is Browse or Find mode and also allow to Hit ENTER or RETURN to the next field.

However, my boss not gonna invest in it. I need a developer edition as to compile a written application so that users do not have to purchase a licences for FMP. I sounds silly if we want to sell something which ended up FMP earn more than us.

Adrian
0
billmercerCommented:
My experience with memo fields is they are problematic for many other programs, not just FileMaker.

Just out of curiosity, why don't you use FileMaker itself to hold the data?

Switching to something other than Access would be a BIG improvement, not only for longer text fields, but also in terms of stability and avoiding corrupted data. Even better than using MSDE would be to use something like Firebird or MySQL. That way you could offer your program to Macintosh users as well.

0
lesouefCommented:
And I can witness that accessing mySQL with FM through the mySQL ODBC driver is incredibly fast, faster than accessing a filemaker server itself. And costs nothing.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
adriankohwsAuthor Commented:
Oh yes, I heard that as well. MYSQL is quite fast, just not experienced in it and having doubts, didn't know about the stability and things like that. MSDE as mentioned by MS is of the same structure as MSSQL Server 2000 which I feel quite comfortable.

Lesouef,

How is Mysql? Stable?

For Mr.Billmercer question, if I can live with just FileMaker, I would not go through all these hardships haha. I would love to my friend, I have been using it for the past 7 years since version 4.0. The problem is when you want to distribute in a network environment, you need to purchase FileMaker for all clients to access.

Of if you are talking about purchasing a FileMaker Pro Server, the cost is so high that I can get a good performance RDBMS. Macintosh is not a concern here. I am in Singapore and users here do not really use Mac unless for those dealing with fine graphics like designers and printing companies etc. And moreover, even for that kind of nature, they will purchase PCs for their normal operations as well as for the admin etc.

You are extremely right for the memo field, really problematic field type. MS DB other than MSSQL Server coudln't make it, like Access, Foxpro etc. For Foxpro for instance, the padding problem makes you crazy. For Access is not only the memo field problem, when you insert text contents like = "It's a fine day, You've to rest." , you will hit error. They considered that as special characters, I almost faint checking out my SQL coding but later found out that it's the text contents that is stopping it to be inserted. Urrrrg..

Anyway, I will definitely switch, MSDE or MYSQL, that's it and full stop.

Thanks guys.

Adrian
0
lesouefCommented:
To me, mySQL is stable, never had any crash, but my experience is not very relevant: what I do with it is rather light, only 10 bases/70 tables all in all, not many records, nothing above 10000 rec.

I am using it under w2000 server. You can also use a linux version which may be of some interest as well.
It seems that with a lot of memory (we have 2GB) MSSQL is faster, but the diff is between fast and fast+. For the price difference, and multi-os capability, I would go to mySQL rather than microsoft. MSDE: no experience.
0
billmercerCommented:
MySQL is one of the fastest SQL engines around, and has proven to be pretty stable, but it doesn't have all the features of other SQL engines. For example, you can't do nested queries, views, triggers, etc. If you don't need those features, MySQL is a good option. If you do, then consider Firebird.
http://www.ibphoenix.com/
Firebird is an open source SQL engine based on the InterBase engine. It's very fast, very stable, and very easy to set up and use, and supports all the popular features of other engines, views, triggers, stored procedures, etc.

The MSDE is a nice engine, but it has a built-in performance limiter that deliberately slows down the database as more users connect. As long as the number of users is low, this isn't a problem, but if you need to support more than 8-10 connections, you should probably avoid MSDE. Also, if you're running the MSDE on a workstation OS instead of a server, you have to worry about connection licenses.
0
adriankohwsAuthor Commented:
I got the message. Thanks so much for the lots of information. So I should avoid MSDE. Most of my application will probably be used by more than 10 users. I might want to try MySQL, but what you meant by not supporting nested query, something like:

SELECT * FROM CLIENTTRAN.CLIENT_ID WHERE CLIENTTRAN.CLIENT_ID NOT IN(SELECT CLIENT.CLIENT_ID FROM CLIENT)

This query doesn't work?

Thanks.

Adrian Koh
0
billmercerCommented:
Sorry, my information was a bit out of date. As of version 4.1, MySQL now supports subqueries. That's a relatively recent change, and I had not kept up. Version 5 of MySQL will supposedly  provide lots more functionality, including stored procedures, etc, but I have no experience with it.
0
adriankohwsAuthor Commented:
Wow, sounds great, I will get more info for that. Thanks so much. Have a present day today.
0
lesouefCommented:
Are they missing days?? I guess it was pleasant you meant!
I was gonna upgrade mine, but I realized they'd given up "control center" which I did like, so I have done it yet either.
So let's move to 5 (and not to Cleveland)
0
billmercerCommented:
MySQL 5 is still in beta, and is not recommended for production use. It's also a whole new product which usually means there's going to be issues for a while even after release.

Lesouef, the MSDE is really pretty cool. You should take the time to give it a try. It's almost identical to full SQL Server, except for the performance limitations and a few enterprise-type features. It's a great product for a Microsoft developer who wants to use something beefier than an Access mdb file for the back end, but of course it's Microsoft-only, which makes it useless for those of us who need to support other platforms.


0
lesouefCommented:
ok, I'll see.
0
adriankohwsAuthor Commented:
Yes, you are right billmercer, however, the concern is really if the performance drop too much when concurrent of 10 users or above is connected.

I tried Mysql but couldn't log in saying userid or password is incorrect. What's the default for those?
0
lesouefCommented:
root, no passwd
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Components

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.