Source language: Translate to:

Help With SQL Code and Access Database

Questions about using NeoBook's scripting language

Moderator: Neosoft Support

Help With SQL Code and Access Database

Postby Dawn » Wed Dec 19, 2012 12:50 pm

What I’m trying to do is get the field from the Audits table named AuditID to automatically carry over into the Audits Details table in the AuditID field


Name of Access Database: AUDITS
1st Table Name: Audits with the fields:
AuditID (Primary Key)
PlantLocation
Shift
Area
AuditDate
AuditTime

2nd Table: AuditDetails with the fields
DetailID (Primary key)
AuditID
RiskFactor
BCFactor
BCDesc
ESResolution


Below is the code:

"AUDITS" "UPDATE* "AuditDetails" INNER JOIN "Audits" ON AuditDetails.AuditID = Audits.AuditID Set AuditDetails.AuditID
= Audits.AuditID, AuditDetails.sAuditID = Audits.AuditID WHERE Audits.AuditID = "[AuditID]"
Last edited by Dawn on Thu Dec 20, 2012 1:51 pm, edited 1 time in total.
Dawn
 
Posts: 4
Joined: Wed Dec 12, 2012 5:51 am

Re: Help With SQL Code and Access Database

Postby Neosoft Support » Thu Dec 20, 2012 11:34 am

Have you tried executing those SQL commands with DBPro's dbpExecSQL action?

What type of field is AuditID?
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5603
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: Help With SQL Code and Access Database

Postby Dawn » Thu Dec 20, 2012 1:50 pm

I have tried to use that but I keep getting a missing quotations error. The AuditID field is the primarykey in the table named Audits.
Dawn
 
Posts: 4
Joined: Wed Dec 12, 2012 5:51 am

Re: Help With SQL Code and Access Database

Postby Neosoft Support » Thu Dec 20, 2012 5:39 pm

Can you post the dbpExecSQL code you're using?
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5603
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: Help With SQL Code and Access Database

Postby Dawn » Sat Dec 22, 2012 8:06 am

"AUDITS" "UPDATE* "AuditDetails" INNER JOIN "Audits" ON AuditDetails.AuditID = Audits.AuditID Set AuditDetails.AuditID
= Audits.AuditID, AuditDetails.sAuditID = Audits.AuditID WHERE Audits.AuditID = "[AuditID]"
Dawn
 
Posts: 4
Joined: Wed Dec 12, 2012 5:51 am

Re: Help With SQL Code and Access Database

Postby Neosoft Support » Wed Dec 26, 2012 2:27 pm

I'm not a SQL expert and some of those commands can be pretty difficult to get right. However, since double quotes are used as delimiters for NeoBook's scripting language, you can't use them in your SQL commands. Instead of double quotes, try using single quotes. For example:

dbpExecSQL "AddrBook" "SELECT * FROM Contacts WHERE State = 'NY'" ""
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5603
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: Help With SQL Code and Access Database

Postby Dawn » Thu Jan 10, 2013 8:23 am

Looks as though I'm stuck in neutral on this! I can't get anything to work.
Dawn
 
Posts: 4
Joined: Wed Dec 12, 2012 5:51 am

Re: Help With SQL Code and Access Database

Postby Neosoft Support » Thu Jan 10, 2013 2:10 pm

You could manually populate the AuditDetails table. For example:

SetVar "[Audit.AuditDetails.DetailID]" "[Audit.Audit.AuditID]"

This will copy the contents of the Audit table's current record AuditID field to the AuditDetail table's current record DetailID field.

I'm sure it's possible to get your SQL code to work also, but without actually having access to your database, pub file, etc. it's difficult for us to make any specific suggestions.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5603
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA


Return to NeoBook Action Commands

Who is online

Users browsing this forum: No registered users and 0 guests