Post reply

Converting Access Action Queries to Procedures and Views

  • September 27, 2004 at 5:34 pm

    #162373

    I am new to SQL Server environment and I have a bunch of databases that I have upsized from Access. I have lots and lots of Action queries and it will take me a very long time to rewrite all of them to procedures and views from scratch especially since I am new at this. Is there a tool or way to automatically convert these queries from Jet? I would appreciate any feedback I can get.

    Thanks a bunch!

  • September 28, 2004 at 2:19 am

    #524591

    Don't forget that it isn't just action queries that need to be converted to procs.  Select queries that accept parameters will also need conversion.

    I seem to remember a package called ProcBlaster being useful in rapid development of stored procedures.

    LinkedIn Profile

  • September 28, 2004 at 7:40 am

    #524630

    The first time I used the wizard the most the queries became function queries and they did not work. I then opened Access and connected to the SQL server has a project. Then I cut and pasted my old queries into a new view or stored procedure. I found that some of my fields names were needed to be changed such as date and so on. From here I tested the view and stored procedure. Time consuming, but I learned a lot about SQL databases using queries that worked in Access and not In SQL.

    Jim

     

  • September 29, 2004 at 9:40 am

    #524796

    An alternative is to keep the Access database simply as your application front-end and use linked tables to point to the new SQL Server tables (that you imported from this same Access database)  and then there is no need to convert your Access queries, simply point to the linked tables.

  • September 30, 2004 at 1:39 am

    #524871

    I would plan to convert the queries over time because Access can cause huge locking problems.

    Someone on this site recommended using pass thru queries but that means converting your queries in any case.  If you are going to go the pass thru route then it is probably better to go the whole hog and convert to stored procedures in any case, even if you keep access as your front end.

    LinkedIn Profile

  • September 30, 2004 at 9:25 am

    #524928

    I agree with David.  If you're going to convert the Access queries to be SQL Server specific to allow for pass-thru queries, you might as well simply take this SQL statement and add it to a stored proc in SQL Server.  Then in the pass-thru, call the stored proc instead.

  • October 3, 2004 at 7:17 am

    #525132

    may be i am the guy who suggest path-through queries

    I use path through in dealing with reports .. that open frequently changed/ inserted data tables  .. so i can put [with (nolock)] .. and no locks is added on tables .. i use path-through to ceate dynamic reports

    there is aother wonderful solution .. is to make Views on all tables ... and in views put [with (nolock)] .. and you can name the view (TableName)_View

    and then link these views in your Access (they will appear as tables) .. and don't forget to choose PK in access when you link them

    then you can use them in your access queries as a regular tables ... and you get benifits that there is No Locks on server

    i hope this help u


    Alamir Mohamed
    Alamir_mohamed@yahoo.com

  • October 3, 2004 at 7:19 am

    #525133

    by the way, I use this last option in showing data in forms and in some reports


    Alamir Mohamed
    Alamir_mohamed@yahoo.com

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply