Solved

From Access to SQL Server

Posted on 2000-03-23
7
171 Views
Last Modified: 2010-05-02
I have an update statement which works in Access. Now I am moving to SQL Server and  trying to modify the statement, but so far without luck. What am I doing wrong?

Here the Access statement:

 UPDATE MyTable INNER JOIN YourTable ON MyTable.ID = YourTable.ID SET MyTable.NAME = [YourTable]![NAME], MyTable.ADDRESS = [YourTable]![ ADDRESS]

Here my new modified for SQL Server statement.

UPDATE MyTable
SET MyTable.NAME = YourTable.NAME, MyTable.ADDRESS = YourTable. ADDRESS
WHERE MyTable.ID = YourTable.ID

Thanks.
0
Comment
Question by:andr
  • 4
  • 3
7 Comments
 
LVL 1

Accepted Solution

by:
ndb earned 50 total points
ID: 2651209
Try:

UPDATE MyTable
SET MyTable.NAME = YourTable.NAME, MyTable.ADDRESS = YourTable. ADDRESS
FROM MyTable, YourTable
WHERE MyTable.ID = YourTable.ID

0
 

Author Comment

by:andr
ID: 2653425
Thank you ndb. That worked.
The only problem I have now - when I run it in the Sql server - everything is fine, but when I am trying to run from VB it gives me message "YourTable is not an object's name".
If you have an answer I will post it as a separate question(I have 30 points left at this time).
0
 
LVL 1

Expert Comment

by:ndb
ID: 2653569
Try to use:

UPDATE MyTable
SET MyTable!NAME = YourTable!NAME, MyTable!ADDRESS = YourTable!ADDRESS
FROM MyTable, YourTable
WHERE MyTable!ID = YourTable!ID





0
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

Author Comment

by:andr
ID: 2653905
No, sql server doesn't recognize this syntax.
0
 
LVL 1

Expert Comment

by:ndb
ID: 2654018
What do you use in VB to connect to your database ... ODBC or OLE DB?
0
 

Author Comment

by:andr
ID: 2654508
I use OLE DB(ADO, UID = sa, no password)
0
 

Author Comment

by:andr
ID: 2654853
I found the problem - I was connected to old database. Thans for your help.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question