AnsweredAssumed Answered

vrf Help Accessing Access

Question asked by VRFuser on Oct 18, 2004
Georg was helpful with my problem (thank you)...but I'm a little weak with SQL and VEE together. I was wondering if anybody has an complete SQL example where a field is searched, the record retrieved, then a new record is written.

I've been playing with this by creating a SQL query in Access, then pasting the text into VEE. Below is a snippet of the search portion of the query:

SELECT [586707].LAST_MARKED, [586707].EMM_SN
FROM 586707
WHERE ((([586707].LAST_MARKED)=1));

I don't know if I'm using the correct references or not, and I'm not sure of syntax. Any further help would be greatly appreciated.


=====================================
Patrick Barney
Senior Test Engineer
Bombardier Recreational Products
329 Hallberg Street
Delavan, WI 53115 USA
tel: 262-740-5048
fax: 262-740-5060
email: pat.barney@brp.com
=====================================

>>> g.nied@gn-software.de 10/13/04 11:00AM >>>
Pat,

I perform the following steps:

- I try to insert the record with a INSERT SQL command
if you use the same field order than in your table you can ommit the 'FIELDS' Section
make sure all text values are surrounded by quotation marks


execute the command with:  connection.execute(SQL_Text)

put an error pin at the object and if an error occurs you can check for the error of double index:



- if you have the 'double index' error, build a SQL UPDATE command and execute it




best regards,

G. Nied



Georg Nied ? Software-Development ? Hansestr. 7 ? 51149 Cologne ? Germany
Tel: +49 (2203) 9770915 ? Fax: +49 (2203) 9770924 ? E-Mail: info@gn-software.de




Original Message     
processed by Tobit InfoCenter
Subject:
[vrf] Help Accessing Access (13-Okt-2004 16:57)     
From:   
Pat.Barney@brp.com     
To:     
vrf@agilent.com     

Hi All -

In my VEE program I need to generate a unique serial for our product. The
format is DDDYYXXXRR. Where DDD is the day-of-the-year (doty), YY is the
year, XXX is the counter (001 -> 999), and RR is the revision level. When the doty
and/or year changes, the counter is reset to 001.

I write the serial number along with aother data to a database in Access. When
the program runs, it generates the doty and year values and compares it to
the last record in the database. If doty or the year has not changed, the
counter
is just incremented. Then the new data is written to the database.

I used the example program "Access97DataClient.vee" and modified getRecords
UserFunction to go to last record in the database and extract the data. The
problem is that when I use the resultsRS.MoveLast() command in the
getRecords function, it appears that it doesn't always go to the last record. I have
the serial set as the primary key so that i cannot possibly generate a duplicate
serial number.

I'm open to any suggestions or thoughts anybody has.

- Pat

=====================================
Patrick Barney
Senior Test Engineer
Bombardier Recreational Products
329 Hallberg Street
Delavan, WI 53115 USA
tel: 262-740-5048
fax: 262-740-5060
email: pat.barney@brp.com
=====================================


---
You are currently subscribed to vrf as: g.nied@gn-software.de
To subscribe send a blank email to "join-vrf@it.lists.it.agilent.com".
To unsubscribe send a blank email to "leave-vrf@it.lists.it.agilent.com".
To send messages to this mailing list, email "vrf@agilent.com".
If you need help with the mailing list send a message to "owner-vrf@it.lists.it.agilent.com".




---
You are currently subscribed to vrf as: rsb@soco.agilent.com
To subscribe send a blank email to "join-vrf@it.lists.it.agilent.com".
To unsubscribe send a blank email to "leave-vrf@it.lists.it.agilent.com".
To send messages to this mailing list,  email "vrf@agilent.com". 
If you need help with the mailing list send a message to "owner-vrf@it.lists.it.agilent.com".

Outcomes