| Author |
Topic |
|
jennypretty
Yak Posting Veteran
96 Posts |
 Posted - 2004-10-05 : 11:13:35
|
| I have an Access db with 20 queries, and I 've been working on converting this db to sql server. I created all sql table schemas and working on creating sql views for each access query. This consumes lots of time. So, my question is:Is there any way to convert Access queries to SQL server views? (a wizard maybe?)thanks.The stupid question is the question you don't ask.www.single123.com |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-10-05 : 11:37:58
|
| I hate to be contrary... but here I go:Why would you convert a set of access queries into a like series of views? Part of the reason SQL is better than access is that it can do stored procedures. This means that it has a bit more power than a series of access queries. When my place of work converted from access to SQL we had some so-called SQL experts come and try to convert our 20 & 30 query long access reports. The just create a series of stored procedures that exactly mimic'd the access original. Some of the same queries now run as single select statements in stored procedures that are relatively simple...Point: Don't do it this way. Write the report in SQL so that the result data matches, not the method.Corey |
 |
|
|
Ledell
SQL NASCAR Parsing Chick
107 Posts |
Posted - 2004-10-05 : 12:54:14
|
| For those of us that are used to Access, would you be so kind as to briefly outline how to create a stored procedure that would do that? I have been working in SQL for a couple of years now and as much as I hate to admit it (cuz I know Rob is going to read this), I find that I am still using SQL like I used Access. I have not been successful in creating a series of store procedures that could do what I did in Access.*************************Got some code from Rob. Can anyone help? |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-10-05 : 13:16:19
|
| Because Access is easily accessible, anyone can use it.That's a good and a bad thing.Porting an access application to sql server would be pretty staightforward IF (and this is a HUGE if) the original design was built "correctly" (lots of debatable points in that statement).Most of them, however, are not. Making a straight upgrade difficult to impossible (I have seen more than my share, and have always invoked to law of rewrite).BUT, you only say you have 20 queries. That is a good and managable thing.What's the most complicated query. Does it have queries nested upon queries?The absoulte worst query I saw was about 13 levels of nesting with 4 separate brances...And I was able to, for kicks, rewite it as a sql view.Stored Procedure or view the process would be the same (right 7th?). In any case, it took quite a while to do. Mostly because I had to understand the logic behind a developer who would do something like that, AND at the same time, remove ineffeciencies due to lack of knowledge. (For example, in Access the easiest way to get rid up dup rows is to GROUP BY everything...this is very bad).So...all that said. 20 Queries is managable. Describe to us the most complicated query.Provide us with the DDL of the tables, the actual SQL Query DML and maybe some sample data.The biggest problem with Access is the way the data is handled.The correct way (MOO) is to connect, get he data, move it to an array, and disconnect.You need to get in and out as fast as you can.Brett8-) |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-10-05 : 13:22:14
|
To create a stored procedure:Create Procedure dbo.MyProcedure @param1 int, @param2 varchar(100)As Select blah from myTable where col1 = @param1 and col2 = @param2Go This is a basic example.What I was really refering to:Use the advantages that SQL has over access. Subselects, or derived tables, can be extremely powerful. You can also create functions to do simple calculations (although if you are concerned about speed it may not be the best route). You can build functions that return recordsets.It is hard for me to explain, as I had to learn this over time by trial and error. If you wanted to give a small scenario (5 queries or so), I'm sure I (and many here) could give you some tips on how to write this for SQL, not just in SQL.Corey |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-10-05 : 13:36:53
|
I think I partly misread the original topic, but I think that my last comment is still relevant. As far as stored procedures & views, I am sure the process of development would be the same, but I was never formally 'introduced' to views (our resident SQL server expert turned out to know very little) and I still don't use them very often... Ultimately, as Brett said, the best thing is to determine the desired logic or set of rules. Then write that logic with looking at the original queries, an thus avoid the original inefficiences (or at least some of them  )Corey |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2004-10-05 : 14:08:37
|
| If you have access 2000 or above you can still use it to write SPs instead of Query's.Create .Adp Link to Sql DBUse GUI Interface to write SPs instead of querys. use Sp for form or report.I do it all the time.JimUsers <> Logic |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-10-05 : 15:04:18
|
| Ya know...I never created a project before....so I gave it a whirl..I try an connect to one of my servers and I get an erro that says"Overflow"That's it....Brett8-) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-10-05 : 15:30:55
|
| Wow....I just created a project...created a form on employees in northwindstarted to update a record in a form, and left it in edit mode..Went to QA and performed an update on that row...no locking..The tried to apply the update in Access, and it flagged me, telling me the data had changed.1. How did it know2. what is snapshot(updates allowed)?How come there isn't any locking?Is this a project thing?Does it work differently than regular access and ODBC?Brett8-) |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-10-05 : 15:44:44
|
Most likely, it acts more like a web form... get the data, then close the connection. When you try to save the record, it verifies the original values and if they match then update, else notify...I've never messed with that part of access either though... just not really a fan (i love web based!!)  Corey |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2004-10-05 : 16:25:43
|
| couple of Items. If your going to use access you must have a primary Key field or you cannot update.Also form does not update row untill you go to next record or close form.Set Record locking on form open if you do not want anyone else jerking with the record Or you could save record on update-note field-referesh-return to field.JimUsers <> Logic |
|
|
|
|
HOW TO CONVERT Access queries to SQL Server Views?