T Sql code for Access Update query

[bobh]

Hi All,
I'm looking for help with what the Transact SQL code is for this
Access Update query sql code. I've been doing Access for some time and
all my back-ends have been jet. I'm just starting with SQLServer back-
ends and I'm wanting to change those queries that can be changed to
pass-thru queries using T SQL were I can.

UPDATE tblHospLineFina l INNER JOIN tblCodes ON
tblHospLineFina l.RemarkCode = tblCodes.CodeID SET
tblHospLineFina l.RemarkDescrip t = tblCodes!CodeDe scription;

thanks
bobh.

[Rich P]

Try something like this:
Update tbl1 set fname = t2.fname from tbl1 t1 join tbl2 t2 on t1.recID =
t2.recID where t1.fname is null

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Technolust]

On Nov 5, 10:18 am, bobh <vulca...@yahoo .comwrote:

Hi All,
I'm looking for help with what the Transact SQL code is for this
Access Update query sql code. I've been doing Access for some time and
all my back-ends have been jet. I'm just starting with SQLServer back-
ends and I'm wanting to change those queries that can be changed to
pass-thru queries using T SQL were I can.

UPDATE tblHospLineFina l INNER JOIN tblCodes ON
tblHospLineFina l.RemarkCode = tblCodes.CodeID SET
tblHospLineFina l.RemarkDescrip t = tblCodes!CodeDe scription;

thanks
bobh.

You can also search for the UPDATE statement with SQL Server
Management Studio help. It will give you information and examples on
how to use the keyword or statement you desire to implement.

[bobh]

On Nov 5, 2:52 pm, Technolust <queenskni...@t echnologist.com wrote:

On Nov 5, 10:18 am, bobh <vulca...@yahoo .comwrote:

Hi All,
I'm looking for help with what the Transact SQL code is for this
Access Update query sql code. I've been doing Access for some time and
all my back-ends have been jet. I'm just starting with SQLServer back-
ends and I'm wanting to change those queries that can be changed to
pass-thru queries using T SQL were I can.

UPDATE tblHospLineFina l INNER JOIN tblCodes ON
tblHospLineFina l.RemarkCode = tblCodes.CodeID SET
tblHospLineFina l.RemarkDescrip t = tblCodes!CodeDe scription;

thanks
bobh.

You can also search for the UPDATE statement with SQL Server
Management Studio help. It will give you information and examples on
how to use the keyword or statement you desire to implement.

Unforutenaly the company I work in will not allow such management
tools to be purchased and installed on pc's...... :( If I requested
that I'd have to give my right arm and left leg plus my first born and
then it would take about six months before I saw anything and they
would Zen it down to my pc and it would not work, most likely. I don't
what to give up that much nor can I wait that long.

do you know what the T SQL code would be?

[Rich P]

Using ADO you can do this (make sure you have a reference to Microsoft
ActiveX Data Object 2.5 Library -- or greater):

Dim cmd As New ADODB.Command, strSql As String

cmd.ActiveConne ction = "Provider=SQLOL EDB; Data
Source=yourServ er;Database=you rDB;Trusted_Con nection=Yes"

cmd.ActiveConne ction.CursorLoc ation = adUseClient

cmd.CommandType = adCmdText

'--this is the Tsql version of your Access update query
strSql = "UPDATE tblHospLineFina l SET
t1.RemarkDescri pt = t2.CodeDescript ion
FROM tblHospLineFina l t1 JOIN tblCodes t2 ON t1.RemarkCode = t2.CodeID"

cmd.CommandText = strsql
cmd.Execute

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[lyle]

On Nov 5, 2:52 pm, Technolust <queenskni...@t echnologist.com wrote:

On Nov 5, 10:18 am, bobh <vulca...@yahoo .comwrote:

Hi All,
I'm looking for help with what the Transact SQL code is for this
Access Update query sql code. I've been doing Access for some time and
all my back-ends have been jet. I'm just starting with SQLServer back-
ends and I'm wanting to change those queries that can be changed to
pass-thru queries using T SQL were I can.

UPDATE tblHospLineFina l INNER JOIN tblCodes ON
tblHospLineFina l.RemarkCode = tblCodes.CodeID SET
tblHospLineFina l.RemarkDescrip t = tblCodes!CodeDe scription;

thanks
bobh.

You can also search for the UPDATE statement with SQL Server
Management Studio help. It will give you information and examples on
how to use the keyword or statement you desire to implement.

I would not do this as I always have the Code Description available by
executing a JOIN. In most cases it's unnecessary to store it twice.
Some would say, "Unwise".

But if in another life I came back as someone less rigorous I would
create a persistent VIEW
eg.
View_1:
SELECT dbo.ExpensesAcc ounts.CommonNam e,
dbo.ExpensesTra nsactions.Debit AccountName
FROM dbo.ExpensesAcc ounts
INNER JOIN dbo.ExpensesTra nsactions
ON dbo.ExpensesAcc ounts.AccountID =
dbo.ExpensesTra nsactions.Debit AccountID

To Update the DebitAccountNam e in ExpensesTransac tions I would just
run a simple Update
UPDATE View1 SET DebitAccountNam e = CommonName

In MS-SqlServer this should update the ExpensesTransac tions Table.

[bobh]

On Nov 5, 1:18 pm, bobh <vulca...@yahoo .comwrote:

Hi All,
I'm looking for help with what the Transact SQL code is for this
Access Update query sql code. I've been doing Access for some time and
all my back-ends have been jet. I'm just starting with SQLServer back-
ends and I'm wanting to change those queries that can be changed to
pass-thru queries using T SQL were I can.

UPDATE tblHospLineFina l INNER JOIN tblCodes ON
tblHospLineFina l.RemarkCode = tblCodes.CodeID SET
tblHospLineFina l.RemarkDescrip t = tblCodes!CodeDe scription;

thanksbobh.

for anyone who's interested this is what worked

UPDATE tblHospLineFina l
SET tblHospLineFina l.RemarkDescrip t = tblCodes.CodeDe scription
from tblHospLineFina l, tblCodes where tblHospLineFina l.RemarkCode =
tblCodes.CodeID

bobh.