MSACCES error connection
lazarus / 16-Databases

----------------------------------------------------------------------
eldonfsr · 2021-10-16 03:35

HI I follow example of create database access odbc programatic but
send  error

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  
OpenDialog1.Filter:='*.mdb ; *.accdb';   if OpenDialog1.Execute then
begin       if fileExists(OpenDialog1.Filename) then begin         
CFile:= OpenDialog1.FileName;          ODBCCon.Driver:='Microsoft
Access Driver (*.mdb, *.accdb)';         
ODBCCon.Params.Add('DBQ='+cfile ); //         ODBCCon.Params.Add(
'Locale Identifier=1031');//          ODBCCon.Params.Add(
'ExtendedAnsiSQL=1');          ODBCCon.Params.Add( 'CHARSET=utf8');  
      ODBCCon.Connected:=true;          ODBCCon.KeepConnection:=true; 
        SQLTOdbc.DataBase := ODBCCon;          SQLTOdbc.Action:=
caCommit;          SQLTOdbc.Active:=true;       end;   end;

----------------------------------------------------------------------
wp · 2021-10-16 09:57

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][+][-]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);begin  if FileExists(FILE_NAME)
then begin    ODBCConnection1.Driver:='Microsoft Access Driver (*.mdb,
*.accdb)';   
ODBCConnection1.Params.Add('DBQ='+ExpandFileName(FILE_NAME));  //    
   ODBCCon.Params.Add( 'Locale Identifier=1031'); //        
 ODBCCon.Params.Add( 'ExtendedAnsiSQL=1'); 
 ODBCConnection1.Params.Add( 'CHARSET=utf8'); 
 ODBCConnection1.Connected:=true; 
 ODBCConnection1.KeepConnection:=true;   SQLTransaction1.DataBase :=
ODBCConnection1;   SQLTransaction1.Action:= caCommit; 
 SQLTransaction1.Active:=true;   SQLQuery1.UsePrimaryKeyAsKey :=
false;   SQLQuery1.Open; end;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 · 2021-10-16 10:02

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][+][-]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 
connect,mdb_table:olevariant;  // add uses comobj  slinks:variant; 
i:integer;  table_name:WideString='Parsel';  // Don't forget to change
the table name here  field_name:WideString='json';  // Don't forget to
change the field name herebegin   OpenDialog1.Filter:='Microsoft
Access Driver | *.mdb';   if OpenDialog1.Execute then begin       if
fileExists(OpenDialog1.Filename) then begin         
 connect:=CreateoleObject('ADODB.Connection');         
 slinks:='DRIVER={Microsoft Access Driver (*.mdb)}; DBQ='+
OpenDialog1.FileName;           connect.open (slinks);         
 mdb_table:=CreateoleObject('ADODB.Recordset');           slinks:='
Select * from '+table_name;         
 mdb_table.open(slinks,connect,1,3);               showmessage('Total
Record Count :'+ inttostr(mdb_table.recordcount));             
 mdb_table.movefirst;               for i:=0 to
mdb_table.recordcount-1 do               begin                 if i<3
then showmessage(mdb_table.Fields[field_name].Value);               
 mdb_table.movenext;               end;             
 mdb_table.close;       end;   end;end;

----------------------------------------------------------------------
eldonfsr · 2021-10-16 16:21

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 · 2021-10-17 04:36

Ok testing with oledb i got message error... just when open connection
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
TFormMgrt.MigrateMRP;Var Tsql,Tsqlb, Tsqlc:Tsqlquery; 
 Odbcconn,SQLQOdbcTv:olevariant;  // add uses comobj 
 slinks:variant;   table_name:WideString;begin 
tsql:=TsqlQuery.Create(nil);  tsql.DataBase :=DM.InvMgrCon; 
tsql.Transaction:= DM.SQLTInvMgr;  tsqlb:=TsqlQuery.Create(nil); 
tsqlb.DataBase :=DM.InvMgrCon;  tsqlb.Transaction:= DM.SQLTInvMgr; 
tsqlc:=TsqlQuery.Create(nil);  tsqlc.DataBase :=DM.InvMgrCon; 
tsqlc.Transaction:= DM.SQLTInvMgr;  
Odbcconn:=CreateoleObject('ADODB.Connection'); 
slinks:='DRIVER={Microsoft Access Driver (*.mdb , .accdb)}; DBQ='+
OpenDialog1.FileName;  Odbcconn.open (slinks); 
SQLQOdbcTv:=CreateoleObject('ADODB.Recordset');

----------------------------------------------------------------------
loaded · 2021-10-17 06:34

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Win +
RC:\Windows\SysWOW64\odbcad32.exeOK64 Bit ODBC file path:
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
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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Microsoft
Access Driver (*.mdb)
So your link code should be:
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
 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.

----------------------------------------------------------------------
eldonfsr · 2021-10-17 07:10

Looks is 64 bits...

----------------------------------------------------------------------
loaded · 2021-10-17 07:42

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.
But let's make another adaptation according to the situation, maybe
this time it will work.
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 {$ifdef
CPU32}slinks:='DRIVER={Microsoft Access Driver (*.mdb)}; DBQ='+
OpenDialog1.FileName;{$endif} {$ifdef CPU64}slinks:='DRIVER={Microsoft
Access Driver (*.mdb, *.accdb)}; DBQ='+ OpenDialog1.FileName;{$endif}
By the way, the codes are working for me.

----------------------------------------------------------------------
eldonfsr · 2021-10-17 19:27

Thanks Is working....every day we learn some think thanks.