sql server query to access sql help

Fordraiders
Fordraiders used Ask the Experts™
on
ACCESS 2010
sql server query to access -  linked tables

i'm trying to update a table , it keeps telling me my FROM statement is not correct ?
this query is from sql server....putting it in access..
 update fix
      set fix.[Aligned CSM/MAM] = nata_use.[Aligned CSM/MAM]
    , fix.[Director Name]= nata_use.[Director Name]
        , fix.[am_arm_racf]= nata_use.[am_arm_racf]
    , fix.[director_dsm_racf] = nata_use.[director_dsm_racf]
    , fix.[NSC_Segment] = nata_use.[NSC_Segment]
  FROM [ss_program_workflow].[dbo].[t_nsc_trackcode_assigned] fix
  ,[ss_program_workflow].[dbo].[t_nsc_trackcode_assigned] nata_use
  ,[ss_program_workflow].[dbo].[t_nsc_trackcode_reassign_overrides] ntra
  where 1 = 1
  and fix.[Track_code] = @curr_track   '' from text
  and ntra.[Current Track_Code] = @curr_track  ' from text
  and nata_use.[Track_Code] = @realign_to_track  ' to text

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
I see three tables in your FROM clause but I don't see two JOIN .. ON clauses stating how those tables are related.

Author

Commented:
jim.
“dbo_t_nsc_trackcode_reassign_overrides”
Realign_Using_Trackcode
Current Track_Code

To
“dbo_t_nsc_trackcode_assigned”
Track_code

join
Current Track_Code = Track_code

fordraiders
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Replace the ???'s with whatever columns relate these tables..
UPDATE fix
SET 
	fix.[Aligned CSM/MAM] = nata_use.[Aligned CSM/MAM], 
	fix.[Director Name]= nata_use.[Director Name], 
	fix.[am_arm_racf]= nata_use.[am_arm_racf], 
	fix.[director_dsm_racf] = nata_use.[director_dsm_racf], 
	fix.[NSC_Segment] = nata_use.[NSC_Segment]
FROM [ss_program_workflow].[dbo].[t_nsc_trackcode_assigned] fix
   JOIN [ss_program_workflow].[dbo].[t_nsc_trackcode_assigned] nata_use ON fix.??? = nata_use.???
	JOIN [ss_program_workflow].[dbo].[t_nsc_trackcode_reassign_overrides] ntra ON nata_use.??? = ntra.???
WHERE 
	fix.[Track_code] = @curr_track AND 
	ntra.[Current Track_Code] = @curr_track AND
	nata_use.[Track_Code] = @realign_to_track  

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
jim this is my attempt in access  (no joins yet)
strsql_sql = ""
                    strsql_sql = "UPDATE [dbo_t_nsc_trackcode_assigned]" & vbCrLf
                    strsql_sql = strsql_sql & " SET [dbo_t_nsc_trackcode_assigned].[Aligned CSM/MAM] = [dbo_t_nsc_trackcode_assigned].[Aligned CSM/MAM]" & vbCrLf
                    strsql_sql = strsql_sql & " ,[dbo_t_nsc_trackcode_assigned].[director_dsm_racf] = [dbo_t_nsc_trackcode_assigned].[director_dsm_racf] " & vbCrLf
                    strsql_sql = strsql_sql & " ,[dbo_t_nsc_trackcode_assigned].[am_arm_racf] = [dbo_t_nsc_trackcode_assigned].[am_arm_racf]" & vbCrLf
                    strsql_sql = strsql_sql & " ,[dbo_t_nsc_trackcode_assigned].[director_dsm_racf] = [dbo_t_nsc_trackcode_assigned].[director_dsm_racf]" & vbCrLf
                    strsql_sql = strsql_sql & " ,[dbo_t_nsc_trackcode_assigned].[NSC_Segment] = [dbo_t_nsc_trackcode_assigned].[NSC_Segment] " & vbCrLf
                    strsql_sql = strsql_sql & " FROM [dbo_t_nsc_trackcode_assigned] AND [dbo_t_nsc_trackcode_reassign_overrides] " & vbCrLf
                    strsql_sql = strsql_sql & " WHERE 1 = 1 " & vbCrLf
                    strsql_sql = strsql_sql & " AND [dbo_t_nsc_trackcode_assigned].[Track_Code] = '" & txtfrom & "' " & vbCrLf
                    strsql_sql = strsql_sql & " AND [dbo_t_nsc_trackcode_reassign_overrides].[Current Track_Code] = '" & txtfrom & "' " & vbCrLf
                    strsql_sql = strsql_sql & " AND [dbo_t_nsc_trackcode_assigned].[Track_Code] = '" & txtto & "';"
                    CurrentDb.Execute strsql_sql, dbSeeChanges

Open in new window

Author

Commented:
error on update
I'm trying to get the query to work in access


here is the error
"FROM [dbo_t_nsc_trackcode_assigned] AND [dbo_t_nsc_trackcode_reassign_overrides]"

That is wrong, you cannot use AND after FROM, you are MIXING the where clause with the FROM,
maybe just change your AND with a comma
Distinguished Expert 2017

Commented:
If you don't know how to write Access syntax (it really isn't much different from T-SQL), then use the query designer.  Paste in the select clause, the from clause with no joins in SQL View.  Switch to QBE view and draw the join lines.  Convert the query type to update and choose the fields in the update to line.

I don't know what Access' rule is (I don't have it loaded to check) but you might need "as" in front of each Alias for the tables.
Consulting
Distinguished Expert 2017
Commented:
As Arana noticed, you have an AND statement in your FROM clause.

Either list the tables needed separated by a comma, in that case you'll also need to write the Relationship between your 2 tables in the WHERE clause.
Or perform a JOIN in your FROM clause.
sql = sql & "UPDATE	[dbo_t_nsc_trackcode_assigned])" & vbCrLf
sql = sql & "SET	[dbo_t_nsc_trackcode_assigned].[Aligned CSM/MAM] = [dbo_t_nsc_trackcode_assigned].[Aligned CSM/MAM],)" & vbCrLf
sql = sql & "	[dbo_t_nsc_trackcode_assigned].[director_dsm_racf] = [dbo_t_nsc_trackcode_assigned].[director_dsm_racf],)" & vbCrLf
sql = sql & "	[dbo_t_nsc_trackcode_assigned].[am_arm_racf] = [dbo_t_nsc_trackcode_assigned].[am_arm_racf],)" & vbCrLf
sql = sql & "	[dbo_t_nsc_trackcode_assigned].[director_dsm_racf] = [dbo_t_nsc_trackcode_assigned].[director_dsm_racf],)" & vbCrLf
sql = sql & "	[dbo_t_nsc_trackcode_assigned].[NSC_Segment] = [dbo_t_nsc_trackcode_assigned].[NSC_Segment] )" & vbCrLf
sql = sql & "FROM	[dbo_t_nsc_trackcode_assigned], [dbo_t_nsc_trackcode_reassign_overrides])" & vbCrLf
sql = sql & "WHERE	1 = 1 )" & vbCrLf
    '// Add the Missing relationship here

sql = sql & "  AND	[dbo_t_nsc_trackcode_assigned].[Track_Code] = '" & txtfrom & "' )" & vbCrLf
sql = sql & "  AND	[dbo_t_nsc_trackcode_reassign_overrides].[Current Track_Code] = '" & txtfrom & "' )" & vbCrLf
sql = sql & "  AND	[dbo_t_nsc_trackcode_assigned].[Track_Code] = '" & txtto & "';)"

Open in new window


Also, I do not recommend building an SQL string by concatenation, especially when you concatenate form control's values within it, because it expose your application to SQL injection (wich is a security breach !!).
It is more secure to write a parameterized query.
Sample code:
    '// Write an SQL Query with parameters
Dim sql As String
sql = vbNullString
sql = sql & "PARAMETERS Value Text(255);" & vbCrLf
sql = sql & "UPDATE myTable" & vbCrLf
sql = sql & "SET myColumn = [Value];" & vbCrLf

Dim db As DAO.Database
Set db = CurrentDb

    '// Create a queryDef object
Dim qd As DAO.QueryDef
Set qd = db.CreateQueryDef("tempQd", sql)
    '// Fill up parameters
qd.Parameters("Value") = "blablabla"

    '// Execute the query
qd.Execute dbSeeChanges

    '// Cleanup
qd.close
db.QueryDefs.Delete qd.Name

Open in new window

Additional bonus:
You won't have to struggle when users enter values containing single quotes, double quotes or a mix of them.

Side notes:
Give up hungarian notation, it provide nothing usefull.

Author

Commented:
THANKS ALL

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial