[Solved] Data import from MSAccess

Discuss the database features
Post Reply
JakeRogers
Posts: 7
Joined: Sat Dec 17, 2022 1:21 am

[Solved] Data import from MSAccess

Post by JakeRogers »

I'm really trying hard to get into OOBase, from MSAccess2013. Please see attached pic for error. I'm able to connect to the AccessDB. I successfully created new OOdb and have copied over 1 table but continue to get error on 2nd table, which has 3663 records. Problem might be the data type "long text" from Access, which has NO defined length. I defined VarChar length as 200 and 500 but it made no difference. Other fields are "short text" and 50, which is same as OO. HELP please.
Update: Oddly it did copy over 13 records???
Attachments
OOErr124.jpg
OOErr124.jpg (74.48 KiB) Viewed 4411 times
Last edited by MrProgrammer on Wed Dec 28, 2022 4:37 am, edited 2 times in total.
Reason: Tagged ✓ [Solved] -- MrProgrammer, forum moderator
Jake, OpenOffice 4.1.13, Win10
JakeRogers
Posts: 7
Joined: Sat Dec 17, 2022 1:21 am

Re: Data import from MSAccess

Post by JakeRogers »

Update: I was finally able to get all 3667 records into the OOdb, minus a single field that was apparently the source of the problem. It is a "long text" data type that apparently is NOT compatible with OOBase. Not sure where to go from here since this is too much data to input manually. I see from posts that MANY others have had this problem and I'm surprised it still persists. I don't mean to be critical of the many who have devoted their time and talent toward development of this alternative to MSOffice. Like many my license was revoked when my motherboard failed and I'm now completely locked out from accessing my data. I would really like to get MS's hook out of me once and for all. Thanks again for any advise.
Jake, OpenOffice 4.1.13, Win10
User avatar
Sliderule
Volunteer
Posts: 1290
Joined: Thu Nov 29, 2007 9:46 am

Re: Data import from MSAccess

Post by Sliderule »

If I understood you correctly, you have an Access database, and, can attach to it with OpenOffice OR LibreOffice Base.

And, you need to know the maximum length ( number of characters ) in a specific column in Access so you can define a column length for a table you are defining within Base.

Please follow these steps, so you will know the MAXIMUM length of text characters.
  1. Start your OpenOffice / LibreOffice database file ( *.odb
  2. On the left, click on Queries
  3. Under Tasks, click on Create Query in SQL View...
  4. Copy and Paste the Query below:

    Code: Select all

    SELECT 
       MAX( LEN(`MY_ACCESS_COLUMN`) ) as `MAX_LENGTH_OF_COLUMN`
    FROM `MY_TABLE`
    
  5. Critical step, Either:
    1. Click on the icon SQL with a check mark
    2. From the Menu: Edit -> Run SQL command directly
  6. Change . . . the name of the column, that I used as `MY_ACCESS_COLUMN` in your Access database to the name that is defined in your database
  7. Change . . . the name of the table, that I used as `MY_TABLE` in your Access database to the name that is defined in your database
  8. Run the Query, EITHER:
    1. Press F5 key
    2. Press Run Query icon
    3. From the Menu: Edit -> Run Query Directly
Now that you know, how large ( number of characters ) a column is needed, you can use the appropriate VARCHAR( ) number, for example, VARCHAR(1000).

Explanation: The Query will use two Access functions:
  1. LEN which returns the length ( number of characters in each row )
  2. MAX returns the maximum of the returned values for the LEN function
  3. The reason the above Query needs to be executed ( Run In Direct Mode is the OpenOffice / LibreOffice Base Parser will flag the LEN function as illegal, since it does NOT follow the SQL standards, or LENGTH. Therefore, run it directly, withOUT the OpenOffice / LibreOffice Parser, and, it will be sent to the database back-end, in your case, Access directly. :super:
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
JakeRogers
Posts: 7
Joined: Sat Dec 17, 2022 1:21 am

Re: Data import from MSAccess

Post by JakeRogers »

Update: YES, it was in fact 60k characters in a few cases due to users skipping MANY lines and inadvertently including blank spaces... my bad!
Hmmm, this can't be... can it? 63,240 characters??? I guess that could be a max for Access' "Long Text" field type, but I'm certain I never approached this. One would think that OO would simply truncate the data should you exceed the maximum.

Regardless and to reiterate, I can connect to my existing accdb (Access 2007 format) with OO. I can also import my Access table data into OO, IF I exclude this problematic "Long Text" field. All fails on importing when I include this field, as shown in pic of original post.
Attachments
OOErr122622.jpg
OOErr122622.jpg (21.77 KiB) Viewed 4301 times
Last edited by JakeRogers on Wed Dec 28, 2022 3:55 pm, edited 1 time in total.
Jake, OpenOffice 4.1.13, Win10
User avatar
Sliderule
Volunteer
Posts: 1290
Joined: Thu Nov 29, 2007 9:46 am

Re: Data import from MSAccess

Post by Sliderule »

JakeRogers wrote: Hmmm, this can't be... can it? 63,240 characters??? I guess that could be a max for Access' "Long Text" field type, but I'm certain I never approached this.
One possibility is, you have trailing spaces at the end of a field, so that it filled up the LENGTH to 63240 characters.
Therefore . . . try the following Query . . . replacing your column name, and, table name according to your Access database.

Code: Select all

Select 
    MAX( LEN( `Remarks` ) ) as `MAX_LENGTH_REMARKS`,
    MAX( LEN( TRIM(`Remarks`) ) ) as `MAX_LENGTH_TRIM_REMARKS`,
    MAX( LEN( LEFT(`Remarks`, 32000) ) ) as `MAX_LENGTH_LEFT_REMARKS`,
From `Recipes`
Explanation:
  1. The TRIM function will remove any trailing spaces from the column for calculation of LENGTH
  2. The LEFT function will only look at the first 32000 characters, OR, whatever number you replace it with.
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
JakeRogers
Posts: 7
Joined: Sat Dec 17, 2022 1:21 am

Re: Data import from MSAccess

Post by JakeRogers »

Sorry to be so much trouble, but I now get a Syntax error when executing code.
Jake, OpenOffice 4.1.13, Win10
User avatar
Sliderule
Volunteer
Posts: 1290
Joined: Thu Nov 29, 2007 9:46 am

Re: Data import from MSAccess

Post by Sliderule »

JakeRogers wrote:Sorry to be so much trouble, but I now get a Syntax error when executing code.
¿ Did you follow steps 5 AND 8 above ?

IMPORTANT: I made a small mistake in the Query . . . please . . . remove the trailing comma from 4th line . . . the line BEFORE the FROM part.

It should have been:

Code: Select all

Select 
    MAX( LEN( `Remarks` ) ) as `MAX_LENGTH_REMARKS`, 
    MAX( LEN( TRIM(`Remarks`) ) ) as `MAX_LENGTH_TRIM_REMARKS`, 
    MAX( LEN( LEFT(`Remarks`, 32000) ) ) as `MAX_LENGTH_LEFT_REMARKS` 
From `Recipes`
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
JakeRogers
Posts: 7
Joined: Sat Dec 17, 2022 1:21 am

Re: Data import from MSAccess

Post by JakeRogers »

OK, removing the comma did the trick. However, the max trimmed len showed 63,192; which surprisingly was in fact ACCURATE.
Regardless, upon setting the "Remarks" field to a VarChar length of 63192, it did indeed transfer. Many thanks for your patience and help with this. Now to get busy with form and query development.
Last edited by JakeRogers on Wed Dec 28, 2022 3:57 pm, edited 1 time in total.
Jake, OpenOffice 4.1.13, Win10
User avatar
Villeroy
Volunteer
Posts: 31345
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Data import from MSAccess

Post by Villeroy »

Code: Select all

SELECT *, MAX( LEN( `Remarks` ) ) AS `MAX_LENGTH_REMARKS`
FROM `Recipes`
ORDER BY  `MAX_LENGTH_REMARKS` DESC
Shows all records with the largest remarks on top.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Sliderule
Volunteer
Posts: 1290
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Data import from MSAccess

Post by Sliderule »

Villeroy:

Just as an FYI, ( For Your Information ), and, others reading this post, the Query you have written will not work exactly as you have written it . . . but . . . it would need a small change as below, for use by Access with an ODBC connection:

Code: Select all

SELECT *, MAX( LEN( `Remarks` ) ) AS `MAX_LENGTH_REMARKS`
FROM `Recipes`
ORDER BY  MAX( LEN( `Remarks` ) ) DESC
OR to return the columns displayed in as defined ( first column to last ) use:

Code: Select all

SELECT `Recipes`.*, MAX( LEN( `Remarks` ) ) AS `MAX_LENGTH_REMARKS`
FROM `Recipes`
ORDER BY  MAX( LEN( `Remarks` ) ) DESC
The reason, the ORDER BY clause cannot reference a calculated column ( with Access and ODBC connection ) . . . BUT . . . by including it ( calculated column ) again in the ORDER BY clause, it works.

Great idea.

Sliderule
Post Reply