sql server query to access sql help

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

LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
I see three tables in your FROM clause but I don't see two JOIN .. ON clauses stating how those tables are related.
FordraidersAuthor 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 DudeCommented:
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

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

FordraidersAuthor 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

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


here is the error
aranaCommented:
"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
PatHartmanCommented:
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.
Fabrice LambertConsultingCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
THANKS ALL
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.