'Could not get updatable attribute' when using ODBC to use MS Access
lazarus / 16-Databases

----------------------------------------------------------------------
willbprog9933 · 2019-10-09 02:13

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button1Click(Sender: TObject); var  conn: TODBCConnection; 
trans: TSQLTransaction;  query: TSQLQuery; begin  conn :=
TODBCConnection.Create(nil);  trans := TSQLTransaction.Create(nil); 
query := TSQLQuery.Create(nil);   // connection  conn.Driver :=
'MDBTools';  conn.Params.Add('DBQ=/home/will/test_data/office1'); 
conn.Connected := True;  conn.KeepConnection := True;   //
transaction  trans.DataBase := conn;  trans.Action := caCommit; 
trans.Active := True;   // query  query.DataBase := conn; 
query.UsePrimaryKeyAsKey := False;  query.SQL.Text := 'SELECT
FirstName, LastName FROM Patients WHERE LastName = ''SMITH'''; 
query.Open;   ListBox1.Items.Add(query.FieldByName('LastName').Value +
', ' + query.FieldByName('FirstName').Value);end;
When I run this code, I get the following error:
Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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 · 2019-10-09 07:01

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Connection:
=TODBCConnection.Create(nil);Connection.DatabaseName:=PathToMyDatabase
;Transaction:=TSQLtransaction.Create(Connection);Connection.Transactio
n:=Transaction;Query:=TSQLQuery.Create(nil);Query.Database:=Connection
;Query.Transaction:=Transaction;//Set some Driver-
ParamsConnection.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 · 2019-10-09 14:55

Quote from: Zvoni on October 09, 2019, 09:01:06 amIs 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Connection:
=TODBCConnection.Create(nil);Connection.DatabaseName:=PathToMyDatabase
;Transaction:=TSQLtransaction.Create(Connection);Connection.Transactio
n:=Transaction;Query:=TSQLQuery.Create(nil);Query.Database:=Connection
;Query.Transaction:=Transaction;//Set some Driver-
ParamsConnection.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 · 2019-10-09 16:33

Quote from: Zvoni on October 09, 2019, 09:01:06 amIs 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 · 2019-10-10 06:10

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 · 2019-10-10 16:59

Quote from: Zvoni on October 10, 2019, 08:10:52 amHmm, 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)

Quote from: willbprog9933 on October 09, 2019, 04:13:33 amColumn 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.