I added a login and password and seems to have done the trick. It looks like there were 3 instances of the database connected, so this could also been an issue. Modified the code to check for existence of attached file and, if present and with records, the SCONNECT and SATTACH portion of the code is now bypassed. Thank you all for the help. Looks like today everybody is getting their issues resolved. J Javier, Javier Valencia, PE O: 913-829-0888 C: 913-915-3137 From: 'Karen Tellef' via RBASE-L [mailto:[email protected]] Sent: Monday, February 11, 2019 12:32 PM To: [email protected] Subject: Re: [RBASE-L] - Working with external databases Sorry, those parameters are in the SCONNECT Karen -----Original Message----- From: javier.valencia <[email protected]> To: rbase-l <[email protected]> Sent: Mon, Feb 11, 2019 12:17 pm Subject: RE: [RBASE-L] - Working with external databases Karen, Where do you include the parameters you mentioned? In the delete or insert command string or the actual DELETE/INSERT command line? I don’t have any on the SCONNECT or SATTACH commands and the database connects and the table attaches correctly. Albert, You might have something there since the command is passed on to the access database (???). I will create a long command line and see if it helps. I always try to make my code readable and also, in the back of my mind, have the 80 character limit which might not even be applicable anymore…old habits die hard. Javier, Javier Valencia, PE O: 913-829-0888 C: 913-915-3137 From: [email protected] [mailto:[email protected]] On Behalf Of Albert Berry Sent: Monday, February 11, 2019 11:36 AM To: [email protected] Subject: Re: [RBASE-L] - Working with external databases Javier: it might be the + continuation character. Try a straight long string on one line. Albert On Feb 11, 2019, at 10:28 AM, 'Karen Tellef' via RBASE-L <[email protected]> wrote: I've never had to use username/password, but my notes say you include them in your string with the semicolon delimiters like this: uid=my_user_name;pwd=my_pw Karen -----Original Message----- From: javier.valencia <[email protected]> To: rbase-l <[email protected]> Sent: Mon, Feb 11, 2019 11:10 am Subject: RE: [RBASE-L] - Working with external databases Thanks Buddy, I don’t believe the Access database requires an userid and password; I can open it from Access directly. I will check with the creator of that database. Where would I add the userid and password on the SCONNECT command? Javier, Javier Valencia, PE O: 913-829-0888 C: 913-915-3137 From: <mailto:[email protected]> [email protected] [ <mailto:[email protected]> mailto:[email protected]] On Behalf Of Buddy Walker Sent: Monday, February 11, 2019 7:50 AM To: <mailto:[email protected]> [email protected] Subject: RE: [RBASE-L] - Working with external databases Javier Is it access rights. You may need a user (uid) and password (psw). Buddy From: <mailto:[email protected]> [email protected] < <mailto:[email protected]> [email protected]> On Behalf Of <mailto:[email protected]> [email protected] Sent: Monday, February 11, 2019 1:58 AM To: <mailto:[email protected]> [email protected] Subject: [RBASE-L] - Working with external databases I am connecting to an external database using the DSNless command: SET VAR vcommandC = ('SCONNECT ' + '''' + + ';Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\Program Files (x86)\sPMS Program\SAMS.mdb;' + '''') &vCommandC And then SATTACH a table using the command: SATTACH 'SegmentData' AS 'ASegmentData' USING ALL ALIAS + ASubSection, + ASegmentID, + ARoad, + ABegSeg, + AEndSeg, + ADistrict, + APavementType, + ALength, + AWidth, + ATotalSize, + ALanes, + ADirection, + AClass, + ARightOfWay, + ABegMilepost, + AendMilepost, + AUtilities I can access the table and data and run queries but I am having problems trying to delete and insert rows to the attached table. For example, the following command does not seem to work: DELETE ROWS FROM ASegmentData WHERE(ASubSection+ ASegmentID) + NOT IN (SELECT seg_chk FROM features WHERE sub_sys = 'CJC') If a project the attached table to a local table: PROJECT TEMPORARY BSegmentData FROM ASegmentData USING * And run the DELETE ROWS command on the projected table it works correctly so I know the syntax is correct, same thing with the INSERT INTO… command, works great on the projected table but not on the SATTACHEd table. I am obviously missing something because I am pretty sure R+Base can add/delete rows on external databases/tables and I believe I have done this before but I can’t seem to find that particular application. Any help will be greatly appreciated. Javier, Javier Valencia, PE O: 913-829-0888 C: 913-915-3137 From: <mailto:[email protected]> [email protected] [ <mailto:[email protected]> mailto:[email protected]] On Behalf Of <mailto:[email protected]> [email protected] Sent: Thursday, February 07, 2019 11:22 AM To: <mailto:[email protected]> [email protected] Subject: RE: [RBASE-L] - SQL tutorial You can easily check this yourself. Place the following code in a command file makings sure that vPOID has a valid value SET ERROR VAR hold SET VAR vchk = ReceiptStatus IN PODetail WHERE PO_ID = .vPOID AND ReceiptStatus = 'P' IF hold = 0 THEN Trace the code one line at the time looking at variables hold and vchk. Look at the value for vchk and hold after the code executes the second line. This should tell you how the ERROR VAR changes depending on whether you get a valid value for vchk or not. Javier, Javier Valencia, PE O: 913-829-0888 C: 913-915-3137 From: <mailto:[email protected]> [email protected] [ <mailto:[email protected]> mailto:[email protected]] On Behalf Of jan johansen Sent: Thursday, February 07, 2019 11:09 AM To: <mailto:[email protected]> [email protected] Subject: Re: [RBASE-L] - SQL tutorial Group, Part of this didn't get answered, maybe obliquely. In the OLD code IF Hold = 0 Does that mean success or failure? Jan From: "jan johansen" < <mailto:[email protected]> [email protected]> To: <mailto:[email protected]> [email protected] Date: Tue, 05 Feb 2019 15:41:06 -0800 Subject: [RBASE-L] - SQL tutorial All, I think I need a SQL refresher. I need to update old code that looks like this. SET ERROR VAR hold SET VAR vchk = ReceiptStatus IN PODetail WHERE PO_ID = .vPOID AND ReceiptStatus = 'P' IF hold = 0 THEN So is IF hold = 0 the same as IF SQLCODE = 0? Sorry such a dumb question. Jan -- For group guidelines, visit <http://www.rbase.com/support/usersgroup_guidelines.php> http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to <mailto:[email protected]> [email protected]. For more options, visit <https://groups.google.com/d/optout> https://groups.google.com/d/optout. -- For group guidelines, visit <http://www.rbase.com/support/usersgroup_guidelines.php> http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to <mailto:[email protected]> [email protected]. For more options, visit <https://groups.google.com/d/optout> https://groups.google.com/d/optout. -- For group guidelines, visit <http://www.rbase.com/support/usersgroup_guidelines.php> http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to <mailto:[email protected]> [email protected]. For more options, visit <https://groups.google.com/d/optout> https://groups.google.com/d/optout. -- For group guidelines, visit <http://www.rbase.com/support/usersgroup_guidelines.php> http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to <mailto:[email protected]> [email protected]. For more options, visit <https://groups.google.com/d/optout> https://groups.google.com/d/optout. -- For group guidelines, visit <http://www.rbase.com/support/usersgroup_guidelines.php> http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to <mailto:[email protected]> [email protected]. For more options, visit <https://groups.google.com/d/optout> https://groups.google.com/d/optout. -- For group guidelines, visit <http://www.rbase.com/support/usersgroup_guidelines.php> http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to <mailto:[email protected]> [email protected]. For more options, visit <https://groups.google.com/d/optout> https://groups.google.com/d/optout. -- For group guidelines, visit <http://www.rbase.com/support/usersgroup_guidelines.php> http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to <mailto:[email protected]> [email protected]. For more options, visit <https://groups.google.com/d/optout> https://groups.google.com/d/optout. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
-- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
