* * *

Author Topic: Database Path contains special characters like ! (  (Read 1201 times)

shobits1

  • Sr. Member
  • ****
  • Posts: 275
  • .
Database Path contains special characters like ! (
« on: April 19, 2017, 09:37:51 pm »
I use TODBCConnection to connect/open MS-Access database (.mdb)... everything works great until I try to open Database inside directory having special characters in it's name.
like 'D:\The Path to DB )\DB.mdb'

After searching and searching, I can't find way to make this work. (the same DB can be opened by MS-Access 2010 with no problem)

any help.

HeavyUser

  • Full Member
  • ***
  • Posts: 118
Re: Database Path contains special characters like ! (
« Reply #1 on: April 19, 2017, 10:09:22 pm »
I use TODBCConnection to connect/open MS-Access database (.mdb)... everything works great until I try to open Database inside directory having special characters in it's name.
like 'D:\The Path to DB )\DB.mdb'

After searching and searching, I can't find way to make this work. (the same DB can be opened by MS-Access 2010 with no problem)

any help.
Access does not use odbc try to use ADO instead.

shobits1

  • Sr. Member
  • ****
  • Posts: 275
  • .
Re: Database Path contains special characters like ! (
« Reply #2 on: April 19, 2017, 10:56:21 pm »
How to use ADO in lazarus?
Is it possible to use TDataSource, TSQLQuery?
How much it defer from TODBCConnection, it term of code changes?

I have minimal knowledge of Database programing, and I have already done so much in my current project (almost completed), and I don't want make big changes.

Afaik, Zeos is the only component that uses ADO and it doesn't support (*.mdb) files - maybe I'm wrong -

LacaK

  • Hero Member
  • *****
  • Posts: 539
Re: Database Path contains special characters like ! (
« Reply #3 on: April 20, 2017, 07:38:32 am »
There is no TADOConnection for FreePascal/Lazarus as ADO is Windows specific and not cross-platform.
TODBCConnection should work. It will help if you can debug your application and catch connection string which is passed to SQLDriverConnect() or at least give error message which you get plus properties of your TODBCConnection object.

shobits1

  • Sr. Member
  • ****
  • Posts: 275
  • .
Re: Database Path contains special characters like ! (
« Reply #4 on: April 20, 2017, 06:44:49 pm »
I tried to debug TODBCConnection and catch the value of ConnectionString,, but for some reason the debugger won't let me -even thought, I rebuilt lazarus with debug profile-.

anyway, this is the message I get:
Quote
Could not connect with connection string "DRIVER={Microsoft Access Driver (*.mdb)};Dbq={D:\_Projects\SeesHelper\DATA\!SECG-ECO-2016.mdb};Uid=admin;Pwd=password;". ODBC error details: LastReturnCode: SQL_ERROR; Record 1: SqlState: HY000; NativeError: -1044; Message: [Microsoft][ODBC Microsoft Access Driver] Not a valid file name.; Record 2: SqlState: 01000; NativeError: 1; Message: [Microsoft][ODBC Microsoft Access Driver]General Warning Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x2154 Thread 0x230c DBC 0x677b4c4 Jet'.;

and this is a cut down to how I open the DB:
Code: Pascal  [Select]
  1. procedure TfrmMain.OpenDataBase(ADBPath: string);
  2. begin
  3.   if not FileExists(ADBPath) then
  4.   begin
  5.     ShowMessage(msgFileNotFound);
  6.     Exit;
  7.   end
  8.  
  9.   //
  10.   // Close all TSQLQuery
  11.   //
  12.   dbConnection.Close(True);
  13.   dbConnection.Driver := 'Microsoft Access Driver (*.mdb)';
  14.   dbConnection.Params.Add('Dbq=' + UTF8ToCP1256(ADBPath) );
  15.   //This gives the same error
  16.   //dbConnection.Params.Add('Dbq=' + ADBPath );
  17.   dbConnection.Params.Add('Uid=admin');
  18.   dbConnection.Params.Add('Pwd=password');
  19.   dbConnection.Open
  20. end;
  21.  // No other parameters are changed in the ObjectInspector for dbConnection
  22.  

if I rename the file from !SECG-ECO-2016.mdb to SECG-ECO-2016.mdb,, the Database opens successfully.


[EDIT]
BTW, changing the driver to Microsoft Access Driver (*.mdb, *.accdb) gives this error message:
Quote
Could not connect with connection string "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};Dbq={D:\_Projects\SeesHelper\DATA\!SECG-ECO-2016.mdb};Uid=admin;Pwd=password;". ODBC error details: LastReturnCode: SQL_ERROR; Record 1: SqlState: HY000; NativeError: 63; Message: [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0xb0c Thread 0x2104 DBC 0x13817434                                                              Jet'.; Record 2: SqlState: HY000; NativeError: 63; Message: [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0xb0c Thread 0x2104 DBC 0x13817434                                                              Jet'.; Record 3: SqlState: HY000; NativeError: -1044; Message: [Microsoft][ODBC Microsoft Access Driver] Not a valid file name.;
« Last Edit: April 20, 2017, 06:48:39 pm by shobits1 »

miab3

  • Jr. Member
  • **
  • Posts: 92
Re: Database Path contains special characters like ! (
« Reply #5 on: April 20, 2017, 08:59:37 pm »
@shobits1,

Try ZEOS and ADO.
Here I wrote about it:
http://zeoslib.sourceforge.net/viewtopic.php?f=40&t=67800#p86611

Michal

shobits1

  • Sr. Member
  • ****
  • Posts: 275
  • .
Re: Database Path contains special characters like ! (
« Reply #6 on: April 20, 2017, 10:58:39 pm »
Thank you, miab3
Yes it works with Zeos 7.2-beta but not the stable 7.1.4 (the one I was trying before); also I can use "Provider=Microsoft.Jet.OLEDB.4.0" which comes with WinXP and later. the only down side I see for now is that I have to rewrite all my TSQLQuery and use (I don't know) maybe TZQuery? TZSQLProcessor?! also there is the question of performance when using nested SQL

anyway,, thank you again... I'm interested if there is way to continue using ODBC is my case (do not want to do big changes at least for now).

miab3

  • Jr. Member
  • **
  • Posts: 92
Re: Database Path contains special characters like ! (
« Reply #7 on: April 21, 2017, 12:17:35 am »
@shobits1,

It also works with:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\Dane\ADDemo.mdb;Persist Security Info=False

I advise to use ZEOS 7.2 svn 3986:
https://sourceforge.net/p/zeoslib/code-0/HEAD/tree/branches/testing-7.2/

Michal

shobits1

  • Sr. Member
  • ****
  • Posts: 275
  • .
Re: Database Path contains special characters like ! (
« Reply #8 on: April 21, 2017, 12:30:47 am »
It also works with:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\Dane\ADDemo.mdb;Persist Security Info=False
Yes, I know... I was making statement, not asking; sorry for the confusion.

Also, as side note for others; if DB has password, use Jet OLEDB:Database Password=mypassword;

LacaK

  • Hero Member
  • *****
  • Posts: 539
Re: Database Path contains special characters like ! (
« Reply #9 on: April 25, 2017, 07:48:38 am »
Only tip: Try enclose path in double quotes: "":
  dbConnection.Params.Add('Dbq="' + UTF8ToCP1256(ADBPath) + '"');

shobits1

  • Sr. Member
  • ****
  • Posts: 275
  • .
Re: Database Path contains special characters like ! (
« Reply #10 on: April 25, 2017, 08:52:32 pm »
Only tip: Try enclose path in double quotes: "":
  dbConnection.Params.Add('Dbq="' + UTF8ToCP1256(ADBPath) + '"');
thank you,, but unfortunately it doesn't work.

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus