[SOLVED]*.mdb ->Sort of chicken before the egg

[What I can do]

I finally got my utills to load a *.mdb file and do some basic operation and learning more everyday, so here is my road block for today.
I have a routine that checks if the *.mdb exist, if so load it but if not create it.
But our good friend Microsoft make Access file have to be full path and short name.

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

1.  TheDBFile:='GavinWords.mdb';
2.  TheDBFile:=ExtractFilePath(Application.ExeName)+TheDBFile;
3.  TheDBFile:=ExtractShortPathName(TheDBFile);
4.  

TheDBFile comes out nil because " TheDBFile:=ExtractShortPathName(TheDBFile);" the file must exist first.
any ideals to point me in a direction

[wp]

The first two lines are enough to define the full path to the mdb file, provided that it resides in the applications binary directory. Why do you need the third line? Must confess that I never used ExtractShortPathName...

After deciding whether file already exists you eventually must create a new one. Please note that the SQL "CREATE TABLE" works only when a mdb file exists. Take a look at https://wiki.freepascal.org/MS_Access#Create_a_database_programmatically to see how an empty mdb (or accdb) file can be created by code.

[What I can do]

Thank you wp, just so happen that is the exact code i use.
The problem is the ODBC Microsoft driver, the wiki ACESS examples all examples fail me except for defining the UserDSN file as " (Microfsoft Access Driver (*.mbd,*accdb)" 
then I add this "AConn.Params.Add('DBQ='+FN);"  but the kicker is the name has to be short name format.

[What I can do]

These were my test runs
[FAIL]      f:='CarCare.mdb;
[FAIL]      f:=ExtractFilePath(Application.ExeName)+'CarCare.mdb';
[Success] f:=ExtractShortPathName(ExtractFilePath(Application.ExeName)+'CarCare.mdb');
'CarCare.mdb' is an existing file

[paweld]

you must precede the file with a directory path, but it does not have to be a direct path, it can be indirect, that is.

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

1. conn.Params.Add('DBQ=.\accessdbfile.mdb');
2.  

[marcov]

(note that outside the IDE, the working dir is not necessarily the application dir)

[wp]

These were my test runs
[FAIL]      f:='CarCare.mdb;
[FAIL]      f:=ExtractFilePath(Application.ExeName)+'CarCare.mdb';
[Success] f:=ExtractShortPathName(ExtractFilePath(Application.ExeName)+'CarCare.mdb');
'CarCare.mdb' is an existing file

Cannot confirm this. The attached test application corresponds to the case "f:= Application.Location + 'CarCare.mdb';" and works (at least for me: Win 11, 64-bit IDE, Microsoft Access Database Engine Redistributable 2016 installed for 64 bit).

Unfortunately using Access is overly complicated because of the change from the mdb to the accdb format and because of 32- and 64-bit applications, and because Microsoft allows only a single bitness of the database engine (either 32-bit and 64-bit).

[What I can do]

UPDATE:
I had to work today and it gave me some get away and I came up with a solution.
It all about the order,
Since Applicate has an existing file the just extract the short name path then append the DB file name.
Microsoft Access is happy with the path being short but the DB file name is still long format.
It created everything just fine.

[What I can do]

UPDATE:
paweld suggestion works well also.  conn.Params.Add('DBQ=.\accessdbfile.mdb');
I like it smaller code and much simpler.

I remember seeing this before but the dot to me always represented the root of the directory tree where .. is the previous branch. What's going on here why does . not jump to root?

[Khrys]

I remember seeing this before but the dot to me always represented the root of the directory tree where .. is the previous branch. What's going on here why does . not jump to root?

The double dot  ..  represents the present working directory's (the PWD's) parent directory, while the single dot  .  represents the PWD itself. This is useful for creating a path from a filename, which for some use cases (such as yours) may be required.
If you give i.e. your database driver only a simple filename (accessdbfile.mdb), there's no way to tell where it'll end up looking for (and perhaps failing to find) it; it may be programmed to go through some hard-coded folders and never consider the PWD at all. But if you specify a path instead (./accessdbfile.mdb), this ambiguity is gone: you've explicitly told it to start at the present working directory (.) and start looking from there.

[dseligo]

Since Applicate has an existing file the just extract the short name path then append the DB file name.
Microsoft Access is happy with the path being short but the DB file name is still long format.
It created everything just fine.

So it seems that problem is in the path.
Do you have spaces in path or maybe some non-ASCII characters?

[wp]

you must precede the file with a directory path, but it does not have to be a direct path, it can be indirect, that is.

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

1. conn.Params.Add('DBQ=.\accessdbfile.mdb');
2.  

I edited the wiki https://wiki.freepascal.org/MS_Access#DSN-less_connection to avoid this misleading notation because the current directory ('.') may be changed at any time.

[What I can do]

Thank you wp

but you bring up a WOW! type of question. 

I edited the wiki https://wiki.freepascal.org/MS_Access#DSN-less_connection to avoid this misleading notation because the current directory ('.') may be changed at any time.

Two things come to me ...
1) this web page has some sort of editing that I'm not yet aware of
or
2) You have full administrative privileges with full sever access. If so then you are one of the engineers on this development team.   

either way, I humbly say thank you.

[TRon]

1) this web page has some sort of editing that I'm not yet aware of
or
2) You have full administrative privileges with full sever access. If so then you are one of the engineers on this development team.   

Everyone is able to add an account at the wiki adn that grants you privileges to edit pages. It is the same as for instance wikipedia (though there they seem to have stricter rules these days).

[What I can do]

Thank you TRon

That is very interesting. However I don't have much desire to make changes but I find that very interesting.

Most of my coed is fit a square peg in a square hole, but a lot of code I run across is what I call magic code where just three lines can make a full blown 3d  imager design package.   I find many coders put a round peg in a square hole by making the square hole round. 
I find a lot of smart guys in here and do appreciate all the help.