Update Query using ADP 1

[drj3320]

I was wondering why, when using the Access Project strictly as a front-end, A. you cannot specify more than 1 table in the grid pane?, and B. how, using the ADP as front-end only (not SQL view), can you use a column name from another table as your update value?

 

[BigJimSlade]

I would like to bump this as I have the same question.

Thanks!

Big Jim

 

[lupins46]

To create the equivalent of an mdb update query you must create a new storedprocedure.
Ignore the tables dialog(close it).
Show the sql view.

Use syntax such as:
UPDATE dbrders
SET shipname = dbo.orders1.lastname
FROM dbo.orders INNER JOIN
dbo.orders1 ON dbo.orders.orderid = dbo.orders1.employeeid

Check the syntax. Save the stored procedure. Run it.
Make sure you have a copy/backup of your data first.

 

[jgoodman00]

I always prefer to write my update statements in QA. If you make a mistake in Access, it normally just gives 'ADO Error'. If you make the same mistake in QA, it gives you the proper error message.

Personally, I love using Access to create select statements, but I prefer to write data-modification statements in QA for the reason stated above. You can also layout your SQL as you wish in QA; Access reformats it which can make it harder to read.

James Goodman MCSE, MCDBA

http://www.j.goodman.dsl.pipex.com

 

[psimon88]

Hello

I also am new to ADPs and stored procedures. I have been doing app development with Access MDB files for the last seven years.

So, am I to understand that SPs can more or less do the same things as the action queries of MDBs? I found a bunch of sites on the web. Any recommendations I would appreciate.

Thanks.

ps

 

[jgoodman00]

Yes that is largely correct.

I cannot really think of any good online recommendations, but the Inside SQL Server book by Kalen Delaney is excellent. I find myself using it for reference frequently and always keep a copy of the eBook on my laptop.

James Goodman MCSE, MCDBA

http://www.j.goodman.dsl.pipex.com