Connect external databases (Access, ODBC-Databases)

Frank_Raemakers

I got 2 questions.

1.
I have a problem with the update from changed fields in Navision to a ACCESS database. If I change in Navision Employee table the last_name and the email the update to access goes wrong. Only the first field is updated and I got this message.

"The call to member Update failed"

A insert of a new employee gives no message and goes good.


GLOBALS
ADOConn Automation 'Microsoft ActiveX Data Objects 2.7 Library'.Connection
ADOrs Automation 'Microsoft ActiveX Data Objects 2.7 Library'.Recordset
RecEmployee Record EMPLOYEE


OpenMethod := 2; // 1=adOpenKeyset; 2=adOpenDynamic; 3= adOpenStatic
LockMethod := 2; // 1=dLockreadonly; 2=adLockPessimistic; 3=adLockOptimistic; 4=adLockBatchOptimistic
CREATE(ADOConn);
Connstr := 'Driver={MySQL ODBC 3.51 Driver}; Data Source=D:\TestDB.mdb';
ADOConn.Open(Connstr);
CREATE(ADOrs);
IF RecEmployee.FIND('-') THEN
REPEAT
ADOrs.Open('SELECT * from U_MEDEWERKERS where CODE="'+RecEmployee."No."+'"',ADOConn,OpenMethod,LockMethod);
IF NOT ADOrs.EOF THEN BEGIN
ADOrs.Update('Naam',ParRecEmployee."Last Name");
ADOrs.Update('EMAIL',ParRecEmployee."E-Mail");
END ELSE BEGIN
ADOrs.AddNew('CODE',RecEmployee."No.");
ADOrs.Update('Naam',ParRecEmployee."Last Name");
ADOrs.Update('EMAIL',ParRecEmployee."E-Mail");
END;
ADOrs.Close;
UNTIL RecEmployee.NEXT=0;
ADOConn.Close;
CLEAR(ADOrs);
CLEAR(ADOConn);


2.
In the select statement I will select with 2 criterea. How must I wrote (programcode) this?

ADOrs.Open('SELECT * from U_MEDEWERKERS where CODE="'+RecEmployee."No."+'"',ADOConn,OpenMethod,LockMethod);




Does anyone have any ideia how to solve this?

Thanks a lot.

Best Regards,
Frank

Christian_Buehl

AFAIK you must give complete strings to the ADO functions.
Instead of

ADOrs.Open('SELECT * from U_MEDEWERKERS where CODE="'+RecEmployee."No."+'"'+'"',ADOConn,OpenMethod,LockMethod);

try this:

sqlstring := strsubstno('SELECT * from U_MEDEWERKERS where CODE=%1',RecEmployee."No.");
ADOrs.Open(sqlstring,ADOConn,OpenMethod,LockMethod);

Frank_Raemakers

Thanks Christian for the reply but it doesn't work.

The message form navision is now:
"The call to open member failed"

In my solution the open functionality works well but:

1.
I have changed in the employee table in Navision from 1 record 2 fields, Name and Email. When I start the update function I wrote, I can see with the debugger the update from the name goes well (ADOrs.Update('Naam',ParRecEmployee."Last Name");
but the next update (ADOrs.Update('EMAIL',ParRecEmployee."E-Mail"); from the email goes wrong.
The message is: The call to member Update failed. Microsot OLE DB Provider for ODBC Drivers returned the following message: (I translate now this message) The update query failed, because the row to be updated cannot found.

2.
My second question is:
If the U_MEDEWERKERS table has a second key how can I give a select on this table. How must I wrote the where condition for to fields. For instance Customer."No." and Customer.Name.

I hope you understood my questions

Kind regards

Frank

captain

Does anyone have any ideia how to solve this?

May be better to use ODBC (Access)'s tables as "Link Table" & ODBC (Access) database as Link Server for MSSQL"?

Best regards,
Valery

Christian_Buehl

Hi Captain, this solution looks like driving a tank for getting cigarettes. I assume a native database is used (my experience on same hardware about 5000 times faster than sql-server for standard usage)

I had similar problems.
I solved this using this method and a different open and LockMethod in the loop. And I recommend to use sql statements instead of the sometimes tricky access methods:

 OpenMethod := 1; // 1=adOpenKeyset; 2=adOpenDynamic; 3= adOpenStatic 
 LockMethod := 3; // 1=dLockreadonly; 2=adLockPessimistic; 
 SQLstring := strsubstno('UPDATE T1 SET Name = "%1" WHERE T1.Number="%2";',rec.Name,rec."No.");  // use your sql-statement here
 ADOconn.Execute(SQLstring);
 ADOrs.UpdateBatch;
 ADOrs.Requery;
 ADOrs.MoveFirst;

To access a table with mutiple keys should be no problem.
Assuming ODBC-Table T1 has fields no and name and you want to select from the Navision customer table :

The SQL-String should look like
strsubstno('SELECT T1.no, T1.name, T1.address FROM T1 WHERE T1.no = "%1" and T1.name = "%2";',customer."No.",customer.Name);

Be aware of encapsuling strings by " and the ending semicolon in the sql statement.

Cheers

Frank_Raemakers

Thanks Christian for your help..

It works.

Frank