Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2007
    Posts
    18

    Unanswered: Access Queries are converted in to Sql server tables....

    HI,
    I have upsize my access to sql server. During upsizing i found some bugs and fixed that.
    But now i got a strange bug. While conversion Sql server converted all my Access queries in to the tables.
    and you know tables are just data. Unlike queries they are unable to dynamically fetch the data from different tables.
    and it happened to my most of the queries.
    I tried to convert those queries in stored procedure but i m having "Filter cannt applied to stored procedure".

    Is ther any way i could get rid of this prb..
    Thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have you thought about creating views or the SQL?
    In your access db you can open a query in SQL view, and borrow the code from there.

    Then it's a simple case of
    Code:
    CREATE VIEW <viewname>
    AS
    <insert SQL>
    GO
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Are you converting the mdb into an ADP while upsizing? This sounds like something similar for an ADP. Upsizing normal Access tables to SQL Server tables shouldn't be converting queries into tables unless you've told it to do this for an ADP.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Jul 2007
    Posts
    18
    yes i m upsizing mdb in adp...
    and also tried to create view.. but it doesn't work either.It didnt ask any thing it just converted them in table

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by doosra
    yes i m upsizing mdb in adp...
    and also tried to create view.. but it doesn't work either.It didnt ask any thing it just converted them in table
    I doubt this very much... though I admit I have not used the upsizing wizard.

    What does this return:
    Code:
    
    SELECT name
    , xtype
    FROM dbo.sysobjects
    WHERE name='MyQueryName'
    
    Last edited by pootle flump; 08-22-07 at 11:24.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jul 2007
    Posts
    18
    All of queries are in tables..
    So
    doing
    select name from table
    will return the row. But that could nt anyhow give you the row you just entered.
    coz quries were combination of tables and now those are tables itself..

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What was the result of the query I posted?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jul 2007
    Posts
    18
    The result was
    'U'

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by doosra
    The result was
    'U'
    Then it is doing just what you say and I am amazed. Sorry - that's the limit to how I can help - as I say I have no experience of the upsizing wizard. I was pretty certain you were mistaken but clearly not.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jul 2007
    Posts
    18
    No pootle i m not mistaken.
    I think you didnt understand my problem. Ok no prb. let me repeat once more.
    My all queries have now been converted into tables(static). By no means i m able to get the new updated values from those tables/queries.
    is there any way that i could transfer my queries as queries or views or storedprocedure.
    or do i have to manually write the code for all of them.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by doosra
    do i have to manually write the code for all of them.
    See post #2
    George
    Home | Blog

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    In the last step of the upsizing, it asks you what you want to do with the newly upsized SQL Server tables. Are you selecting "Do Nothing" (or something to that effect) or are you selecting "Link the tables into my application" (or something to that effect?) Try selecting "Do Nothing" in the final step of the upsizing (I can't remember the exact wording but it's the last question asked in the upsizing wizard.) Not sure if this will make a difference but I always select the "Do Nothing" in the last step of the upsizing. I then "manually" link in the tables after I've created an ODBC DSN.
    Last edited by pkstormy; 08-22-07 at 22:39.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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