Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2002
    Location
    Montreal, Canada
    Posts
    44

    Unhappy Unanswered: MSAccess Update queries..how to in MSSQL Server

    converting an MSAccess DB to MSSQL Server 2000 and i need to run a few update queries on the data from within MSSQL Server 2000. How would i do this?

    Thanks
    Sammy

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    You can use the "upsizing wizard" to convert an access database to sql server. Once it has been converted, you can run update statements through a user interface called "query analyzer" or "enterprise manager" - query analyzer is preferred. Let me know if you have additional questions.

  3. #3
    Join Date
    Nov 2002
    Posts
    5
    Hi
    Think i have had the same problem as you are having. I found this web page which has been the most helpful up to now but im finding i have to get snippets of info here here and everywhere! seems to be coming along nicely now, so keep Persevering
    Anyways heres the web link hope it helps:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/access2sql.asp

  4. #4
    Join Date
    Dec 2002
    Location
    Montreal, Canada
    Posts
    44

    Still having a problem

    simple Update statements seem to work fine within the Query Analyzer, but it doesn't seems to work when using INNER JOIN's to relate 2 tables. Is there something i'm over looking?

  5. #5
    Join Date
    Nov 2002
    Location
    Glasgow, Scotland
    Posts
    5

    Shoq us the SQL

    Hi,

    Can you post the SQL that you are using so we can see what you are trying to do? A CREATE statement for your two tables, and INSERT statement for them, and the INNER JOIN that you are trying to do would be great.

    Thanks,

    Matt

  6. #6
    Join Date
    Dec 2002
    Location
    Montreal, Canada
    Posts
    44
    Tables are already in MSSQL Server 2000, just trying to update some data, here is one of the Update Queries i am attempting to run


    UPDATE tblDistribution INNER JOIN qryPatternColorFID ON (tblDistribution.StyleNo = qryPatternColorFID.StyleNo) AND (tblDistribution.Pattern = qryPatternColorFID.Pattern) AND (tblDistribution.Color = qryPatternColorFID.Color) SET tblDistribution.patternColorFID = qryPatternColorFID.patterncolorid;


    thanks for your time
    Sammy

  7. #7
    Join Date
    Feb 2002
    Posts
    2,232
    What is the error you receive ? Have you tried the update without the inner join - doing an old sql syntax with a where clause instead ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •