Two questions:

1. What is the equivalent of an Access Update Query in MS SQL using Access 2002 in an .ADP (not .MDB)?

I have been working with Access since it first came out and can do a lot with its queries, but now that I am working with MS SQL as the back end and Access 2002 .ADP as the front end, most of what I know seems useless.

What I am trying to do (for starters since I have a lot more to do) is update a field in one table with the corresponding field in another table. Here is the Access SQL:

UPDATE [T-Parameter] INNER JOIN [T-SetupSheetParametersDetail] ON [T-Parameter].ParameterID = [T-SetupSheetParametersDetail].ParameterID SET [T-SetupSheetParametersDetail].ListOrder = [T-Parameter].[ListORder];

But when I use any of the design tools in Access 2002 (.ADP) I don’t see how to even make an update query using the query design tools, the closest I got was to make an update SP with only one table in it and since I need two tables that is not going to work.

2. Can anyone suggest a good book, web site, etc. on query structure or MS SQL syntax specifically for programs going from the Jet to MS SQL as the back end and Access still as the front end. The difference in these queries is really going to kill me!

Thanks!

Michael

__________________
Mitch

Mitch,

I haven't worked with Access to MSSQL, but I know my SQL so I'll take a stab at helping you.

Here's MSSQL query structure that should work for you:

UPDATE [T-SetupSheetParametersDetail]
SET [T-SetupSheetParametersDetail].ListOrder = [T-Parameter].[ListORder]
FROM [T-Parameter]
WHERE [T-Parameter].ParameterID = [T-SetupSheetParametersDetail].ParameterID ;

These kinds of queries always confuse me so I imagine it's particularly harder when you are used to doing it a whole other way.

Peter

Thanks Peter, that did the work.

But I wish I had a better handle of how to use the new Access 2002 query design tools. From what I see now, they don't give me much help except for simple queries, no "Action" queries seem to be supported. That is really going to slow me down!

Thanks for your help.

I have tried your query snippet, but it does not work in my Access. I of course changed the table names and fields appropriately. I always get a syntax error and it highlights on the word "FROM". Here is my update query.

UPDATE tblExpDemo
SET tblExpDemo.Company = dbo_ClinicPlantView.ClinicID
FROM tblClinics dbo_ClinicPlantView
WHERE dbo_ClinicPlantView.EmployerName = tblExpDemo.UDF1 AND dbo_ClinicPlantView.PlantLocation = tblExpDemo.UDF2 AND
dbo_ClinicPlantView.PlantNumber = tblExpDemo.UDF3;

Any help would be appreciated.

Dave

I have tried your query snippet, but it does not work in my Access. I of course changed the table names and fields appropriately. I always get a syntax error and it highlights on the word "FROM". Here is my update query.

UPDATE tblExpDemo
SET tblExpDemo.Company = dbo_ClinicPlantView.ClinicID
FROM tblClinics dbo_ClinicPlantView
WHERE dbo_ClinicPlantView.EmployerName = tblExpDemo.UDF1 AND dbo_ClinicPlantView.PlantLocation = tblExpDemo.UDF2 AND
dbo_ClinicPlantView.PlantNumber = tblExpDemo.UDF3;

Any help would be appreciated.

Dave

You may not have a space in your table name:

Gert