T Sql code for Access Update query

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 tblHospLineFinal INNER JOIN tblCodes ON
tblHospLineFinal.RemarkCode = tblCodes.CodeID SET
tblHospLineFinal.RemarkDescript = tblCodes!CodeDescription;

thanks
bobh.

Re: T Sql code for Access Update query

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 ***

Re: T Sql code for Access Update query

On Nov 5, 10:18 am, bobh wrote:
> 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 tblHospLineFinal INNER JOIN tblCodes ON
> tblHospLineFinal.RemarkCode = tblCodes.CodeID SET
> tblHospLineFinal.RemarkDescript = tblCodes!CodeDescription;
>
> 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.

Re: T Sql code for Access Update query

On Nov 5, 2:52 pm, Technolust wrote:
> On Nov 5, 10:18 am, bobh wrote:
>
> > 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 tblHospLineFinal INNER JOIN tblCodes ON
> > tblHospLineFinal.RemarkCode = tblCodes.CodeID SET
> > tblHospLineFinal.RemarkDescript = tblCodes!CodeDescription;
>
> > 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?

Re: T Sql code for Access Update query

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.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourServer;Database=yourDB;Trusted_Connection=Yes"

cmd.ActiveConnection.CursorLocation = adUseClient

cmd.CommandType = adCmdText

'--this is the Tsql version of your Access update query
strSql = "UPDATE tblHospLineFinal SET
t1.RemarkDescript = t2.CodeDescription
FROM tblHospLineFinal t1 JOIN tblCodes t2 ON t1.RemarkCode = t2.CodeID"

cmd.CommandText = strsql
cmd.Execute

Rich

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

Re: T Sql code for Access Update query

On Nov 5, 2:52 pm, Technolust wrote:
> On Nov 5, 10:18 am, bobh wrote:
>
> > 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 tblHospLineFinal INNER JOIN tblCodes ON
> > tblHospLineFinal.RemarkCode = tblCodes.CodeID SET
> > tblHospLineFinal.RemarkDescript = tblCodes!CodeDescription;
>
> > 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.ExpensesAccounts.CommonName,
dbo.ExpensesTransactions.DebitAccountName
FROM dbo.ExpensesAccounts
INNER JOIN dbo.ExpensesTransactions
ON dbo.ExpensesAccounts.AccountID =
dbo.ExpensesTransactions.DebitAccountID

To Update the DebitAccountName in ExpensesTransactions I would just
run a simple Update
UPDATE View1 SET DebitAccountName = CommonName

In MS-SqlServer this should update the ExpensesTransactions Table.

Re: T Sql code for Access Update query

On Nov 5, 1:18 pm, bobh wrote:
> 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 tblHospLineFinal INNER JOIN tblCodes ON
> tblHospLineFinal.RemarkCode = tblCodes.CodeID SET
> tblHospLineFinal.RemarkDescript = tblCodes!CodeDescription;
>
> thanksbobh.

for anyone who's interested this is what worked

UPDATE tblHospLineFinal
SET tblHospLineFinal.RemarkDescript = tblCodes.CodeDescription
from tblHospLineFinal, tblCodes where tblHospLineFinal.RemarkCode =
tblCodes.CodeID

bobh.