Recent

Author Topic: 'Could not get updatable attribute' when using ODBC to use MS Access  (Read 294 times)

willbprog9933

  • New Member
  • *
  • Posts: 34
    • BrainOut!
Hello friends,

Here is the code I am using to connect to an MS Access (.mdb) database without DSN using ODBConn and the ODBC driver provided by MDB Tools:

Code: Pascal  [Select]
  1. procedure TForm1.Button1Click(Sender: TObject);
  2.  
  3. var
  4.   conn: TODBCConnection;
  5.   trans: TSQLTransaction;
  6.   query: TSQLQuery;
  7.  
  8. begin
  9.   conn := TODBCConnection.Create(nil);
  10.   trans := TSQLTransaction.Create(nil);
  11.   query := TSQLQuery.Create(nil);
  12.  
  13.   // connection
  14.   conn.Driver := 'MDBTools';
  15.   conn.Params.Add('DBQ=/home/will/test_data/office1');
  16.   conn.Connected := True;
  17.   conn.KeepConnection := True;
  18.  
  19.   // transaction
  20.   trans.DataBase := conn;
  21.   trans.Action := caCommit;
  22.   trans.Active := True;
  23.  
  24.   // query
  25.   query.DataBase := conn;
  26.   query.UsePrimaryKeyAsKey := False;
  27.   query.SQL.Text := 'SELECT FirstName, LastName FROM Patients WHERE LastName = ''SMITH''';
  28.   query.Open;
  29.  
  30.   ListBox1.Items.Add(query.FieldByName('LastName').Value + ', ' + query.FieldByName('FirstName').Value);
  31. end;

When I run this code, I get the following error:
Code: Text  [Select]
  1. Could not get updatable attribute for column 1. ODBC error details: LastReturnCode: SQL_ERROR;.

Column 1 is an autoincrementing large integer, which is also the primary key.

I have read other posts on the forums, and people generally say "Use a *real* database" when Access is brought up.  Well, the issue here is that I'm trying to write a converter from Access to Firebird or possibly MariaDB, so I initially need to successfully read Access databases.

Does anyone have guidance on this issue?

Thanks everyone! :D

- Debian Linux 10 - amd64
- Lazarus 2.0.4
- FPC 3.0.4
« Last Edit: October 09, 2019, 04:58:06 am by willbprog9933 »
Blessed and forgiven! :-D

Zvoni

  • Full Member
  • ***
  • Posts: 236
Re: 'Could not get updatable attribute' when using ODBC to use MS Access
« Reply #1 on: October 09, 2019, 09:01:06 am »
Is it just me, or has he forgotten to assign the Transaction-Object to his Query-Object and Transaction-Object to his Conn-Object?
I would have expected something like this
Code: Pascal  [Select]
  1. Connection:=TODBCConnection.Create(nil);
  2. Connection.DatabaseName:=PathToMyDatabase;
  3. Transaction:=TSQLtransaction.Create(Connection);
  4. Connection.Transaction:=Transaction;
  5. Query:=TSQLQuery.Create(nil);
  6. Query.Database:=Connection;
  7. Query.Transaction:=Transaction;
  8. //Set some Driver-Params
  9. Connection.Open;

On a sidenote:
1) In your SELECT --> Use Parameters, then you don't have to escape the single apostrophs
2) Are you sure that your Last Names in the Database are in UPPER-Case? IIRC SQL is case-sensitive, so SMITH<>Smith
3) After a SQL-Query it's good practice (at least IMO) to first check the count of returned Recordsets (just if Recordcount is >0) BEFORE doing anything with it.
« Last Edit: October 09, 2019, 09:29:59 am by Zvoni »
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircrafts

willbprog9933

  • New Member
  • *
  • Posts: 34
    • BrainOut!
Re: 'Could not get updatable attribute' when using ODBC to use MS Access
« Reply #2 on: October 09, 2019, 04:55:18 pm »
Is it just me, or has he forgotten to assign the Transaction-Object to his Query-Object and Transaction-Object to his Conn-Object?
I would have expected something like this
Code: Pascal  [Select]
  1. Connection:=TODBCConnection.Create(nil);
  2. Connection.DatabaseName:=PathToMyDatabase;
  3. Transaction:=TSQLtransaction.Create(Connection);
  4. Connection.Transaction:=Transaction;
  5. Query:=TSQLQuery.Create(nil);
  6. Query.Database:=Connection;
  7. Query.Transaction:=Transaction;
  8. //Set some Driver-Params
  9. Connection.Open;

On a sidenote:
1) In your SELECT --> Use Parameters, then you don't have to escape the single apostrophs
2) Are you sure that your Last Names in the Database are in UPPER-Case? IIRC SQL is case-sensitive, so SMITH<>Smith
3) After a SQL-Query it's good practice (at least IMO) to first check the count of returned Recordsets (just if Recordcount is >0) BEFORE doing anything with it.

Thanks for that.  I'll check it out and see how it goes.  The code example I provided was just minimal to get the point across so the things noted in your sidenote aren't there.

Thanks again :)
Blessed and forgiven! :-D

willbprog9933

  • New Member
  • *
  • Posts: 34
    • BrainOut!
Re: 'Could not get updatable attribute' when using ODBC to use MS Access
« Reply #3 on: October 09, 2019, 06:33:57 pm »
Is it just me, or has he forgotten to assign the Transaction-Object to his Query-Object and Transaction-Object to his Conn-Object?

I just gave your corrections a try, but I'm still getting the same error message.  :(
Blessed and forgiven! :-D

Zvoni

  • Full Member
  • ***
  • Posts: 236
Re: 'Could not get updatable attribute' when using ODBC to use MS Access
« Reply #4 on: October 10, 2019, 08:10:52 am »
Hmm, looking at the Source-code of odbcconn there seems to be a problem with the "Is updatable?"-Property of your Column 1
Have you checked your Access-DB? Could you provide the Properties of your Column 1 (I'm guessing it's the PrimaryKey, ID or something)
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircrafts

willbprog9933

  • New Member
  • *
  • Posts: 34
    • BrainOut!
Re: 'Could not get updatable attribute' when using ODBC to use MS Access
« Reply #5 on: October 10, 2019, 06:59:50 pm »
Hmm, looking at the Source-code of odbcconn there seems to be a problem with the "Is updatable?"-Property of your Column 1
Have you checked your Access-DB? Could you provide the Properties of your Column 1 (I'm guessing it's the PrimaryKey, ID or something)

Column 1 is an autoincrementing large integer, which is also the primary key.

Thanks for your research and help Zvoni! :)

I had forgotten that I had coded up a Visual Basic 6 app a while ago that converts our customer databases to sqlite3 as well as CSV, so I think I'll probably just go that route instead of banging my head against the MS Access ODBC problem.  It's too bad that MDB Tools and other open-source Access things didn't work right, but that's life, I guess.  Being an open-source programmer myself, I understand the challenges and struggles of maintaining open-source projects.
Blessed and forgiven! :-D