ADOQUERY HEADACHES

[hbez]

I am experiencing two frustrating problems with an ADOQuery:
1) QRY1 uses a ConnectionString to Access db1. QRY2 must use a ConnectionString to another Access db2 in a different folder. However, QRY2 keeps telling me it cannot find db2 in the folder where db1 is located. To at least make the thing work I copied the db to the first folder but, it's not supposed to be there.

2) I set up an SQL that I run on QRY2 and I can find a FieldValue without any problem. I immediately try to run another slightly different SQL on QRY2 but I get a 'Field not found' error if I then try to get a FieldValue again. The code looks as follows (abbr):

qrySetLanguage.SQL.Text := 'SELECT LANG, FIELDNAME, StringContent FROM FieldContent WHERE LANG="' + Language + '" AND FIELDNAME="lblReg"';
qrySetLanguage.Active := True;
lblReg.Caption := qrySetLanguage.FieldValues['StringContent'];

This is repeated several times. I've tried to Close and to de-Activate the query, no luck. As usual I'm probably missing the tree for the woods but will really appreciate help.
Hannes

 

[Griffyn]

need more information.

1. Try using two different TADOConnection objects that your TADOQuery objects link to. If this still fails in the same way, there must be something fundamentally wrong with the way you have things structured. Post more code, including the ConnectionStrings if this still happens.

2. Please show the full SQL strings of the two queries you're trying to run.

 

[hbez]

With a DBGrid, ADOConnection, ADOQuery and two buttons, my code is

procedure TForm1.btnDB1Click(Sender: TObject);
begin
  qryDB1.Active := False;
  //DB1 is in a sub-folder
  conStr1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;' +
                              'Data Source=OtherFolder\db1.mdb;' +
                              'Persist Security Info=False';
  qryDB1.SQL.Text := 'SELECT * FROM City';
  qryDB1.Active := True;
end;

procedure TForm1.btnDB2Click(Sender: TObject);
begin
  qryDB1.Active := False;
  //DB2 is in the root or could be in another sub-folder under the root
  conStr1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;'
                              'Data Source=db2.mdb;Persist Security Info=False';
  qryDB1.SQL.Text := 'SELECT * FROM Names';
  qryDB1.Active := True;
end;

end.
//NOTE: I do not want to refer to drive, eg C:\....

The first button works fine, the second not.

Hannes

 

[DjangMan]

Griffyn is probably right - use separate TADOConnection objects. You can either drop them on the form or create them manually. Otherwise both queries are probably creating a temporary connection object and sharing them.

 

[Griffyn]

from the posted code above, it appears you're actually using a single TADOConnection (conStr1) and simply modifying it's ConnectionString. This won't work. For two different databases, you need two different TADOConnection objects, or use the TADOQuery.ConnectionString property (rather than TADOQuery.Connection).

Also... You may want to modify your database path to

Data Source=' + ExtractFilePath(ParamStr(0)) + 'db2.mdb;

This ensures that you're referring to the same path that the application resides in. If you omit this, then the current folder is used. If your application were launched via a shortcut that specified a different folder (separate to your application path), your program may try to look in a different folder.

 

[hbez]

I have in fact tried two different ADOConnections as well as two different ADOQuerys. Eventually I left the connection to the 'remote' (different directory, it may be on a server) db as is and moved the 'local' db to the folder where the executable resides. Using ExtractFilePath(Application.exe) + 'db1.mdb' I got it to work. I guess I could refer to a sub-folder on the local pc in this manner, tho I didnt try it.

Hannes