HELP! UPDATE command tsql syntax and sql access syntax

[ges via AccessMonster.com]

I have upsized access to sql2005 server. Access front end. I have an open
form for user to type in data to update the table link to sql2005 server.
I have to re-write the vba code in access form to update tables that's link
to sql server.

In sql server (tsql) I use syntax as follow:
UPDATE table1
SET field1 = 'x',
field2 = 'y',
field3 = 'z'
FROM table2 INNER JOIN table1
ON table2.ID = table1.ID
WHERE (table2.Account ='97') AND (table2.FileType ='Sales')

I can execute the update above in sql server 2005 with no problem, the row
was updated fine. But
it give me error when I run in thru opened form vba in Access. it said there
is syntax error (missing operator in 'z' and FROM table 2 INNER join table 1)

I also tried the following syntax in access query:
UPDATE table1
INNER JOIN table2
ON table1.ID = table2.ID
SET field1 = 'x',
field2 = 'y',
field3 = 'z'
WHERE (table2.Account ='97') AND (table2.FileType ='Sales')

It also give me the same error.

Anyone can help me with the syntax that will work in access also work in sql
server?
Thanks in advance for any input.

Ges

 

[Bob Barrows [MVP]]

I have upsized access to sql2005 server. Access front end. I have an
open form for user to type in data to update the table link to
sql2005 server.
I have to re-write the vba code in access form to update tables
that's link to sql server.

In sql server (tsql) I use syntax as follow:
UPDATE table1
SET field1 = 'x',
field2 = 'y',
field3 = 'z'
FROM table2 INNER JOIN table1
ON table2.ID = table1.ID
WHERE (table2.Account ='97') AND (table2.FileType ='Sales')

I can execute the update above in sql server 2005 with no problem,
the row was updated fine. But
it give me error when I run in thru opened form vba in Access. it
said there is syntax error (missing operator in 'z' and FROM table 2
INNER join table 1)

I also tried the following syntax in access query:
UPDATE table1
INNER JOIN table2
ON table1.ID = table2.ID
SET field1 = 'x',
field2 = 'y',
field3 = 'z'
WHERE (table2.Account ='97') AND (table2.FileType ='Sales')

It also give me the same error.

Really? it looks correct to me ... the best thing you can do is fire up the
query builder, create a query using the Design View gui that does what you
want, then switch t oSQL View to see the generated sql statement.

 

[Lord Kelvan]

thats what i thought bob but i think the problem isnt the query it the
the real data he is putting into the query

ges
is table2.Account a numeric field if so then remove the ' around the
97 and if field 3 is numeric you will most likly have to do the same.
That is the only guess i have without seeing the real query you are
trying to use

Regards
Kelvan

 

[Bob Barrows [MVP]]

thats what i thought bob but i think the problem isnt the query it the
the real data he is putting into the query

ges
is table2.Account a numeric field if so then remove the ' around the
97 and if field 3 is numeric you will most likly have to do the same.
That is the only guess i have without seeing the real query you are
trying to use

I think that is one of the things he would have discovered by using the
query builder ...

 

[ges via AccessMonster.com]

Bob & Kelvan,
Thanks for the input. I use the query designer in sql server, actually the
query syntax is correct, when I upsize the database from access to sql server
2005, I did not delete the link and re-link the table. Once I do that the
query work.

But I do have another problem with import button. I try to update the import
AccountIDAuto in the import table. code as follow:

Sub UpdateAcctIDAutoINtblImport(dbs As DAO.Database)
On Error GoTo Err_UpdateAcctIDAutoINtblImport

Dim strSQL As String
Dim Errorno As String
Dim errormsg As String

'Update the AccIDAuto of the tblImport
strSQL = "UPDATE tblImport INNER JOIN tblAccounts "
strSQL = strSQL & "ON tblImport.AccountKey=tblAccounts.AccountKey "
strSQL = strSQL & "SET tblImport.AcctIDAuto = [tblAccounts.AcctIDAuto];"

dbs.Execute strSQL


Exit_UpdateAcctIDAutoINtblImport:
Exit Sub

I got an error as follow:
You must use dbSeeChanges option with open recordset when accessing a sql
server table that has and Identity Column.

Since I'm new in sql server 2005, I don't quite understand this error message.
Where to start and how to fix this problem.
Any idea?

Thanks,
Ges

thats what i thought bob but i think the problem isnt the query it the
the real data he is putting into the query

[quoted text clipped - 4 lines]

That is the only guess i have without seeing the real query you are
trying to use

I think that is one of the things he would have discovered by using the
query builder ...

 

[Bob Barrows [MVP]]

Bob & Kelvan,
Thanks for the input. I use the query designer in sql server,

I meant for you to use the one in Access to get the correct sql syntax.

There are issues with the QA designer in sql server in that it imposes
artificial limits on the queries one can create. Most developers I know
eschew the use of that tool.

But I do have another problem with import button. I try to update
the import AccountIDAuto in the import table. code as follow:

Sub UpdateAcctIDAutoINtblImport(dbs As DAO.Database)
On Error GoTo Err_UpdateAcctIDAutoINtblImport

Dim strSQL As String
Dim Errorno As String
Dim errormsg As String

'Update the AccIDAuto of the tblImport
strSQL = "UPDATE tblImport INNER JOIN tblAccounts "
strSQL = strSQL & "ON tblImport.AccountKey=tblAccounts.AccountKey
" strSQL = strSQL & "SET tblImport.AcctIDAuto =
[tblAccounts.AcctIDAuto];"

dbs.Execute strSQL


Exit_UpdateAcctIDAutoINtblImport:
Exit Sub

I got an error as follow:
You must use dbSeeChanges option with open recordset when accessing a
sql server table that has and Identity Column.

Since I'm new in sql server 2005, I don't quite understand this error
message. Where to start and how to fix this problem.
Any idea?

It's not a sql server error (the "heading" of the error message will
tell you the source of the error) so even if you were a sql server 2005
expert you would have been baffled by this error. This erro came from
either Jet or DAO.

Frankly, i don't understand the error - i don't see a call to
OpenRecordset in your code.

 

[Lord Kelvan]

ummmmm why are you importing data from sql server why dont you just
set up a odbc link table then the data will stay constant and wont
become out of date while you haev the program open

i am sorry i dont know how to set up the odbc link our server tec has
done that for us i only know how to access it

as far as i can tell that error is releating to a simmilar error you
get when updating a table with auto number in access it is basically
telling you to refresh your recordset

as i said look at setting up odbc and link tables rather than
importing

Regards
Kelvan

 

[ges via AccessMonster.com]

1. I did use access sql syntax to update (my previous problem) and it work,
once I deleted the link
and relink the tables thru odbc connection
2. This import button is to be used for importing new file every week from
excell to access. So I'm
not importing file from sql server.
All these files from excell need to be imported to access via link tables
in sql server.
I don't use open recordset in my code, that's why I can't figure out why the
error said I must use dbSeeChanges with openrecordset.

Any one can help? Thanks

Ges

 

[Lord Kelvan]

umm you could always make the excel sperad sheet a linked table as
well then you dont have to worry about importing it