'Could not get updatable attribute' when using ODBC to use MS Access

[willbprog9933]

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!

- Debian Linux 10 - amd64
- Lazarus 2.0.4
- FPC 3.0.4

[Zvoni]

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.

[willbprog9933]

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

[willbprog9933]

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. 

[Zvoni]

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)

[willbprog9933]

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.