SqlQuery and Microsoft.ACE.OLEDB.12.0 error

[ellano]

One of my clients is having the following error:

Microsoft.ACE.OLEDB.12.0

The Microsoft Access DB engine could not find the object 'location'.
Make sure that the object exists and that the name and path of the
object are written correctly, check the network connectivity and contact
your network administrator.

Source : Microsoft Access Database Engine
NativeError: 543884569
Error Source: Microsoft Access Database Engine
Sql State : 3011

FW_OPENRECORDSET(414)

This happens when using the following code repeatedly (and on another piece of code that depend on SqlQuery further on):

Code: Select all  Expand view

USE locations ALIAS Locations SHARED NEW
DATABASE oDbfP
oDbfP:LOAD()
aLoc:= oDbfP:SqlQuery( "SELECT location" )  //also tried SELECT location FROM location
CLOSE Locations
aLocat:= ARRAY(LEN(aLoc))
FOR i:=1 TO LEN(aLoc)
  aLocat[i]:=aLoc[i][1]
NEXT i
 

The program has been tested in 7 different other computers with OS ranging from Windows XP, Vista, 7, and 8 and works correctly.

Is FW depending on C:\Program Files\Common Files\Microsoft Shared\OFFICE14\ACEOLEDB.DLL or any other component that should be registered on the computer to work?

[nageswaragunupudi]

It seems ACE 12 is installed and working.

Can you please let us know:
1. Does the file locations.dbf exist on local disk of network drive?
2. Do both the exe file and locations.dbf reside in the same folder on local disk?
3. Try ? oDbfP:cFile and let us know the result.

[ellano]

Thanks for your response,

1. The DBF exists, it is used several times, and it is a local file in this case.
2. Both, the DBF and the EXE, are located on the same local disk.
3. If I do:

Code: Select all  Expand view

cProv:= oDbfP:cFile
? cProv

I get c:\temp\location.dbf, so yes, it is there in place where it should be.

[nageswaragunupudi]

c:\temp should not be used to store dbf files and I don't think you really placed it there.

Please keep the dbf in some normal folder and in the USE command please give the full path and try.

Instead of
USE locations ALIAS Locations SHARED NEW

USE C:\<yourfolder>\locations ALIAS locations SHARED NEW

[ellano]

OK. The folder was just a phony, it is installed in a correct path.

Changed for USE (GetcurDir()+"\Location") ALIAS Location SHARED NEW as per your suggestion.

For me did not change anything: still working correctly and as before. Need to consult with the client.

Thanks for your time. Will report back

[nageswaragunupudi]

Changed for USE (GetcurDir()+"\Location") ALIAS Location SHARED NEW as per your

No.
This much work TDatabase itself does automatically.
I have some doubts on this.

ADO connection is to be created with datapath.

For now, kindly hard code the real folder with absolute path. Not even relative path.

cFile := "<fullabsolutepathwithdrive>\locations.dbf"

example: "c:\fwh\samples\customer.dbf"

if File( cFile )
USE ( cFile ) ............etc ...........
<other code>
else
? 'file not found'
endif

[ellano]

The problem here is that I do not know in advance where is the client installing it.

I can always ask him to install the application in a fixed, and known, path to test it and then use that to open the DB as you suggest.

What is also very strange is that I have 3 instances of the command SqlQuery in that piece of code and only 2 of them are returning the error in the client's machine.

[nageswaragunupudi]

Also try "SELECT [LOCATION]"

[ellano]

Here are my findings:
1. Installed on 8 different computers in Europe ranging from Windows XP, Vista, 7, and 8 32 and 64 bit working without a hitch using the following syntax:
USE (GetcurDir()+"\test.dbf") ALIAS test SHARED NEW
USE ("c:\test\test.dbf") ALIAS test SHARED NEW
or
USE test ALIAS test SHARED NEW
and then
DATABASE oDbfP
oDbfP:LOAD() // ? oDbfP:cFile
aCondic := oDbfP:SqlQuery( "SELECT Location") //or "SELECT [Location]" or even "SELECT [Location] FROM test"
oBrwD:aCols := {}
oBrwD:SetArray( aCondic )

2. Installed in America on 2 computers with Windows 8 64 bit will ONLY work using
USE (GetcurDir()+"\test.dbf") ALIAS Test SHARED NEW
or
USE ("c:\test\test.dbf") ALIAS Test SHARED NEW
or even
USE (GetcurDir()+"\test") ALIAS Test SHARED NEW

Cannot use the 2nd syntax since I cannot force the user to install the program on c:\test

So if you use SqlQuery and have this strange problem now (thanks to Mr. Nages suggestions ) you know the answer.

[Rick Lipkin]

Elanno

This code works for me to determine the location of the folder where the executable resides .. then I set the internal default .. consider this code ..

Code: Select all  Expand view

//-- get timestamp on .exe //

cFILE := GetModuleFileName( GetInstance() )
aDIR  := DIRECTORY( cFILE )
dEXE  := aDIR[1] [3]            // timestamp used for revision info

// where .exe started from is default directory //

nSTART := RAT( "\", cFILE )
cDEFA  := SUBSTR(cFILE,1,nSTART-1)

aDIR   := NIL

SET DEFA to ( cDEFA )

Now that you have SET DEFAULT .. the application always knows where it is when you do this ..

cLocation := set(7) // returns the logical or unc path of the exe assuming your data is in the same folder

Select 1
Use ( cLocation+"\Table.dbf" via "DBFCDX" Shared

or if you use Ms Access .. assuming your .mdb or accDb is in the same folder ...

xPROVIDER := "Microsoft.Jet.OLEDB.4.0"
xSOURCE := cLocation+"\Groom.mdb" // legacy ms access
xPASSWORD := "xxxxxxxxx"

xSTRING := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Jet OLEDB:Database Password='+xPASSWORD

Rick Lipkin

[nageswaragunupudi]

Can we not replace this code

Code: Select all  Expand view

cFILE := GetModuleFileName( GetInstance() )
aDIR  := DIRECTORY( cFILE )
nSTART := RAT( "\", cFILE )
cDEFA  := SUBSTR(cFILE,1,nSTART-1)

with

Code: Select all  Expand view

cFilePath( ExeName() )

?

[ellano]

Since I have to do tests that traverse the Atlantic Ocean I will have to settle with:

SET DEFAULT TO (cFilePath( ExeName() ) ) //taken either from an ini file or directly as an instruction

or just plainly

USE (GetcurDir()+"\test.dbf") ALIAS Test SHARED NEW

until I find out what's going on

[nageswaragunupudi]

Mr Ellano

1. cFilePath( ExeName() ) --> Fullpath where the exe resides
2. GetCurDir() --> Present directory.

Both can be different.

Important point:

For using SqlQuery, ADO connection needs to be established to the "folder" where the DBF resides.

If you want to use SqlQuery you better not use SET DEFAULT TO.
RDD can search SET DEFAULT path and open DBFs but TDatabase clas has to know what is the exact folder name to obtain a valid ADO connection.

Whichever folder you keep DBF files, keep that folder name in a global variable and open
USE ( cPath + "\MYDATA.DBF" ) ................

TDataBase class tries to obtain the folder name by calling
DBINFO( DBI_FULLPATH )
If a DBF is opened without building the correct path info while opening the DBF, there is no way TDataBase can establish a valid ADO connection.

[ellano]

After a 3 hour debugging session with my client using TeamViewer we finally understood what is going on with SqlQuery:

My client was using path structures such as “c:\my folder 2003\my version 4\program.exe” in such cases the program did not work at all.

If you changed the path to: c:\my_folder_2003\my_version_4\program.exe SqlQuery did not gave an error but did not fill the array, in the other hand if you simply changed the path to only one level to: “c:\my_version 4\program.exe” then SqlQuery suddenly started working, giving no errors and filling arrays correctly.

So there you are. Now I am forced to install the program in paths without spaces (no c:\program files\blah blah) and directly to the root until I (you) find the cause of this mischief .

[nageswaragunupudi]

I suggest this small correction to adofuncs.prg.

Please locate the following line in your \fwh\source\function\adofuncs.prg.

Code: Select all  Expand view

  c        := Token( aSpec[ 1 ], ' ', 1 )

Please change it as:

Code: Select all  Expand view

  c        := aSpec[ 1 ]

Please recompile adofuncs.prg and include in your project.
You can experiment with long folder names containing blanks on your pc.

With this correction I found it working for me with my DBF file in f:\Base 67 maps\test folder\deep folder\customer.dbf