Cannot open .mdb file on Win 10

[ranny]

Hi,

Many years ago I wrote a small program in VB that opened a simple database in .mdb format and allowed simple searching.  The purpose of this was to have a simple search without the need for Microsoft Access installed on many machines.  It worked fine.  I started to create a new one using Lazarus and it was working fine as well until I tried it on Windows 10 and it wont open the database.

The code I have is this (got it on the web somewhere):-

Code: Pascal  [Select][+][-]

1.    //make ODBC connection
2.     ODBCConnection1.Driver := 'Microsoft Access Driver (*.mdb, *.accdb)';
3.     ODBCConnection1.Params.Add('DBQ=.\id931.mdb');      // or specify full path to mdb file
4.     ODBCConnection1.Params.Add('Locale Identifier=1031');
5.     ODBCConnection1.Params.Add('ExtendedAnsiSQL=1');
6.     ODBCConnection1.Params.Add('CHARSET=ansi');
7.     ODBCConnection1.Connected := True;
8.     ODBCConnection1.KeepConnection := True;
9.  
10.     //transaction
11.     SQLTransaction1.DataBase := ODBCConnection1;
12.     SQLTransaction1.Action := caCommit;
13.     SQLTransaction1.Active := True; //not applied to ms access(false)
14.  
15.     SQLQuery1.DataBase := ODBCConnection1;
16.     SQLQuery1.UsePrimaryKeyAsKey := False;

I get the following response in the error:-

......

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

Its important this program will work on any PC in our office WITHOUT the need to install any other files or passwords etc. on these machines.  In Win 7 it worked fine in this way, but not in Win 10.

Can this be fixed easily?

Thanks in advance.

[update]
Also, when it compiles it seems to take 3.3GB of memory!  something is clearly wrong!

[wp]

I don't use Access a lot any more, but IIRC the new Access driver which you specify as "Microsoft Access Driver (*.mdb, *.accdb)" always has been making trouble. I was luckier with the old driver "Microsoft Access Driver (*.mdb)" (without "*.accdb"). Please try this one instead. But I think the old driver is 32-bit only, i.e. you only can used it in a 32-bit application.

When I compile a little demo project using this driver memory hardly increases (the overall memory usage is at 4.3 GB, though).

[ranny]

Thanks for the comment.  I tried that and its still the same error, and still consuming 3.3GB of memory!

I am not very good with database stuff so I just found what I could on the internet and was pleased when it worked on Win 7 and frustrated with it not working on Win 10.

As I mentioned, I cannot go around installing drivers on other machines so if there is a solution that will work without doing such then that will be good.  If not then I need to park this little project.

Ever hopeful...

[wp]

Are you sure that you are building a 32-bit application? Please post here what is displayed in the About box of Lazarus: "Help" > "About" > right-click on image and select "Copy version information to clipboard", and paste the clipboard here.

[ranny]

Here is the info:-

Lazarus 1.6.4 r54278 FPC 3.0.2 x86_64-win64-win32/win64

I don't really know what all of of that means to be honest.

[wp]

The x86_64-win64 means that you are using the 64-bit IDE, i.e. you are creating a 64-bit application. The mdb driver, however, is for 32 bit.

It is possible to create a 32-bit application from the 64-bit IDE by cross-compilation. But this is too complicated for me, I installed the 32-bit installation in addition to the 64-bit IDE. This can be done as a "secondary installation" which leaves the first installation alone so that you can always go back to it if something goes wrong. In fact you can install as many versions as you like as secondary installations.

These are the steps to create a secondary Lazarus installation:

• Download the 32-bit version of Lazarus. For your current version 1.6.4, you find the download at https://sourceforge.net/projects/lazarus/files/Lazarus%20Windows%2032%20bits/Lazarus%201.6.4/lazarus-1.6.4-fpc-3.0.2-win32.exe/download. Since v1.6.4 is rather old I'd recommend that you upgrade to the the current Lazarus release 2.0.2 which you can download from https://sourceforge.net/projects/lazarus/files/Lazarus%20Windows%2032%20bits/Lazarus%202.0.2/lazarus-2.0.2-fpc-3.0.4-win32.exe/download. Note the "win32" in these filenames, this is for the 32-bit IDEs; the 64-bit installation would have a "win64" here.
• Run the downloaded installer. In the second page of the installer ("Select Destination Location") enter a path different from your first installation and - IMPORTANT - check the box "Create a secondary installation". Do not install into "c:\program files" because this would force you to work with admin rights.
• On page "Select configuration folder" specify a folder in which Lazarus will store your configuration data. It must be in a folder in which you have write permissions.
• In the next page ("Select Components"), uncheck the options to associate file options. Proceed with the installation.
• If you have personal IDE settings you must repeat the configuration after installation because the new installation uses default settings. In particular you must install third-party components again
• When this is done you can try to compile and run your program, hopefully it should work now. If you installed the current version of Lazarus 2.0.2 you should make a backup copy first because v2.0.2 could write new properties to the components on your forms which cause problems when you open the project in your old version 1.6.4 again.

[ranny]

Thanks for that, that might be worth trying.

But I have a question, I am using 1.6.4 on both Win 7 and Win 10, I think my Win 7 was 64 bit.  When I compiled on Win 7 it worked on Win 7 but that compiled version did not work on Win 10.  When compiled on Win 10 it did not work on Win 10 but I never tried it on Win 7 but could look at that.  Is it the Win 10 operating system drivers that are giving me the problem?

[af0815]

Is this realy true for a "hello world" programm, or only for programm using 3rd partie libs ?

if you start on a commandline, what message is written ??

compile for Win32 should work for both win versions.

[wp]

I am using 1.6.4 on both Win 7 and Win 10, I think my Win 7 was 64 bit. 

The bitness of the OS is not so important. On Win 7, do you use a 32-bit or 64-bit Lazarus? Look at the About box of Lazarus to determine whether this is for 32 bit or 64 bit, just as I explained above for Win10.

[EDIT]
I am attaching a little demo constructed around a dummy mdb file of the TvPlanIt project. I can compile and run the demo successfully on 64bit Win10 and 32bit Win7; the application is 32bit in each case.

Compiled with the 64-bit IDE, I get the error message "Could not connect with connection string "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=D:\Prog_Lazarus\tests\_database\access_odbc\data.mdb;". ODBC error details: LastReturnCode: SQL_ERROR; Record 1: SqlState: IM002; NativeError: 0; Message: [Microsoft][ODBC Driver Manager] The data source name was not found; no default driver specified" (translated to English).

[devEric69]

Another possibility: is the driver for accessing Acces\Excel as a database installed ( ==> To download it, you have to search for "AccessDatabaseEngine_X64" in a web search engine; but it's for direct access, privileging ADO tchnology according to Microsoft.... Maybe, it can also be used as an ODBC driver )?

[ranny]

I tried the demo program and I got the error message as the IDE is 64 bit I think.  So I guess  I need to have a 32bit compiler if  I want to continue that project.

However, more immediate problem is that after downloading 2.0.4, my other programs wont compile due to problems with Versionsupport.pas that I use.  I remember this before when I tried to install la newer version than 1.6.4.  Is there a solution to this issue? 

This is the part of VersionSupport.pas where the compiler states that lpGtk, lpGtk2 etc "Identifier not found "lpGtk" and Constant expression expected.

Code: Pascal  [Select][+][-]

1. function GetWidgetSet: string;
2. begin
3.   case WidgetSet.LCLPlatform of
4.     lpGtk:   Result := WIDGETSET_GTK;
5.     lpGtk2:  Result := WIDGETSET_GTK2;
6.     lpWin32: Result := WIDGETSET_WIN;
7.     lpWinCE: Result := WIDGETSET_WINCE;
8.     lpCarbon:Result := WIDGETSET_CARBON;
9.     lpQT:    Result := WIDGETSET_QT;
10.     lpfpGUI: Result := WIDGETSET_fpGUI;
11.   else
12.     Result:=WIDGETSET_OTHER;
13.   end;
14. end;

[wp]

What is VersionSupport.pas? I cannot find it in the entire Lazarus file structure. Therefore I suppose this is a third-party unit which is not updated.

At some time after Laz 1.6.4, some procedures and declarations were moved to other units. The lpGtk, lpGtk2 etc identifiers now are in unit LCLPlatformDef. After adding this unit to "uses" the function still does not compile because the string constants WIDGETSET_GTK, WIDGETSET_GKT2, etc are not found. I suppose they are just names of the widgetsets. Therefore, you could use direct strings, like here:

Code: Pascal  [Select][+][-]

1. function GetWidgetset: string;
2. begin
3.   case InterfaceBase.Widgetset.LCLPlatform of
4.     lpGtk:    result := 'GTK widget set';
5.     lpGtk2:   result := 'GTK 2 widget set';
6.     lpWin32:  result := 'Win32/Win64 widget set';
7.     lpWinCE:  result := 'WinCE widget set';
8.     lpCarbon: result := 'Carbon widget set';
9.     lpQT:     result := 'QT widget set';
10.     lpfpGUI:  result := 'fpGUI widget set';
11.   else
12.     result := 'Other gui';
13.   end;
14. end;

Or you could use the array LCLPlatformDirNames which is declared in LCLPlatformDef, too. This would have the advantage that you get up-to-date names if a new widgetset is added (like qt5, gtk3 or cocoa which are missing in your enumeration):

Code: Pascal  [Select][+][-]

1. function GetWidgetSet: string;
2. begin
3.   Result := LCLPlatformDirNames[WidgetSet.LCLPlatform];
4. end;

[ranny]

That's great thanks, some things for me to try out there.  Will post the success!

[minesadorada]

I tried the demo program and I got the error message as the IDE is 64 bit I think.  So I guess  I need to have a 32bit compiler if  I want to continue that project.

However, more immediate problem is that after downloading 2.0.4, my other programs wont compile due to problems with Versionsupport.pas that I use.  I remember this before when I tried to install la newer version than 1.6.4.  Is there a solution to this issue? 

You need to add LCLPlatformDef to the Uses clause of the implememtation section.
e.g.

Code: [Select]

Uses
  resource, versiontypes, versionresource, InterfaceBase
  ,LCLPlatformDef;