Recent

Author Topic: [SOLVED] Non-ASCII paths in Microsoft Access (TODBCConnection)  (Read 4279 times)

Khrys

  • Full Member
  • ***
  • Posts: 148
[SOLVED] Non-ASCII paths in Microsoft Access (TODBCConnection)
« on: December 16, 2024, 09:22:41 am »
Hello everyone,
Microsoft is once again testing my patience it seems  :)

My problem is that the 32-bit Access driver can't handle anything but basic ASCII paths, and trying to teach people to avoid those is a fruitless effort. So I thought I'll just find a workaround - how hard could it be?

As it turns out, the combination of Windows + Access databases on network drives is very resistant to workarounds. Here's what I've tried so far:

Q: Escape the paths!
A: I couldn't find any documentation stating that this is even supported by the ODBC Access driver, and had no luck with the standard approaches I tried (URL encoding, e.g. "Gerät" → "Ger%C3%A4t", backslashes w/ hexadecimal Unicode code points). The driver also struggles with UNC paths, even though they explicitly allow codepoints below 0xFF  except for  "*+,/:;<=>?[\]|  which would suffice for the German special characters (ÄÖÜäöüß) I'd have to handle.

Q: Create a temporary symbolic link!
A: Windows considers symbolic links a security risk, and so you'll need admin permissions to create them, which my application doesn't have.

Q: Create a temporary hard link!
A: The relevant files mostly reside on network drives, meaning that any hard link would have to be created on there as well. But there's no general writable directory for temporary files (like  C:\temp  or  C:\Users\Khrys\Local\Temp), so it wouldn't be doable anyways. NTFS directory junctions (or reparse points in general) also don't support jumps between volumes, so this is out of the question too.

Q: Just  cd  to the file's location and use a relative path!
A: This would already be a very hacky solution at best, but the Access driver throws an additional wrench into the works here. I'd like to keep the scope of such global state changes as narrow as possible, ideally only while opening the connection (i.e.  pushd path/to/file && open_connection && popd  - as an analogy, it's not a CLI tool) because after all, paths ideally only serve to tell the OS where to find some resource initially and from then on it's all handles / file descriptors, right?
But the Access driver refuses to work in strange ways after restoring the old PWD, even if "primed" by executing some data-accessing SQL statement right after opening the connection, so unfortunately this ain't it either.

Q: Just create a temporary copy of the file and move it back after you're done!
A: The program I'm writing is an auxiliary tool that's integrated into our main application, which keeps the database open during the entire runtime of my program, so in addition to being cumbersome this approach couldn't even work in the first place - it has to be the actual file.

It drives me insane seeing how easily this could be solved using Unix-style symlinks, but the Gods on Mount Redmond (or Rainier, I suppose) won't listen to my pleas  :-[
I'm running out of ideas...



My hate of MS Access is ever-growing  :)
Inability to install both the 32-bit and 64-bit driver at the same time (thus forcing new tools into 32 bit too because the legacy application requires it), a lobotomized SQL dialect, abysmal performance, a broken driver that causes GDB to crash when debugging from Lazarus as soon as the application loses focus (???, also creates weird call stacks in the 5+ threads it spawns), the sheer audacity to paywall the  LONGINT  data type behind a monthly subscription, unhelpful error messages ("optional feature not implemented" when attempting to use the aforementioned type)... I could go on and on.  %)
« Last Edit: December 18, 2024, 11:30:08 am by Khrys »

Khrys

  • Full Member
  • ***
  • Posts: 148
Re: Non-ASCII paths in Microsoft Access (TODBCConnection)
« Reply #1 on: December 16, 2024, 09:40:23 am »
TL;DR - I want to teach the MS Access (ODBC) driver how to handle unfriendly paths such as  "N:\the file's\uhmmm.../äöü.mdb"

Zvoni

  • Hero Member
  • *****
  • Posts: 2836
Re: Non-ASCII paths in Microsoft Access (TODBCConnection)
« Reply #2 on: December 16, 2024, 09:55:51 am »
Have you tried using a DSN?

As for the issue: I'd be very surprised if it's an issue of access or its ODBC-Driver itself, since Non-ASCII-Paths go back post Win3.11 as far as i can remember.
And there was already a lot of Software using Access (mainly from VB4/5/6)
« Last Edit: December 16, 2024, 09:59:03 am by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

TRon

  • Hero Member
  • *****
  • Posts: 3946
Re: Non-ASCII paths in Microsoft Access (TODBCConnection)
« Reply #3 on: December 16, 2024, 10:01:39 am »
If all else fails, go 8.3
I do not have to remember anything anymore thanks to total-recall.

Khrys

  • Full Member
  • ***
  • Posts: 148
Re: Non-ASCII paths in Microsoft Access (TODBCConnection)
« Reply #4 on: December 16, 2024, 10:41:41 am »
Have you tried using a DSN?

No, I haven't; from what I've gathered it seems to me that DSNs are intended for fixed-location data sources (being stored in the registry after all, which wouldn't be typical for transient data). We use Access (or rather Jet 4 to be technically correct, I think?) as our application file format, so the path can be pretty much anything (selected via dialog).

As for the issue: I'd be very surprised if it's an issue of access or its ODBC-Driver itself, since Non-ASCII-Paths go back post Win3.11 as far as i can remember.
And there was already a lot of Software using Access (mainly from VB4/5/6)

The comments in the stackoverflow thread I linked in my first post speculate that it might be a bug in the 32-bit version only. I don't have first-hand experience with Windows versions prior to Windows 7 - that was before my time  :)
I also forgot to clarify in my initial post that not just non-ASCII characters trigger the issue - an ellipsis (...) or even just a single comma (,) has been the culprit in multiple reports.

If all else fails, go 8.3

I tried running  for %I in (.) do @echo %~sI  on an offending path on the network drive, but it just returned the long path unchanged. It did work on a local  C:\  path however, but sadly that doesn't help in my case.

paweld

  • Hero Member
  • *****
  • Posts: 1295
Re: Non-ASCII paths in Microsoft Access (TODBCConnection)
« Reply #5 on: December 16, 2024, 12:17:48 pm »
As @Zvoni wrote - use DSN:
Code: Pascal  [Select][+][-]
  1. procedure TForm1.bOpenClick(Sender: TObject);
  2. var
  3.   sl: TStringList;
  4. begin
  5.   if not OpenDialog1.Execute then
  6.     exit;
  7.   if ODBCConnection1.Connected then
  8.     ODBCConnection1.Connected := False;
  9.   //create file dsn
  10.   sl := TStringList.Create;
  11.   sl.Add('[ODBC]');
  12.   sl.Add('DRIVER=Driver do Microsoft Access (*.mdb)');
  13.   sl.Add('UID=admin');
  14.   sl.Add('UserCommitSync=Yes');
  15.   sl.Add('Threads=3');
  16.   sl.Add('SafeTransactions=0');
  17.   sl.Add('PageTimeout=5');
  18.   sl.Add('MaxScanRows=8');
  19.   sl.Add('MaxBufferSize=2048');
  20.   sl.Add('FIL=MS Access');
  21.   sl.Add('DefaultDir=' + ExcludeTrailingPathDelimiter(ExtractFilePath(OpenDialog1.FileName)));
  22.   sl.Add('DBQ=' + OpenDialog1.FileName);
  23.   sl.Text := UTF8ToWinCP(sl.Text);
  24.   sl.SaveToFile(GetTempDir + 'tmp.dsn');
  25.   sl.Free;
  26.   Caption := Format('DB File: %s', [OpenDialog1.FileName]);
  27.   //open dsn file
  28.   ODBCConnection1.FileDSN := GetTempDir + 'tmp.dsn';
  29.   ODBCConnection1.Connected := True;
  30. end;            
Best regards / Pozdrawiam
paweld

VB_Lazarus

  • New member
  • *
  • Posts: 7
Re: Non-ASCII paths in Microsoft Access (TODBCConnection)
« Reply #6 on: December 16, 2024, 12:36:11 pm »
TL;DR - I want to teach the MS Access (ODBC) driver how to handle unfriendly paths such as  "N:\the file's\uhmmm.../äöü.mdb"

I use DNS less DB.
For the path in Win I use
Code: Pascal  [Select][+][-]
  1. unitDM.DM.ODBCConnection.Params.Add('DBQ=' + UTF8ToWinCP(DatenbankName));      

Driver 32bit and 64bit.
You need different driver?

Khrys

  • Full Member
  • ***
  • Posts: 148
Re: Non-ASCII paths in Microsoft Access (TODBCConnection)
« Reply #7 on: December 17, 2024, 10:26:19 am »
Code: Pascal  [Select][+][-]
  1. unitDM.DM.ODBCConnection.Params.Add('DBQ=' + UTF8ToWinCP(DatenbankName));

Code: Pascal  [Select][+][-]
  1. sl.Text := UTF8ToWinCP(sl.Text);

Thanks, changing the codepage makes  ÄÖÜäöüß  work, however...

I also forgot to clarify in my initial post that not just non-ASCII characters trigger the issue - an ellipsis (...) or even just a single comma (,) has been the culprit in multiple reports.

...commas, spaces, ellipses (ASCII that should be allowed) still causes  "NativeError: -1044; Message: [Microsoft][ODBC Microsoft Access Driver] Not a valid file name."

These are the drivers I'm trying to use (both raise the error above):

Microsoft Access Driver (*.mdb) 10.00.19041.01  from  odbcjt32.dll
Microsoft Access Driver (*.mdb, *.accdb) 16.00.18227.20002  from  aceodbc.dll

paweld

  • Hero Member
  • *****
  • Posts: 1295
Re: Non-ASCII paths in Microsoft Access (TODBCConnection)
« Reply #8 on: December 17, 2024, 11:46:04 am »
...commas, spaces, ellipses (ASCII that should be allowed) still causes  "NativeError: -1044; Message: [Microsoft][ODBC Microsoft Access Driver] Not a valid file name."
I ran some tests and the characters that cannot occur in the file name:
- semicolon ;
- apostrophe '
- All UTF-8 characters that do not have an equivalent in your system code page

Multiple periods, commas do not pose any problems.
Best regards / Pozdrawiam
paweld

Khrys

  • Full Member
  • ***
  • Posts: 148
Re: Non-ASCII paths in Microsoft Access (TODBCConnection)
« Reply #9 on: December 18, 2024, 11:25:43 am »
I finally managed to find a solution! Thanks @paweld:

sl.Add('DefaultDir=' + ExcludeTrailingPathDelimiter(ExtractFilePath(OpenDialog1.FileName)));

The  DefaultDir  option was what I needed. By itself it has the same path recognition problems, but when combined with the fourth approach in my initial post (cd  to the file's location and use a relative path), passing  DefaultDir=.  keeps the driver from malfunctioning after restoring the previous working directory.

I suppose the presence of this option causes the driver to resolve its (internal) working directory exactly once when opening the connection and using its canonical location as the base for subsequent relative path accesses, but that's just speculation.

Thanks again @paweld and @VB_Lazarus!

 

TinyPortal © 2005-2018