Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Moving queries to sql server (2007/2003)

    Is there a program or rourtine to automatically move queries in Access to views or stored procedures in SQL server?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving queries to sql server (2007/2003)

    Tools | Database Utilities | Upsizing Wizard can be used to transfer tables and queries to SQL Server. From the help for the Upsizing wizard dialog:
    <hr>For SQL Server 2000 databases:
    <UL><LI>Select queries that don't have an ORDER BY clause or parameters are converted to views.
    <LI>Action queries are converted to stored procedure action queries. Access adds SET NOCOUNT ON after the parameter declaration code to make sure the stored procedure runs.
    <LI>Select queries that only reference tables (also called base queries) that use either parameters or an ORDER BY clause are converted to user-defined functions. If necessary, the TOP 100 PERCENT clause is added to a query that contains an ORDER BY clause.
    <LI>Parameter queries that use named parameters maintain the original text name used in the Access database and are converted either to stored procedures or inline user-defined functions.[/list]<hr>
    The help item contains more info about the Upsizing Wizard.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Moving queries to sql server (2007/2003)

    I was using Access 2007 and SQL server express 2005 and it only gives an option for tables not queries that I could find. IS this a change for the'better'? A search in MSFT web site seems to suggest copying the sql statements one by one from Access and pasting them into the Query designed in SQL server. Look here: http://www.microsoft.com/technet/prodtechn...8.mspx?mfr=true
    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving queries to sql server (2007/2003)

    I'm sure others will be able to help you with this. I hardly use SQL Server.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Moving queries to sql server (2007/2003)

    I am curious why? I am about to take on a project of attendance and grades for a school of 180 students. They each take 30 hours of class a week. that means the attendance table will have 180 * 30 * 25 = 135,000 records which I will need to manipulate to provide % of absences. Therefore, I have started thinking about SQL server. Do you think it justifies it? (Maybe this deserves a thread of its own for other people to join in?)
    Thanks

  6. #6
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving queries to sql server (2007/2003)

    Hi,

    Assuming that each year will effectively be just that and that the data in the db can be archived to some degree 138K of rows doesn't sound too bad. I have an db in front of me that has 131K invoice transactions with 50K of products in it, the db backend is sitting at around 190meg - partly due to a very large volume of memo fields that contain document information. This db has been running with this volume on board for the last 2 years with no problems. We produce a heap of stock analysis reporting etc that yes can take a few minutes to run at times but again that is simply due to the complex nature of some of the reports.

    Is the data you collecting annual? Would this data be archived as such or does it need to be available year on year real time? You can always archive the data to a 2nd db then just keep summary data in the main db.

    We have not yet upgrade to SQL backend yet as the db still performs and is responsive enough for the user.

    Cheers
    Tony

  7. #7
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Moving queries to sql server (2007/2003)

    it is annual and could then be summarized and the old data archived, so this sounds like it does not need SQL server
    Thanks

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving queries to sql server (2007/2003)

    Access can handle hundreds of thousands, even millions of records without problems. Queries should be OK, although very complex queries might be slow.
    The bottleneck might be the number of simultaneous users - if you have a handful of people entering/editing data simultaneously, you should be fine, but with ten or more, performance would go down.

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Moving queries to sql server (2007/2003)

    I don't think there is manual way to convert Access queries to SQL views. There are differences between SQL in Access and SQL on SQL server; not huge differences, but just a few little things: like different wild cards, use of single quote to delimit text fields and dates, etc. Plus many of the functions we've come to rely on in Access may not exist on SQL Server (like IIF).

    I've found the best way to proceed is to move your tables to SQL Server, then determine what queries seem to lag and then convert them to pass-through queries in Access. At least for me, it is easier to manipulate them on the Access side than if they are Views on the server.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Moving queries to sql server (2007/2003)

    Going on from what Tony has said, i have a database that is 911Mb in size and one of it/s tables holds 2.4 million records. I have it indexed on MeetingVenue (which is a text field yyyymmddvvv) and doing a filter on this field is immediate, no noticeable pause at all.

Posting Permissions

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