Best way to update records in SQL back end 1

[mkirros]

I'm in the process of moving some databases from Access to an SQL Server back end with an access .adp as the front end. I'm new to working with SQL - could some one give me the best way to do the equivalent of an Access Update Query? I have two tables linked by an ID number field, and need to update a value in one table based on a value in the second table. Thanks for any help.

 

[psemianonymous]

There's no good way to do this. Get someone to install the SQL Server Enterprise Manager on your computer so you can use their query builder. Anything less is pain you don't need.

Their query builder looks somewhat similar to the Access query builder; not identical of course, but similar enough to get you started.

As an alternate, you can use the query builder to build an UPDATE query in Access, then go to the "SQL view" and look at the SQL it generates. For simple things they will be identical SQL syntax, for complex things there will be some serious differences. For the record (if you're googling at all) Access uses "JET SQL" and SQL Server uses "T-SQL" or "Transact SQL", and there are differences between these two.


There's no shortcut to learning, but I hope I've helped you find a few tools to make the learning easier. Also, DEFINITELY get Enterprise Manager. At worst it's a $50 purchase for the "SQL Server Developer Edition", at best you have a site license so it won't cost anything.

 

[mkirros]

Thanks - I have Enterprise Manager installed, so I'll look at the query builder there.

 

[addy]

Surely you can just use an Access Update query?

We have an SQL database with over 300,000 records in the main table and about 15 related tables. I use an Access .mdb to manage the information, linking to the SQL tables using ODBC.

Therefore, I open my access front end, create a new query, add the relevant tables, set the update criteria and hit go.