Insert a record in MsAccess 97 database.

[Jayadev U]

Hello David,

 

I have to insert a record into an old MsAcess 97 database.  I am able to connect using ODBC with the following command:

 

 cQuery := “Select * from CMPNY”

   oRs := Ado(cQuery,"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+cSysTab) // cSysTab is a variable which contains reference to  .mdb file with full path

 

How to insert a record ?

 

This does not work.

   cInsertQuery := "INSERT INTO CMPNY ([cmpny_sCmpShortName]) VALUES("+ c2sql('B27')+");"

   oRs:Execute(cInsertQuery)

 

Please help.

 

Best,

 

Jayadev

 

[David Field]

Jayadev,

Funcion ADO() returns a Recordset object no a connection.

To connect use ADOConn() function  in ADO.prg.

You will need to add code for the driver you are using.

Look at the available code, it will give you an idea of what to do.

something like:

Function AdoConn(Server, Database, User, Pass, Engine, Port)

...

CASE Engine == "MSACCESS"
sconection := " Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Database+;  //

This will return a connection object 

oConn := ADOConn(, cSysTab, , ,"MSACCESS")

oConn:Execute( "INSERT INTO CMPNY ([cmpny_sCmpShortName]) VALUES("+ c2sql('B27')+");")

Be aware that if you use that SQL command you will only be adding a record with value for cmpny_sCmpShortName.

Usually on a INSERT command you add all information required by the table

Let me know if you need more help

Regards,

David FIeld 

[Jayadev U]

Excellent David, everything worked as expected. Thanks so much.

 

The AdoConn function sample did not have "MSACCESS" engine, hence I was not able to connect using the function.  Perhaps you could include that in future updates so that we have a template to work on.

 

Further, please let me know how to insert NULL Value ?  One of the fields in the database is NULL.  See attached photo.

 

 

I am using the following c2sql function borrowed from HMG for value conversion for inserts.

 

STATIC function C2SQL(Value)

local cValue := ""

local cdate := ""

if ( valtype(value) == "C" .or. valtype( value ) == "M" ) .and. LEN(alltrim(value)) > 0

   value := HB_UTF8STRTRAN(value, "'", "''" )

endif

do case

   case Valtype(Value) == "N"

      cValue := AllTrim(Str(Value))

   case Valtype(Value) == "D"

      if !Empty(Value)

         cdate := dtos(value)

         cValue := "'"+HB_USUBSTR(cDate,1,4)+"-"+HB_USUBSTR(cDate,5,2)+"-"+HB_USUBSTR(cDate,7,2)+"'"

      else

         cValue := "''"

      endif

   case Valtype(Value) $ "CM"

      IF Empty( Value)

         cValue="''"

      ELSE

         cValue := "'" + value + "'"

      ENDIF

   case Valtype(Value) == "L"

      cValue := AllTrim(Str(iif(Value == .F., 0, 1)))

   otherwise

      cValue := "''"       // NOTE: Here we lose values we cannot convert

endcase

return cValue

--
Has recibido este mensaje porque estás suscrito al grupo "ADO Harbour" de Grupos de Google.
Para cancelar la suscripción a este grupo y dejar de recibir sus mensajes, envía un correo electrónico a ado-harbour...@googlegroups.com.
Para ver este debate, visita https://groups.google.com/d/msgid/ado-harbour/e1ebc7eb-6e1e-42f2-9ea1-1838bdaa2a21n%40googlegroups.com.

[David Field]

Glad to hear that you could resolve the issue.

I did mention that you would need to add code to ADOConn function.

There are MANY drivers for ADO and I am glad to help with what I can and is most used.

Users are welcome to modify, add, delete or contribute whatever they wish to the program/library.

There are no NULL value in Harbour, there is NIL but it is not the same.

The only way to insert or change a field to NULL is with a SQL Command and this is only posible if the field was defined to accept NULL as a value.

oConn:Execute( "UPDATE users SET password = NULL WHERE user = xxx" )

Some engines accept the above statement, others will not

FYI I do not use any conversión function to do INSERT's or UPDATE's, but I don´t know if MSAccess requieres somethig like that.

For example:

oConn:Execute( "INSERT INTO table (name, age, date, active) VALUES ("+cname+", "+STR(nAge,2)+", "+DTOS(DATE())+", "+if(lActive, "1", "0")+" )" )

[Jayadev U]

Hi David,

 

>>Glad to hear that you could resolve the issue.

Thanks for all your help.

 

>> I did mention that you would need to add code to ADOConn function.

There are MANY drivers for ADO and I am glad to help with what I can and is most used.

Users are welcome to modify, add, delete or contribute whatever they wish to the program/library.

 

Please include MSACCESS Engine as it is now tested to work correctly.  It will give a template to be improved upon in case there is a connection failure under different scenarios.

 

>> oConn:Execute( "UPDATE users SET password = NULL WHERE user = xxx" )

Some engines accept the above statement, others will not

OK

 

>>FYI I do not use any conversión function to do INSERT's or UPDATE's, but I don´t know >>if MSAccess requieres somethig like that.

Ok

 

Thanks once again for your help.

 

Best,

 

Jayadev

 

[David Field]

Hi Jayadev,

>>Please include MSACCESS Engine as it is now tested to work correctly.  It will give a template to be improved upon in case there is a connection failure under different scenarios.

Please send tested connection code that you use.

Thank you,

David Field

[Jayadev U]

Hi David,

 

This following code has been tested many times over:

 

/* code

 

   CASE Engine == "MSACCESS"

      sconection := " Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Database+";"

 

code ends*/

 

Best,

 

Jayadev

 

From: ado-h...@googlegroups.com <ado-h...@googlegroups.com> On Behalf Of David Field

Sent: 20 April 2026 00:26
To: ADO Harbour <ado-h...@googlegroups.com>

--

Has recibido este mensaje porque estás suscrito al grupo "ADO Harbour" de Grupos de Google.
Para cancelar la suscripción a este grupo y dejar de recibir sus mensajes, envía un correo electrónico a ado-harbour...@googlegroups.com.

Para ver este debate, visita https://groups.google.com/d/msgid/ado-harbour/de1f69b8-29e7-48c1-b7a4-0f224b5a5b3fn%40googlegroups.com.