Question Memo fields in multi user environment

[exASHacto]

Hi all,

I'm struggling with memo fields in my multi user database (access 2010, split front and backend).

The memo fields are causing corrupt data

Atm I have quite a few tables, here are the main setup:

tbl_project --> tbl_country --> tbl_channel

One project can have many countries connected and each country can have multiple channels connected. I use memo fields so the users can add comments to the different data-levels. These comments/memo fields are causing some records to corrupt <-- suddenly the values are replaced with Chinese characters :banghead:

I have read that it is recommended to create separate tables for each memo field - so instead of 20 corrupt fields it is just one.

Should I add 1 memo-table pr data-level or should I add one for each memo field??? I'm thinking one table for each memo field.

thanks in advance
Anders

 

[jdraw]

Why exactly are you using memo fields?
Could you not have a comment that was text (255 char max) and a Date and some field to identify the related record/record level etc?
Memo fields can be very finicky - as you are witnessing.

 

[exASHacto]

hi jdraw,

The comments average about 500 char, the biggest is about 3k char.
well I'm using memo fields, since I want one comment pr record in my excel report.

When adding a comment, I add the text from a textbox to the memo field including the current time. Admins can edit the memo fields as well.

I have removed the comments fields from my main tables and added new tables, one for each comment field.

 

[jdraw]

500 - #3K chars is a relatively long comment (in my view).
Good luck with your project

 

[exASHacto]

I know, it is crap! But the customer want it that way.

 

[McSwifty]

I don't want to hijack this thred, but why are there issues with memo fields?

I have a db running over a wired network. most records have a comment made in a memo field. there are over 12000 records currently and it is growing daily, every month i run and append/delete query to 'remove' (send to an archive db) old data from 13 months ago. (only last 12 months is valid)

Advice would be nice to hear.

thanks. again sorry for the hijack.

 

[exASHacto]

Pat Hartman - the legend !

Well they can access the database over VPN (which is VERY slow). I suppose some could kill their connection if it stalls. And when leaving for a meeting they dock-off and the wireless are turned on - does that make sense.

A SQL server can be the solution in 6-12 months time - but not right now. Approvals on many levels stalls the process.

Yes the memo fields are updated every day. A new comment is entered in a textbox which add the text to the memo field including windows username and the current time. The comments are specific not general, so they will not be referenced to.

thanks!

 

[exASHacto]

Please surgest a setup for the memo tables... my solution doesn't work.

I have created 9 memo tables. Each with a number column and a memo column. The number column refers to the PK in the "mother" table". This works when I add comments - BUT my query doesn't work now.

My query contains all the fields in the "mother" tables and memo fields from the memo-tables. I have grouped the memo fields by "First". Is this only possible for 1 field?

What to do..

 

[exASHacto]

I solved it by creating a query with the "mother" PK and all the memo fields (group by First). This solved the problem.

 

[exASHacto]

... BUT truncated the fields to 255 characters (((((((