Moving queries to sql server (2007/2003)

Topic

New Reply

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

Reply | Quote

Replies

Tools | Database Utilities | Upsizing Wizard can be used to transfer tables and queries to SQL Server. From the help for the Upsizing wizard dialog:[indent]

---

For SQL Server 2000 databases:

• Select queries that don’t have an ORDER BY clause or parameters are converted to views.
• 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.
• 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.
• 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]

  ---

  [/indent]The help item contains more info about the Upsizing Wizard.

Reply | Quote

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%5B/url%5D
Thanks

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote