[SOLVED]DBGrid- How to populate lazarus / 16-Databases ---------------------------------------------------------------------- What I can do · 2024-05-04 15:54 OS: Win 10 Compiler: Lazarus 3.2 Does any one have working demo of how to access data and populate a DBGrid from a file *.mdb 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 uses  Classes, SysUtils, Forms, Controls, Graphics, Dialogs, DBGrids; type   { TForm1 }   TForm1 = class(TForm)    DBGrid1: TDBGrid;  private   public   end; var  Form1: TForm1; implementation {$R *.lfm}               What connections and dataset and datsxource do I need. I have copyed the https://wiki.freepascal.org/MS_Access and not even got the demo to work. Every thing i view is mostly far more advanced that what I'm look for. The DB file is very small one table less than 100 records ---------------------------------------------------------------------- cdbc · 2024-05-04 16:24 Hi 1) Search our forum! 2) Have a look at this:https://wiki.lazarus.freepascal.org/ODBCConn Good luck Benny ---------------------------------------------------------------------- What I can do · 2024-05-04 16:27 thank you, I will check that out... ---------------------------------------------------------------------- Handoko · 2024-05-04 16:43 I saw you're having compile time issue with the message saying both SQLQuery1 and SQLTransaction1 not found. Have you dropped those components onto the form? If you don't how where to find them, see the video below: ---------------------------------------------------------------------- What I can do · 2024-05-04 16:54 yes, thank you but got this error ---------------------------------------------------------------------- What I can do · 2024-05-04 16:57 is there a problem? ---------------------------------------------------------------------- paweld · 2024-05-04 16:58 Attached is a sample project - see if it will work for you ---------------------------------------------------------------------- What I can do · 2024-05-04 17:02 or even this one ---------------------------------------------------------------------- Handoko · 2024-05-04 17:05 I found some video tutorials, maybe they can help: https://www.youtube.com/watch?v=TwiTgeU8t84 https://www.youtube.com/watch?v=Cq5QwKmETxs ---------------------------------------------------------------------- wp · 2024-05-04 17:39 Your posts remind me of my own experiences with Access. Since I am usually working with the 32-bit IDE I was only able to open mdb files when I used the driver specification without the *.accdb (ODBCConnection1.Driver := 'Microsoft Access Driver (*.mdb)' - there was no way to get mdb files to load without error from 64-bit applications. Finally I noticed that the corresponding driver, the one with '(*.mdb; *.accdb)' as specified in your code, was not installed by default, at least on my Windows. But it is possible to download and install it from https://www.microsoft.com/en- us/download/details.aspx?id=54920 (Access Database Engine Redistributable). There are downloads for both 32-bit and 64-bit, but you can only install one of them! I installed the 64-bit version since for 32-bit the old driver ('*.mdb') is available by default (at least on my machine). With the incorrect driver you are alway annoyed by a long barely understandable error message (like in reply #4)! So, the essence is (after installing the 64-bit Access Database Engine Redistributable): - for 32-bit applications (i.e. 32-bit Lazarus IDE): use ODBCConnection1.Driver := 'Microsoft Access Driver (*.mdb);' - for 64-bit applications (i.e. 64-bit Lazarus IDE): use ODBCConnection1.Driver := 'Microsoft Access Driver (*.mdb; *.accdb);', but only when the 64-bit Access Database Engine Redistributable is installed; if not you cannot use the 64-Lazarus IDE for mdb files. Regarding your compilation issue: I see in your screenshot of the form in the designer that two components seem to be missing: TSQLTransaction and TSQLQuery. Drop them on the form, and the application should work since all the properties required are set by code in your example. ---------------------------------------------------------------------- What I can do · 2024-05-04 20:47 Thank you, WP HANDOKO sent me this link https://www.youtube.com/watch?v=TwiTgeU8t84 but I got an error message.... he had another link to offer as well https://www.youtube.com/watch?v=Cq5QwKmETxs but i have not tried that yet. Now you have given me some other options to test and will post back when I get result. Thanks to everyone so far.  The best I judge a quality product is by it's support. The help files in wiki are great if you know what your doing so that's a learning curve but the Lazaus help support more than easily make up for lack of simple details. Thank everyone again. will be back in toush ---------------------------------------------------------------------- What I can do · 2024-05-04 21:05 My test code now.. .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 unit Unit1; {$mode objfpc}{$H+} interface uses  Classes, SysUtils, DB, SQLDB, odbcconn, Forms, Controls, Graphics, Dialogs,  DBGrids; type   { TForm1 }   TForm1 = class(TForm)    DataSource1: TDataSource;    DBGrid1: TDBGrid;    ODBCConnection1: TODBCConnection;    SQLQuery1: TSQLQuery;    SQLTransaction1: TSQLTransaction;    procedure FormCreate(Sender: TObject);  private   public   end; var  Form1: TForm1; implementation {$R *.lfm} { TForm1 } procedure TForm1.FormCreate(Sender: TObject);begin  ODBCConnection1.Driver:='Microsft Access Driver (*.mdb)';  ODBCConnection1.Params.Add('DBQ=CarCare.mdb');  ODBCConnection1.Params.Add('Locale Identifier= 1031');  ODBCConnection1.Params.Add('ExtendedAnsiSQL=0');  ODBCConnection1.Params.Add('CHARSET= ansi');  ODBCConnection1.Connected:=TRUE;  ODBCConnection1.KeepConnection:=TRUE;   SQLTransaction1.DataBase:= ODBCConnection1;  SQLTransaction1.Action:= caCommit;  SQLTransaction1.Active:= TRUE;   SQLQuery1.DataBase:=ODBCConnection1;  SQLQuery1.UsePrimaryKeyAsKey:=false;  SQLQuery1.SQL.Text:='SELECT * FROM dbTAble';  SQLQuery1.Open;   DataSource1.DataSet:=SQLQuery1;  DBGrid1.DataSource:=DataSource1; end; end. ---------------------------------------------------------------------- What I can do · 2024-05-04 21:58 OK Updated to 'Microsoft Access Driver (*.mdb; *.accdb); made the addition in my  ODBC Data Sources (64-bit) in my Lazarus source code big improvement now the error message is like triple in size .... ---------------------------------------------------------------------- wp · 2024-05-04 22:12 Typo in 'Microsft' - should be 'Microsoft'. Do you have a 32-bit Lazarus or a 64-bit Lazarus? You can use "ODBCConnection1.Driver := 'Microsoft Access Driver (*.mdb)';" only in the 32-bit IDE. For 64-bit you need  the newer "ODBCConnection1.Driver := 'Microsoft Access Driver (*.mdb; *.accdb)';". And I never made any changes to the ODBC Data sources. Did a few more experiments and found that the newer 64-bit version has issues with ancient mdb files created by Access 97. As far as I can tell it seems that the old 32-bit driver can open all .mdb files. ---------------------------------------------------------------------- Handoko · 2024-05-05 02:43 I looked into the reply #12, these lines of the error message: DBQ=CarCare.mdb NativeError: -1044 Message: [Microsoft][ODBC Microsoft Access Driver] Not a valid file name. I think maybe a full path file name is required. Searching the web, I found these comments: https://github.com/mkleehammer/pyodbc/issues/922 https://stackoverflow.com/questions/40616189/python-odbc-connection- not-a-valid-file-name-error https://stackoverflow.com/questions/72355227/odbc-microsoft-access- driver-not-a-valid-file-name ---------------------------------------------------------------------- What I can do · 2024-05-05 14:20 FAT!  FAT!  FAT!  see above for any links These people are wonderful in helping me and I thank them very much for donating there time and knowledge NOTE: This is far more complex then it use to be and so is our technology world many different make and manufactures have to share information of both data and user interface. I finally began to see what needs to talk to what; 1) there is a user setting (configuration) in the OS (win10) that needs to be configured    a. install ODBC 64    b. Add a [User DSN]       i. pick "Microsoft Access Driver (*.mdb, *.accbd)"       ii. Give it a name that makes since like "Access64"       iii. [Select] browse to your data base file     [save and/or ok] to acknowledge and close everything out You have just completed the OS portion and now your ready to start you Lazarus project 1) Project new application 2) drop on DB component and modules     a. like DBGrid, TODBCConnection, TSQLTransaction, TSQLQuery , TDataSource 3) Test db connection with the  TODBCConnection and configure it     a  only one setting [DatabaseName] = "Access64" as created above     b. you now test it,          set [Keep connected] to TRUE     c. The big test -- [Connected] to TRUE at this point if you get any errors they must be resolved. FAT1= I remarked out all code from many examples that make any setting to the ODBCConnection1 parameters. FAT2= do not assign any data base files leave blank FAT3= do not use any DSN files or reference there of FAT4= rem out all settings to Dataset1 and DBGrid1 FAT5= rem out all setting to SQLQuery1 Now you need to build the communication links of the tree OS talks to your data base file via UserDSN confuguration "Access64" ODBCConnection1 talks to [DatabaseName] "Access64" from the ODBC UserDSN settings                           and to [Transaction] SQLTransaction1 SQLTransaction1 talks to [Database] ODBCConnection1  (not circular but two way communication)                         also set [Action] to caCommit                                set [Active] to TRUE SQLQuery1 talks to [Database] through ODBCConnection1                 and to [Transaction] to SQLTransaction1                 set [UsePrimaryKeyAsKey] to false  (should be default)                 set [SQL] top line "SELECT * FROM car" (car is the name of my table) DataSource1 talks to [DataSet] SQLQuery1 DBGrid1 talks to [DataSource] DataSource1 The only programable code I use now is on "FormCreate"  is  SQLQuery1.Open; Everything is hard set and nothing programable which makes this my measuring stick. This is my first successful full view only test project.  now I have a starting point to build with. thanks to everyone ---------------------------------------------------------------------- wp · 2024-05-05 14:57 Congratulations that you were able to do a big leap forward. Just a comment from my personal opinion: As I already wrote above I always use DSN-less configurations since it gives me more freedom in using the database: I can copy the application and the database to another computer without having to take care of DSN configuration which maybe I am not even allowed to do there. ---------------------------------------------------------------------- What I can do · 2024-05-06 12:30 Thank you WP I agree with you but I had to find at least one successful point to start from.  All my OS are Windows  for now but I would like to port to Linux.  I'm trying to eliminate the DSN file all together and just have everything internal code. Your right it makes it easier on me to move to another computer.  I have several system and it's sort of like one is for developing and the other is for using. Plus I have helped a few other people with small utilities and apps for something they needed. It is nice to just give them the code and it's done.  When I used Delphi 2006 enterprise I developed DBunit to the point of just a few lines of code would do a lot and my goal is to do the same with Lazarus . Thank you for your help. The same to HANDOKO Thank you for your support as well Just and update I have manage to eliminate most of the DSN file and have discovered that the DBQ has to be full path short naming convention.  Now all I have to do is work on the driver still get an error inside code with out the DSN . ---------------------------------------------------------------------- Handoko · 2024-05-06 14:50 Glad to know you finally managed to make it works. If you're looking for a database that is easy to distribute for your programs, I would recommend SQLite. It requires zero admin, licensed in Public Domain, for distributing you only need to provide the client library, which can be easy found and downloaded from web. Most Linux distros have SQLite installed by default. SQLite is not specialized for multiuser environments, but it supports multiuser at once. All the information you need can be easily found in the Internet. If you want to see a basic demo of Lazarus - SQLite, here you can try this: https://forum.lazarus.freepascal.org/index.php/topic,65185.msg496461.h tml#msg496461 ---------------------------------------------------------------------- egsuh · 2024-05-07 03:33 QuoteIf you're looking for a database that is easy to distribute for your programs, I would recommend SQLite. The only problem with SQLite is that it does not support stored procedures. ---------------------------------------------------------------------- cdbc · 2024-05-07 04:45 Hi QuoteThe only problem with SQLite is that it does not support stored procedures.Are they __really__ needed in a single-user database?!? I tend to connect them with larger scale DB-operations... Regards Benny ---------------------------------------------------------------------- Thaddy · 2024-05-07 06:01 Quote from: egsuh on May 07, 2024, 05:33:16 amThe only problem with SQLite is that it does not support stored procedures. But it does support UDF's and Triggers. With those two you can come very close to stored procedures. ---------------------------------------------------------------------- Zvoni · 2024-05-07 06:05 Quote from: Thaddy on May 07, 2024, 08:01:28 amBut it does support UDF's and Triggers. With those two you can come very close to stored procedures. Correct. In regards to UDF, though, the "standard"-sqlite3conn doesn't offer to register such an UDF. You have to modify/extend sqlite3conn (Keyword Class-Helper), which i have done to support regex within SQLite-SQL-Statements ---------------------------------------------------------------------- egsuh · 2024-05-07 07:44 QuoteAre they __really__ needed in a single-user database?!? It's not definitely necessary, but I think stored procedures have advantages in keeping data consistency and overall maintenance. For example, I can change only stored procedures when I want to add some more features to database design and table structure, keeping Lazarus program part without change, or vice versa. ---------------------------------------------------------------------- Zvoni · 2024-05-07 08:01 Quote from: egsuh on May 07, 2024, 09:44:20 amQuoteAre they __really__ needed in a single-user database?!? It's not definitely necessary, but I think stored procedures have advantages in keeping data consistency and overall maintenance. For example, I can change only stored procedures when I want to add some more features to database design and table structure, keeping Lazarus program part without change, or vice versa. That's actually the reason why i store the SQL-Statements themselves in a separate table within the sqlite-database, and not in the source of my Lazarus-Frontend. As long as i don't change the "interface" of the Statement, i can change the statements without having to recompile my Frontend. A "somewhere in the middle of it"-approach might be to source out any Data-access to a separate library (dll, so), so if any changes appear you only have to recompile the Lib. The constraint of "don't change the interface of the Statement" still applies, though ---------------------------------------------------------------------- egsuh · 2024-05-07 10:49 QuoteThe constraint of "don't change the interface of the Statement" still applies, though This is especially important as I cannot remember the exact codes after a while since I first wrote the codes.