Recent

Author Topic: MSACCES error connection  (Read 6083 times)

eldonfsr

  • Sr. Member
  • ****
  • Posts: 446
MSACCES error connection
« on: October 16, 2021, 05:35:12 am »
HI I follow example of create database access odbc programatic but send  error

Code: Pascal  [Select][+][-]
  1.   OpenDialog1.Filter:='*.mdb ; *.accdb';
  2.    if OpenDialog1.Execute then begin
  3.        if fileExists(OpenDialog1.Filename) then begin
  4.           CFile:= OpenDialog1.FileName;
  5.           ODBCCon.Driver:='Microsoft Access Driver (*.mdb, *.accdb)';
  6.           ODBCCon.Params.Add('DBQ='+cfile );
  7.  //         ODBCCon.Params.Add( 'Locale Identifier=1031');
  8. //          ODBCCon.Params.Add( 'ExtendedAnsiSQL=1');
  9.           ODBCCon.Params.Add( 'CHARSET=utf8');
  10.           ODBCCon.Connected:=true;
  11.           ODBCCon.KeepConnection:=true;
  12.           SQLTOdbc.DataBase := ODBCCon;
  13.           SQLTOdbc.Action:= caCommit;
  14.           SQLTOdbc.Active:=true;
  15.        end;
  16.    end;                    
  17.  

wp

  • Hero Member
  • *****
  • Posts: 11855
Re: MSACCES error connection
« Reply #1 on: October 16, 2021, 11:57:07 am »
Access is an example of software giving perfectly confusing error messages...

Much of the trouble is caused by the 32bit/64bit confusion.

The following code, based on yours, works for me, but only after several attempts:
Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button1Click(Sender: TObject);
  2. begin
  3.   if FileExists(FILE_NAME) then begin
  4.     ODBCConnection1.Driver:='Microsoft Access Driver (*.mdb, *.accdb)';
  5.     ODBCConnection1.Params.Add('DBQ='+ExpandFileName(FILE_NAME));
  6.   //         ODBCCon.Params.Add( 'Locale Identifier=1031');
  7.  //          ODBCCon.Params.Add( 'ExtendedAnsiSQL=1');
  8.    ODBCConnection1.Params.Add( 'CHARSET=utf8');
  9.    ODBCConnection1.Connected:=true;
  10.    ODBCConnection1.KeepConnection:=true;
  11.    SQLTransaction1.DataBase := ODBCConnection1;
  12.    SQLTransaction1.Action:= caCommit;
  13.    SQLTransaction1.Active:=true;
  14.    SQLQuery1.UsePrimaryKeyAsKey := false;
  15.    SQLQuery1.Open;
  16.  end;
  17. end;
I created the test database with Access 2016. I think this is a 32-bit application.
At first I compiled my test application with the 64-bit version of Lazarus - it failed to open the .accdb file with a similar error as yours. Then I tried the 32-bit version of Lazarus which worked but crashed when opening the query because I had forgotten the critical line "SQLQuery1.UsePrimaryKeyAsKey := false".

I am not sure if it is generally true that only 32-bit applications can open Access files by ODBC. Maybe I should install some Office-Addon... But anyway, give it a try and play with the bitness of your compiler.

loaded

  • Hero Member
  • *****
  • Posts: 824
Re: MSACCES error connection
« Reply #2 on: October 16, 2021, 12:02:43 pm »
Hi,
If you are going to work with an access database, you should check the presence of access drivers installed on ODBC according to the 32/64 bit model you are using.
Probably 64 Bit ODBC's don't have access drivers.

Therefore, to avoid such problems;  When working with access database files, I use the component object model.
A proverb says; Different Ropes For Different Folks

Access database, link code with component object model:
Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button1Click(Sender: TObject);
  2. var
  3.   connect,mdb_table:olevariant;  // add uses comobj
  4.   slinks:variant;
  5.   i:integer;
  6.   table_name:WideString='Parsel';  // Don't forget to change the table name here
  7.   field_name:WideString='json';  // Don't forget to change the field name here
  8. begin
  9.    OpenDialog1.Filter:='Microsoft Access Driver | *.mdb';
  10.    if OpenDialog1.Execute then begin
  11.        if fileExists(OpenDialog1.Filename) then begin
  12.            connect:=CreateoleObject('ADODB.Connection');
  13.            slinks:='DRIVER={Microsoft Access Driver (*.mdb)}; DBQ='+ OpenDialog1.FileName;
  14.            connect.open (slinks);
  15.            mdb_table:=CreateoleObject('ADODB.Recordset');
  16.            slinks:=' Select * from '+table_name;
  17.            mdb_table.open(slinks,connect,1,3);
  18.                showmessage('Total Record Count :'+ inttostr(mdb_table.recordcount));
  19.                mdb_table.movefirst;
  20.                for i:=0 to mdb_table.recordcount-1 do
  21.                begin
  22.                  if i<3 then showmessage(mdb_table.Fields[field_name].Value);
  23.                  mdb_table.movenext;
  24.                end;
  25.                mdb_table.close;
  26.        end;
  27.    end;
  28. end;

Check out  loaded on Strava
https://www.strava.com/athletes/109391137

eldonfsr

  • Sr. Member
  • ****
  • Posts: 446
Re: MSACCES error connection
« Reply #3 on: October 16, 2021, 06:21:20 pm »
Ok Thanks let me change and test, let me try data module i don't test on that way but is good option, when you test deafferent way has option to use what you think is better for you, thanks so much for your help....

eldonfsr

  • Sr. Member
  • ****
  • Posts: 446
Re: MSACCES error connection
« Reply #4 on: October 17, 2021, 06:36:47 am »
Ok testing with oledb i got message error... just when open connection
Code: Pascal  [Select][+][-]
  1. procedure TFormMgrt.MigrateMRP;
  2. Var Tsql,Tsqlb, Tsqlc:Tsqlquery;
  3.    Odbcconn,SQLQOdbcTv:olevariant;  // add uses comobj
  4.    slinks:variant;
  5.    table_name:WideString;
  6. begin
  7.   tsql:=TsqlQuery.Create(nil);
  8.   tsql.DataBase :=DM.InvMgrCon;
  9.   tsql.Transaction:= DM.SQLTInvMgr;
  10.   tsqlb:=TsqlQuery.Create(nil);
  11.   tsqlb.DataBase :=DM.InvMgrCon;
  12.   tsqlb.Transaction:= DM.SQLTInvMgr;
  13.   tsqlc:=TsqlQuery.Create(nil);
  14.   tsqlc.DataBase :=DM.InvMgrCon;
  15.   tsqlc.Transaction:= DM.SQLTInvMgr;
  16.  
  17.   Odbcconn:=CreateoleObject('ADODB.Connection');
  18.   slinks:='DRIVER={Microsoft Access Driver (*.mdb , .accdb)}; DBQ='+ OpenDialog1.FileName;
  19.   Odbcconn.open (slinks);
  20.   SQLQOdbcTv:=CreateoleObject('ADODB.Recordset');
  21.  
  22.  

loaded

  • Hero Member
  • *****
  • Posts: 824
Re: MSACCES error connection
« Reply #5 on: October 17, 2021, 08:34:26 am »
Then we need to go directly to the ODBC source and find out the driver name.
You know, but I'll write anyway-

32 Bit ODBC file path:
Code: Pascal  [Select][+][-]
  1. Win + R
  2. C:\Windows\SysWOW64\odbcad32.exe
  3. OK
64 Bit ODBC file path:
Code: Pascal  [Select][+][-]
  1. C:\Windows\System32\odbcad32.exe

We need to run the relevant application from the address and learn the name of the mdb file driver.

In your system, probably;
Code: Pascal  [Select][+][-]
  1. Microsoft Access Driver (*.mdb)

So your link code should be:
Code: Pascal  [Select][+][-]
  1.  slinks:='DRIVER={Microsoft Access Driver (*.mdb)}; DBQ='+ OpenDialog1.FileName;

I sent it in my previous transmission, if you can send a screenshot of the ODBC driver, we can talk more precisely.  ;D
Check out  loaded on Strava
https://www.strava.com/athletes/109391137

eldonfsr

  • Sr. Member
  • ****
  • Posts: 446
Re: MSACCES error connection
« Reply #6 on: October 17, 2021, 09:10:56 am »
Looks is 64 bits...

loaded

  • Hero Member
  • *****
  • Posts: 824
Re: MSACCES error connection
« Reply #7 on: October 17, 2021, 09:42:59 am »
windows operating system is not clear what will come out like lottery !!!   :)
I asked you for a screenshot of the driver tab.But you sent me user defined drivers. ;D
But let's make another adaptation according to the situation, maybe this time it will work.
Code: Pascal  [Select][+][-]
  1. {$ifdef CPU32}
  2. slinks:='DRIVER={Microsoft Access Driver (*.mdb)}; DBQ='+ OpenDialog1.FileName;
  3. {$endif}
  4.  
  5. {$ifdef CPU64}
  6. slinks:='DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ='+ OpenDialog1.FileName;
  7. {$endif}

By the way, the codes are working for me.
« Last Edit: October 17, 2021, 09:44:45 am by loaded »
Check out  loaded on Strava
https://www.strava.com/athletes/109391137

eldonfsr

  • Sr. Member
  • ****
  • Posts: 446
Re: MSACCES error connection
« Reply #8 on: October 17, 2021, 09:27:25 pm »
Thanks Is working....every day we learn some think thanks.

 

TinyPortal © 2005-2018