?
Solved

Retrieving Long Text into Access Database

Posted on 2005-03-07
23
Medium Priority
?
1,788 Views
Last Modified: 2013-11-18
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.
0
Comment
Question by:adriankohws
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
  • 7
23 Comments
 
LVL 19

Expert Comment

by:billmercer
ID: 13493334
A simple select statement should work. What is the error you get when you try to retrieve the results?
0
 
LVL 10

Author Comment

by:adriankohws
ID: 13495293
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
 
LVL 19

Expert Comment

by:billmercer
ID: 13501829
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 28

Expert Comment

by:lesouef
ID: 13505041
Does it work using the regular import/ODBC ? ODBC drivers always issue en error number while plug-ins...
0
 
LVL 10

Author Comment

by:adriankohws
ID: 13506271
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
 
LVL 28

Expert Comment

by:lesouef
ID: 13506623
which plug-in is that? PDM? fontbase?
0
 
LVL 28

Expert Comment

by:lesouef
ID: 13524579
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
 
LVL 19

Expert Comment

by:billmercer
ID: 13526942
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
 
LVL 10

Author Comment

by:adriankohws
ID: 13527464
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
 
LVL 10

Author Comment

by:adriankohws
ID: 13527488
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
 
LVL 19

Expert Comment

by:billmercer
ID: 13527576
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
 
LVL 28

Accepted Solution

by:
lesouef earned 300 total points
ID: 13528062
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
 
LVL 10

Author Comment

by:adriankohws
ID: 13529266
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
 
LVL 28

Expert Comment

by:lesouef
ID: 13529616
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
 
LVL 19

Expert Comment

by:billmercer
ID: 13555331
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
 
LVL 10

Author Comment

by:adriankohws
ID: 13557085
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
 
LVL 19

Expert Comment

by:billmercer
ID: 13558176
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
 
LVL 10

Author Comment

by:adriankohws
ID: 13561169
Wow, sounds great, I will get more info for that. Thanks so much. Have a present day today.
0
 
LVL 28

Expert Comment

by:lesouef
ID: 13562623
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
 
LVL 19

Expert Comment

by:billmercer
ID: 13566317
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
 
LVL 28

Expert Comment

by:lesouef
ID: 13568050
ok, I'll see.
0
 
LVL 10

Author Comment

by:adriankohws
ID: 13581279
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
 
LVL 28

Expert Comment

by:lesouef
ID: 13581316
root, no passwd
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
I made this because I wanted to get e-mail with a attached csv file so I'd would be able to import user input into a MS Excel template, but I also wanted to register/save all inputs from each day in a file on the server. 1st - It creates a temp C…
The purpose of this video is to demonstrate how to set up the WordPress backend so that each page automatically generates a Mailchimp signup form in the sidebar. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question