Corrupt database

[logineimer]

Hello,

have you heard of any cases where a database gets corrupted after using UCanAccess? We recently updated from 3.0.6 to 4.0.2 and our customers report broken databases now. The use case is inserting a new table with logging information each time a process is executed to present it to the user.

Greetings and thanks!

[Gord Thompson]

Is it possible that you are referring to this issue? If not, then please provide more information on how to reproduce the issue.

[logineimer]

Hello Gord,

thanks for the link! I'm not sure if this is the problem because my DDL statement does not contain an ID definition. According to the changelog, UCanAccess 3.0.6 supported basic DDL.

Well, I hate this silly question: Is it absolutely necessary to define a primary key? If we don't, does UCanAccess do?

CREATE TABLE [tbl_2017_08_23__15_48_53_270_test] 
  ( 
     [out_sql_mod7_feld3] VARCHAR(255), 
     [out_sql_mod7_feld2] VARCHAR(255), 
     [out_sql_mod7_id]    VARCHAR(255), 
     [out_sql_mod7_feld1] VARCHAR(255), 
     [ergebnis_mod7]      VARCHAR(255), 
     [zeitpunkt_mod7]     VARCHAR(255), 
     [meldungen_mod7]     MEMO 
  ) 

Edit: The statement never changed over the past months.

This ist the error when opening the database in Access. It says that the rows cannot be read and there is no read permission on MSysAccessObjects.

[Gord Thompson]

I just tried this on a new empty database using 4.0.2 and it worked fine for me

sql = 
        "CREATE TABLE [tbl_2017_08_23__15_48_53_270_test] " + 
                "  ( " + 
                "     [out_sql_mod7_feld3] VARCHAR(255), " + 
                "     [out_sql_mod7_feld2] VARCHAR(255), " + 
                "     [out_sql_mod7_id]    VARCHAR(255), " + 
                "     [out_sql_mod7_feld1] VARCHAR(255), " + 
                "     [ergebnis_mod7]      VARCHAR(255), " + 
                "     [zeitpunkt_mod7]     VARCHAR(255), " + 
                "     [meldungen_mod7]     MEMO " + 
                "  ) ";
try (Statement st = conn.createStatement()) {
    st.execute(sql);
    st.execute("INSERT INTO [tbl_2017_08_23__15_48_53_270_test] ([out_sql_mod7_feld3]) VALUES ('foo')");
}

 
If you take one of the corrupted databases and do a "Compact and Repair Database" operation in Access does that fix it?

Can you provide an empty (or sanitized) sample database that can reproduce the issue under 4.0.2?

re: primary key - No, it is not absolutely necessary for a table to have a primary key defined.

[logineimer]

Unfortunately, I cannot provide the broken file because it's full of customer's data. When trying to do a "Compact and Repair Database" operation, I see the same error dialog.

However, there seem to be some issues referring to the SO question. Can you guarantee that those won't happen when only reading from the database?

[Marco Amadei]

Yes we can, UCanAccess doesn't change anything in read-only mode. And a sanitized database without data would be very useful (we only need metadata, not data).

[logineimer]

I'll try my best to open the database so i can send it to you! Here is a screenshot of an error which occurs while opening the database with UCanAccess:

[logineimer]

Well, I tried to open the database with permanent shift key down, i tried to do a compact and repair but nothing let me open this fu****ng database. Creating a fresh database and importing the data from the broken one didn't help neither.

[Gord Thompson]

Are you able to reproduce the issue by

• taking a copy of an existing "good" database, (i.e., one that you can open in Access),
• running the CREATE TABLE against it,
• and ending up with a "bad" database?

If so, then that copy of the "good" database (just structure, no data) would be the most useful for diagnostic purposes.