Problem inserting to an Access database

[Alan Chiew]

Hi All,

The following piece of code is what I have tried to update a simple
Access database (none of the commented out code worked either). The
connection to the database seems to be fine because I am able to read
data from it. However, when trying to add another row of data, nothing
seems to happen.

I have also checked the permissions in IIS and they seem to be set up
fine.

Any ideas would be much appreciated.

Regards,
Alan

<%@LANGUAGE = PerlScript%>

<%
use Win32::OLE;
use Win32::OLE::Con st 'Microsoft ActiveX Data Objects';

$Conn = Win32::OLE->new("ADODB.Con nection");
$RS = Win32::OLE->new("ADODB.Rec ordset");

$DBFile = "E:\\vhosts\\in ternaltest\\pub lic_html\\ITRte st.mdb";

$DSN = "PROVIDER=MSDAS QL;DRIVER={Micr osoft Access Driver (*.mdb)};
DBQ=$DBFile;UID =;PWD=;";

$Conn->Open($DSN);

$RS->Open("test", $Conn, adLockOptimisti c, adOpenDynamic);

$fields = ["barcode", "desc"];
$values = ["C999", "this"];

$RS->AddNew($fields , $values);

#$Conn->Open("INSERT INTO test VALUES ('aaa', 'bbb')");
#$RS->Open($SQL, $Conn, 1, 1);

#$SQL = 'insert into test (barcode) VALUES "C999"';
#$RS = $Conn->Execute($SQL );

#$RS->AddNew;
#$RS->Fields('barcod e')->{value} = "ABC";
#$RS->Update;
$RS->Close;
$Conn->Close;
%>

[Pavel Romashkin]

I had problems with AddNew method; for some wierd reason I couldn't make
it work with lists of fields. I ended up looping through the fields.
Have you tried to use the same SQL and VBA code directly in the DB? Is
the other field than Barcode required and not autonumber?
Pavel

Alan Chiew wrote:

Hi All,

The following piece of code is what I have tried to update a simple
Access database (none of the commented out code worked either). The
connection to the database seems to be fine because I am able to read
data from it. However, when trying to add another row of data, nothing
seems to happen.

I have also checked the permissions in IIS and they seem to be set up
fine.

Any ideas would be much appreciated.

Regards,
Alan

<%@LANGUAGE = PerlScript%>

<%
use Win32::OLE;
use Win32::OLE::Con st 'Microsoft ActiveX Data Objects';

$Conn = Win32::OLE->new("ADODB.Con nection");
$RS = Win32::OLE->new("ADODB.Rec ordset");

$DBFile = "E:\\vhosts\\in ternaltest\\pub lic_html\\ITRte st.mdb";

$DSN = "PROVIDER=MSDAS QL;DRIVER={Micr osoft Access Driver (*.mdb)};
DBQ=$DBFile;UID =;PWD=;";

$Conn->Open($DSN);

$RS->Open("test", $Conn, adLockOptimisti c, adOpenDynamic);

$fields = ["barcode", "desc"];
$values = ["C999", "this"];

$RS->AddNew($fields , $values);

#$Conn->Open("INSERT INTO test VALUES ('aaa', 'bbb')");
#$RS->Open($SQL, $Conn, 1, 1);

#$SQL = 'insert into test (barcode) VALUES "C999"';
#$RS = $Conn->Execute($SQL );

#$RS->AddNew;
#$RS->Fields('barcod e')->{value} = "ABC";
#$RS->Update;

$RS->Close;
$Conn->Close;
%>

[Alan Chiew]

On Wed, 24 Sep 2003 09:35:18 -0600, Pavel Romashkin
<pa************ *@hotmail.com> wrote:

I had problems with AddNew method; for some wierd reason I couldn't make
it work with lists of fields. I ended up looping through the fields.
Have you tried to use the same SQL and VBA code directly in the DB? Is
the other field than Barcode required and not autonumber?
Pavel

Hi Pavel,

I have tried the SQL code in the DB and it works fine. The other
field, desc, is just another text field and is not required.

Cheers,
Alan

[Pavel Romashkin]

I don't know what to suggest. Looks to me that executing SQL should have
worked. Try re-posting your original problem again - maybe someone will
have a better idea.
Good luck,
Pavel

Alan Chiew wrote:

On Wed, 24 Sep 2003 09:35:18 -0600, Pavel Romashkin
<pa************ *@hotmail.com> wrote:

I had problems with AddNew method; for some wierd reason I couldn't make
it work with lists of fields. I ended up looping through the fields.
Have you tried to use the same SQL and VBA code directly in the DB? Is
the other field than Barcode required and not autonumber?
Pavel

Hi Pavel,

I have tried the SQL code in the DB and it works fine. The other
field, desc, is just another text field and is not required.

Cheers,
Alan