Access to SQL conversion

[Cheryl Dodd]

I am currently converting over an Access database into SQL. I want to leave the existing Forms and Reports in Access. I have converted the tables but the problem I am having is with the Queries which have some sort of reference to a Form so I am after any tips on how to do this.

[David Westmore]

When you do this kind of conversion, all references in queries to local Access objects (e.g. controls in forms, VBA functions which do not have equivalents in Transact SQL, and so on), will have to remain as queries in Access.

It's not as bad as it sounds, you're not obliged to convert all queries into views. In principle, a view should be a bit more efficient. You can still wirte queries that reference tables (or views which you connect "as if" they were tables).

There are obviously a few big drawbacks you need to try to avoid...e.g. if you're selecting records from a very large table using the result of a VBA function applied to one or more fields, you could end up having to read every single record from SQL into Jet just ot pick out, say, 3 or 4. If possible, filtering conditions should be "server side".

However, I have found that Jet and ODBC do a much better job than you would expect e.g. when you filter records on the basis of a control in a form, it will send the value up to SQL to execute the query (I have verified this with Profiler).

Let's say it's an empirical question: you need to try it out to see what the performance is like, and if it's very bad, you may have to rethink the whole technique. But I suspect that your main concern now is getting things up and working fast.

I don't like the technique of putting references to controls in forms in queries, I'm convinced that there are cleaner ways of achieving the same effect, but I suppose that you have to work with the raw material you've got!

Regards

David Westmore


------------
Cheryl Dodd at 10/2/01 4:44:25 AM

I am currently converting over an Access database into SQL. I want to leave the existing Forms and Reports in Access. I have converted the tables but the problem I am having is with the Queries which have some sort of reference to a Form so I am after any tips on how to do this.

[Cheryl Dodd]

Hi David,

With you suggesting about the queries remaining in Access, to be honest, I have noticed that a lot of them still work successfully without having any impact on the performance. The only problem queries are the ones which refer to forms. They still work (to a fashion) - Basically when you select the relevant control, a box appears for you to enter the required code. Whether you enter anything or leave it blank, the correct information appears on the next form once you select OK. Obviously I want to stop the box appearing - This did not happen when the tables were in Access. The relevant form would appear straight away.

As I have been looking into info on Access to SQL conversions, one of the suggestions was to convert the queries into stored procedures. I have tried this with one query which executes fine in Query Analyzer but then how do you refer to it in Access ? The other thing is, the query I tested it with does not have any reference to info on a form. I'm sure if I created a stored procedure with reference to a form in Access, it's obviously not going to execute.

So really I'm happy to leave the queries as is in Access but it's knowing the best way to refer to forms.

Cheryl


------------
David Westmore at 10/2/01 7:11:01 AM

When you do this kind of conversion, all references in queries to local Access objects (e.g. controls in forms, VBA functions which do not have equivalents in Transact SQL, and so on), will have to remain as queries in Access.

It's not as bad as it sounds, you're not obliged to convert all queries into views. In principle, a view should be a bit more efficient. You can still wirte queries that reference tables (or views which you connect "as if" they were tables).

There are obviously a few big drawbacks you need to try to avoid...e.g. if you're selecting records from a very large table using the result of a VBA function applied to one or more fields, you could end up having to read every single record from SQL into Jet just ot pick out, say, 3 or 4. If possible, filtering conditions should be "server side".

However, I have found that Jet and ODBC do a much better job than you would expect e.g. when you filter records on the basis of a control in a form, it will send the value up to SQL to execute the query (I have verified this with Profiler).

Let's say it's an empirical question: you need to try it out to see what the performance is like, and if it's very bad, you may have to rethink the whole technique. But I suspect that your main concern now is getting things up and working fast.

I don't like the technique of putting references to controls in forms in queries, I'm convinced that there are cleaner ways of achieving the same effect, but I suppose that you have to work with the raw material you've got!

Regards

David Westmore


------------
Cheryl Dodd at 10/2/01 4:44:25 AM

I am currently converting over an Access database into SQL. I want to leave the existing Forms and Reports in Access. I have converted the tables but the problem I am having is with the Queries which have some sort of reference to a Form so I am after any tips on how to do this.

[David Westmore]

Hi, for some reason for some time I don't get automatic notification of replies, so I only found your message today.

Hmmm, the stored procedure approach: in my humble opinion, this is THEORETICALLY correct, but I would suggest using SPs only when you know you're going to be doing updates or inserts in code (and, if you're using Access, you should be avoiding this as much as possible).

You can refer to an SP defining a pass through query, but I really think this is just distorting the situation. SPs are good when you're doing it in code, especially for inserts, updates, etc. (take a look at "Proc Blaster" on the http://www.lockwoodtech.com/ site, there is a freeware version which is useful for getting into how to use SPs in code).

It's not clear to me what your problem is with the queries that refer to form controls. Are these queries in Access or are they views in SQL? I don't really see why they should work any differently if they're Access queries.

Regards

David Westmore







------------
Cheryl Dodd at 10/2/01 8:46:16 AM

Hi David,

With you suggesting about the queries remaining in Access, to be honest, I have noticed that a lot of them still work successfully without having any impact on the performance. The only problem queries are the ones which refer to forms. They still work (to a fashion) - Basically when you select the relevant control, a box appears for you to enter the required code. Whether you enter anything or leave it blank, the correct information appears on the next form once you select OK. Obviously I want to stop the box appearing - This did not happen when the tables were in Access. The relevant form would appear straight away.

As I have been looking into info on Access to SQL conversions, one of the suggestions was to convert the queries into stored procedures. I have tried this with one query which executes fine in Query Analyzer but then how do you refer to it in Access ? The other thing is, the query I tested it with does not have any reference to info on a form. I'm sure if I created a stored procedure with reference to a form in Access, it's obviously not going to execute.

So really I'm happy to leave the queries as is in Access but it's knowing the best way to refer to forms.

Cheryl


------------
David Westmore at 10/2/01 7:11:01 AM

When you do this kind of conversion, all references in queries to local Access objects (e.g. controls in forms, VBA functions which do not have equivalents in Transact SQL, and so on), will have to remain as queries in Access.

It's not as bad as it sounds, you're not obliged to convert all queries into views. In principle, a view should be a bit more efficient. You can still wirte queries that reference tables (or views which you connect "as if" they were tables).

There are obviously a few big drawbacks you need to try to avoid...e.g. if you're selecting records from a very large table using the result of a VBA function applied to one or more fields, you could end up having to read every single record from SQL into Jet just ot pick out, say, 3 or 4. If possible, filtering conditions should be "server side".

However, I have found that Jet and ODBC do a much better job than you would expect e.g. when you filter records on the basis of a control in a form, it will send the value up to SQL to execute the query (I have verified this with Profiler).

Let's say it's an empirical question: you need to try it out to see what the performance is like, and if it's very bad, you may have to rethink the whole technique. But I suspect that your main concern now is getting things up and working fast.

I don't like the technique of putting references to controls in forms in queries, I'm convinced that there are cleaner ways of achieving the same effect, but I suppose that you have to work with the raw material you've got!

Regards

David Westmore


------------
Cheryl Dodd at 10/2/01 4:44:25 AM

I am currently converting over an Access database into SQL. I want to leave the existing Forms and Reports in Access. I have converted the tables but the problem I am having is with the Queries which have some sort of reference to a Form so I am after any tips on how to do this.

------------
Cheryl Dodd at 10/2/01 4:44:25 AM

I am currently converting over an Access database into SQL. I want to leave the existing Forms and Reports in Access. I have converted the tables but the problem I am having is with the Queries which have some sort of reference to a Form so I am after any tips on how to do this.

[Cheryl Dodd]

At the moment, they are queries within Access97 and not Views although with all the hunting round I've been doing and I've been going round and round in circles, I've decided to try a different approach by trying to utilise an Access 2000 Project instead of an ordinary database. That way aswell as using existing Forms and Reports, I'll be able to use Views and/or Stored Procedures so hopefully this will work and I'll be very HAPPY If not, I haven't got a clue...but I don't want to think negative, I'm determined to get it working as it's becoming more urgent and it's not the only database that I will have to convert. We have a fair few Access97 databases which will need transferring over....Can't wait!

We have both SQL 7 and 2000 so they will eventually be 2000 same as all our servers and workstations when we upgrade our network...some time this century.

Anyway, if I manage to get it working, I'll let you know or if you do hear of any other methodes, PLEEASE let me know...oh and I'll check out that link that you passed on.

Thanks,
Cheryl


------------
David Westmore at 10/3/01 7:18:07 AM

Hi, for some reason for some time I don't get automatic notification of replies, so I only found your message today.

Hmmm, the stored procedure approach: in my humble opinion, this is THEORETICALLY correct, but I would suggest using SPs only when you know you're going to be doing updates or inserts in code (and, if you're using Access, you should be avoiding this as much as possible).

You can refer to an SP defining a pass through query, but I really think this is just distorting the situation. SPs are good when you're doing it in code, especially for inserts, updates, etc. (take a look at "Proc Blaster" on the http://www.lockwoodtech.com/ site, there is a freeware version which is useful for getting into how to use SPs in code).

It's not clear to me what your problem is with the queries that refer to form controls. Are these queries in Access or are they views in SQL? I don't really see why they should work any differently if they're Access queries.

Regards

David Westmore







------------
Cheryl Dodd at 10/2/01 8:46:16 AM

Hi David,

With you suggesting about the queries remaining in Access, to be honest, I have noticed that a lot of them still work successfully without having any impact on the performance. The only problem queries are the ones which refer to forms. They still work (to a fashion) - Basically when you select the relevant control, a box appears for you to enter the required code. Whether you enter anything or leave it blank, the correct information appears on the next form once you select OK. Obviously I want to stop the box appearing - This did not happen when the tables were in Access. The relevant form would appear straight away.

As I have been looking into info on Access to SQL conversions, one of the suggestions was to convert the queries into stored procedures. I have tried this with one query which executes fine in Query Analyzer but then how do you refer to it in Access ? The other thing is, the query I tested it with does not have any reference to info on a form. I'm sure if I created a stored procedure with reference to a form in Access, it's obviously not going to execute.

So really I'm happy to leave the queries as is in Access but it's knowing the best way to refer to forms.

Cheryl


------------
David Westmore at 10/2/01 7:11:01 AM

When you do this kind of conversion, all references in queries to local Access objects (e.g. controls in forms, VBA functions which do not have equivalents in Transact SQL, and so on), will have to remain as queries in Access.

It's not as bad as it sounds, you're not obliged to convert all queries into views. In principle, a view should be a bit more efficient. You can still wirte queries that reference tables (or views which you connect "as if" they were tables).

There are obviously a few big drawbacks you need to try to avoid...e.g. if you're selecting records from a very large table using the result of a VBA function applied to one or more fields, you could end up having to read every single record from SQL into Jet just ot pick out, say, 3 or 4. If possible, filtering conditions should be "server side".

However, I have found that Jet and ODBC do a much better job than you would expect e.g. when you filter records on the basis of a control in a form, it will send the value up to SQL to execute the query (I have verified this with Profiler).

Let's say it's an empirical question: you need to try it out to see what the performance is like, and if it's very bad, you may have to rethink the whole technique. But I suspect that your main concern now is getting things up and working fast.

I don't like the technique of putting references to controls in forms in queries, I'm convinced that there are cleaner ways of achieving the same effect, but I suppose that you have to work with the raw material you've got!

Regards

David Westmore


------------
Cheryl Dodd at 10/2/01 4:44:25 AM

I am currently converting over an Access database into SQL. I want to leave the existing Forms and Reports in Access. I have converted the tables but the problem I am having is with the Queries which have some sort of reference to a Form so I am after any tips on how to do this.

[David Westmore]

I've so far tried very little with ADP development (I've been using ADPs since they came out just as a more friendly way of working with SQL...you know, things like a "Save as..." option on the file menù). I recently wrote a little application just for me and my immediate colleagues for handling SQL jobs (just can't stand that appalling Enterpise Manager interface to the jobs subsystem any more!).

I very much doubt that using an ADP will solve your specific problem. It sounds to me as if there is something terribly small and obvious (obvious once you've found it, of course) that is going wrong.

I would stick with queries in Access MDB, I just can't see why what you're trying to do shouldn't work. I'm sure that I do have some queries somewhere that use this technique and they seem to be working OK.

Oh well, back to the Euro conversions...

Regards

David Westmore

------------
Cheryl Dodd at 10/4/01 6:50:00 AM

At the moment, they are queries within Access97 and not Views although with all the hunting round I've been doing and I've been going round and round in circles, I've decided to try a different approach by trying to utilise an Access 2000 Project instead of an ordinary database. That way aswell as using existing Forms and Reports, I'll be able to use Views and/or Stored Procedures so hopefully this will work and I'll be very HAPPY If not, I haven't got a clue...but I don't want to think negative, I'm determined to get it working as it's becoming more urgent and it's not the only database that I will have to convert. We have a fair few Access97 databases which will need transferring over....Can't wait!

We have both SQL 7 and 2000 so they will eventually be 2000 same as all our servers and workstations when we upgrade our network...some time this century.

Anyway, if I manage to get it working, I'll let you know or if you do hear of any other methodes, PLEEASE let me know...oh and I'll check out that link that you passed on.

Thanks,
Cheryl


------------
David Westmore at 10/3/01 7:18:07 AM

Hi, for some reason for some time I don't get automatic notification of replies, so I only found your message today.

Hmmm, the stored procedure approach: in my humble opinion, this is THEORETICALLY correct, but I would suggest using SPs only when you know you're going to be doing updates or inserts in code (and, if you're using Access, you should be avoiding this as much as possible).

You can refer to an SP defining a pass through query, but I really think this is just distorting the situation. SPs are good when you're doing it in code, especially for inserts, updates, etc. (take a look at "Proc Blaster" on the http://www.lockwoodtech.com/ site, there is a freeware version which is useful for getting into how to use SPs in code).

It's not clear to me what your problem is with the queries that refer to form controls. Are these queries in Access or are they views in SQL? I don't really see why they should work any differently if they're Access queries.

Regards

David Westmore







------------
Cheryl Dodd at 10/2/01 8:46:16 AM

Hi David,

With you suggesting about the queries remaining in Access, to be honest, I have noticed that a lot of them still work successfully without having any impact on the performance. The only problem queries are the ones which refer to forms. They still work (to a fashion) - Basically when you select the relevant control, a box appears for you to enter the required code. Whether you enter anything or leave it blank, the correct information appears on the next form once you select OK. Obviously I want to stop the box appearing - This did not happen when the tables were in Access. The relevant form would appear straight away.

As I have been looking into info on Access to SQL conversions, one of the suggestions was to convert the queries into stored procedures. I have tried this with one query which executes fine in Query Analyzer but then how do you refer to it in Access ? The other thing is, the query I tested it with does not have any reference to info on a form. I'm sure if I created a stored procedure with reference to a form in Access, it's obviously not going to execute.

So really I'm happy to leave the queries as is in Access but it's knowing the best way to refer to forms.

Cheryl


------------
David Westmore at 10/2/01 7:11:01 AM

When you do this kind of conversion, all references in queries to local Access objects (e.g. controls in forms, VBA functions which do not have equivalents in Transact SQL, and so on), will have to remain as queries in Access.

It's not as bad as it sounds, you're not obliged to convert all queries into views. In principle, a view should be a bit more efficient. You can still wirte queries that reference tables (or views which you connect "as if" they were tables).

There are obviously a few big drawbacks you need to try to avoid...e.g. if you're selecting records from a very large table using the result of a VBA function applied to one or more fields, you could end up having to read every single record from SQL into Jet just ot pick out, say, 3 or 4. If possible, filtering conditions should be "server side".

However, I have found that Jet and ODBC do a much better job than you would expect e.g. when you filter records on the basis of a control in a form, it will send the value up to SQL to execute the query (I have verified this with Profiler).

Let's say it's an empirical question: you need to try it out to see what the performance is like, and if it's very bad, you may have to rethink the whole technique. But I suspect that your main concern now is getting things up and working fast.

I don't like the technique of putting references to controls in forms in queries, I'm convinced that there are cleaner ways of achieving the same effect, but I suppose that you have to work with the raw material you've got!

Regards

David Westmore


------------
Cheryl Dodd at 10/2/01 4:44:25 AM

I am currently converting over an Access database into SQL. I want to leave the existing Forms and Reports in Access. I have converted the tables but the problem I am having is with the Queries which have some sort of reference to a Form so I am after any tips on how to do this.

[Cheryl Dodd]

These queries that you're sure you've done using this technique....Find them! Find them! Pleeeaase! I know what you mean about the solution being obvious once you've found it....It's just frustrating trying to find it!

Cheryl



------------
David Westmore at 10/4/01 8:14:46 AM

I've so far tried very little with ADP development (I've been using ADPs since they came out just as a more friendly way of working with SQL...you know, things like a "Save as..." option on the file menù). I recently wrote a little application just for me and my immediate colleagues for handling SQL jobs (just can't stand that appalling Enterpise Manager interface to the jobs subsystem any more!).

I very much doubt that using an ADP will solve your specific problem. It sounds to me as if there is something terribly small and obvious (obvious once you've found it, of course) that is going wrong.

I would stick with queries in Access MDB, I just can't see why what you're trying to do shouldn't work. I'm sure that I do have some queries somewhere that use this technique and they seem to be working OK.

Oh well, back to the Euro conversions...

Regards

David Westmore

------------
Cheryl Dodd at 10/4/01 6:50:00 AM

At the moment, they are queries within Access97 and not Views although with all the hunting round I've been doing and I've been going round and round in circles, I've decided to try a different approach by trying to utilise an Access 2000 Project instead of an ordinary database. That way aswell as using existing Forms and Reports, I'll be able to use Views and/or Stored Procedures so hopefully this will work and I'll be very HAPPY If not, I haven't got a clue...but I don't want to think negative, I'm determined to get it working as it's becoming more urgent and it's not the only database that I will have to convert. We have a fair few Access97 databases which will need transferring over....Can't wait!

We have both SQL 7 and 2000 so they will eventually be 2000 same as all our servers and workstations when we upgrade our network...some time this century.

Anyway, if I manage to get it working, I'll let you know or if you do hear of any other methodes, PLEEASE let me know...oh and I'll check out that link that you passed on.

Thanks,
Cheryl


------------
David Westmore at 10/3/01 7:18:07 AM

Hi, for some reason for some time I don't get automatic notification of replies, so I only found your message today.

Hmmm, the stored procedure approach: in my humble opinion, this is THEORETICALLY correct, but I would suggest using SPs only when you know you're going to be doing updates or inserts in code (and, if you're using Access, you should be avoiding this as much as possible).

You can refer to an SP defining a pass through query, but I really think this is just distorting the situation. SPs are good when you're doing it in code, especially for inserts, updates, etc. (take a look at "Proc Blaster" on the http://www.lockwoodtech.com/ site, there is a freeware version which is useful for getting into how to use SPs in code).

It's not clear to me what your problem is with the queries that refer to form controls. Are these queries in Access or are they views in SQL? I don't really see why they should work any differently if they're Access queries.

Regards

David Westmore







------------
Cheryl Dodd at 10/2/01 8:46:16 AM

Hi David,

With you suggesting about the queries remaining in Access, to be honest, I have noticed that a lot of them still work successfully without having any impact on the performance. The only problem queries are the ones which refer to forms. They still work (to a fashion) - Basically when you select the relevant control, a box appears for you to enter the required code. Whether you enter anything or leave it blank, the correct information appears on the next form once you select OK. Obviously I want to stop the box appearing - This did not happen when the tables were in Access. The relevant form would appear straight away.

As I have been looking into info on Access to SQL conversions, one of the suggestions was to convert the queries into stored procedures. I have tried this with one query which executes fine in Query Analyzer but then how do you refer to it in Access ? The other thing is, the query I tested it with does not have any reference to info on a form. I'm sure if I created a stored procedure with reference to a form in Access, it's obviously not going to execute.

So really I'm happy to leave the queries as is in Access but it's knowing the best way to refer to forms.

Cheryl


------------
David Westmore at 10/2/01 7:11:01 AM

When you do this kind of conversion, all references in queries to local Access objects (e.g. controls in forms, VBA functions which do not have equivalents in Transact SQL, and so on), will have to remain as queries in Access.

It's not as bad as it sounds, you're not obliged to convert all queries into views. In principle, a view should be a bit more efficient. You can still wirte queries that reference tables (or views which you connect "as if" they were tables).

There are obviously a few big drawbacks you need to try to avoid...e.g. if you're selecting records from a very large table using the result of a VBA function applied to one or more fields, you could end up having to read every single record from SQL into Jet just ot pick out, say, 3 or 4. If possible, filtering conditions should be "server side".

However, I have found that Jet and ODBC do a much better job than you would expect e.g. when you filter records on the basis of a control in a form, it will send the value up to SQL to execute the query (I have verified this with Profiler).

Let's say it's an empirical question: you need to try it out to see what the performance is like, and if it's very bad, you may have to rethink the whole technique. But I suspect that your main concern now is getting things up and working fast.

I don't like the technique of putting references to controls in forms in queries, I'm convinced that there are cleaner ways of achieving the same effect, but I suppose that you have to work with the raw material you've got!

Regards

David Westmore


------------
Cheryl Dodd at 10/2/01 4:44:25 AM

I am currently converting over an Access database into SQL. I want to leave the existing Forms and Reports in Access. I have converted the tables but the problem I am having is with the Queries which have some sort of reference to a Form so I am after any tips on how to do this.

[David L]

------------
Cheryl Dodd at 10/4/01 8:41:24 AM

These queries that you're sure you've done using this technique....Find them! Find them! Pleeeaase! I know what you mean about the solution being obvious once you've found it....It's just frustrating trying to find it!

Cheryl



------------
David Westmore at 10/4/01 8:14:46 AM

I've so far tried very little with ADP development (I've been using ADPs since they came out just as a more friendly way of working with SQL...you know, things like a "Save as..." option on the file menù). I recently wrote a little application just for me and my immediate colleagues for handling SQL jobs (just can't stand that appalling Enterpise Manager interface to the jobs subsystem any more!).

I very much doubt that using an ADP will solve your specific problem. It sounds to me as if there is something terribly small and obvious (obvious once you've found it, of course) that is going wrong.

I would stick with queries in Access MDB, I just can't see why what you're trying to do shouldn't work. I'm sure that I do have some queries somewhere that use this technique and they seem to be working OK.

Oh well, back to the Euro conversions...

Regards

David Westmore

------------
Cheryl Dodd at 10/4/01 6:50:00 AM

At the moment, they are queries within Access97 and not Views although with all the hunting round I've been doing and I've been going round and round in circles, I've decided to try a different approach by trying to utilise an Access 2000 Project instead of an ordinary database. That way aswell as using existing Forms and Reports, I'll be able to use Views and/or Stored Procedures so hopefully this will work and I'll be very HAPPY If not, I haven't got a clue...but I don't want to think negative, I'm determined to get it working as it's becoming more urgent and it's not the only database that I will have to convert. We have a fair few Access97 databases which will need transferring over....Can't wait!

We have both SQL 7 and 2000 so they will eventually be 2000 same as all our servers and workstations when we upgrade our network...some time this century.

Anyway, if I manage to get it working, I'll let you know or if you do hear of any other methodes, PLEEASE let me know...oh and I'll check out that link that you passed on.

Thanks,
Cheryl


------------
David Westmore at 10/3/01 7:18:07 AM

Hi, for some reason for some time I don't get automatic notification of replies, so I only found your message today.

Hmmm, the stored procedure approach: in my humble opinion, this is THEORETICALLY correct, but I would suggest using SPs only when you know you're going to be doing updates or inserts in code (and, if you're using Access, you should be avoiding this as much as possible).

You can refer to an SP defining a pass through query, but I really think this is just distorting the situation. SPs are good when you're doing it in code, especially for inserts, updates, etc. (take a look at "Proc Blaster" on the http://www.lockwoodtech.com/ site, there is a freeware version which is useful for getting into how to use SPs in code).

It's not clear to me what your problem is with the queries that refer to form controls. Are these queries in Access or are they views in SQL? I don't really see why they should work any differently if they're Access queries.

Regards

David Westmore







------------
Cheryl Dodd at 10/2/01 8:46:16 AM

Hi David,

With you suggesting about the queries remaining in Access, to be honest, I have noticed that a lot of them still work successfully without having any impact on the performance. The only problem queries are the ones which refer to forms. They still work (to a fashion) - Basically when you select the relevant control, a box appears for you to enter the required code. Whether you enter anything or leave it blank, the correct information appears on the next form once you select OK. Obviously I want to stop the box appearing - This did not happen when the tables were in Access. The relevant form would appear straight away.

As I have been looking into info on Access to SQL conversions, one of the suggestions was to convert the queries into stored procedures. I have tried this with one query which executes fine in Query Analyzer but then how do you refer to it in Access ? The other thing is, the query I tested it with does not have any reference to info on a form. I'm sure if I created a stored procedure with reference to a form in Access, it's obviously not going to execute.

So really I'm happy to leave the queries as is in Access but it's knowing the best way to refer to forms.

Cheryl


------------
David Westmore at 10/2/01 7:11:01 AM

When you do this kind of conversion, all references in queries to local Access objects (e.g. controls in forms, VBA functions which do not have equivalents in Transact SQL, and so on), will have to remain as queries in Access.

It's not as bad as it sounds, you're not obliged to convert all queries into views. In principle, a view should be a bit more efficient. You can still wirte queries that reference tables (or views which you connect "as if" they were tables).

There are obviously a few big drawbacks you need to try to avoid...e.g. if you're selecting records from a very large table using the result of a VBA function applied to one or more fields, you could end up having to read every single record from SQL into Jet just ot pick out, say, 3 or 4. If possible, filtering conditions should be "server side".

However, I have found that Jet and ODBC do a much better job than you would expect e.g. when you filter records on the basis of a control in a form, it will send the value up to SQL to execute the query (I have verified this with Profiler).

Let's say it's an empirical question: you need to try it out to see what the performance is like, and if it's very bad, you may have to rethink the whole technique. But I suspect that your main concern now is getting things up and working fast.

I don't like the technique of putting references to controls in forms in queries, I'm convinced that there are cleaner ways of achieving the same effect, but I suppose that you have to work with the raw material you've got!

Regards

David Westmore


------------
Cheryl Dodd at 10/2/01 4:44:25 AM

I am currently converting over an Access database into SQL. I want to leave the existing Forms and Reports in Access. I have converted the tables but the problem I am having is with the Queries which have some sort of reference to a Form so I am after any tips on how to do this.

[David L]

Sorry, I hit the enter key once too many times on the last reply. I caught on to the thread a bit late, and don't have too much to offer directly other than I am going through the same as you, Cheryl. A2K adp's don't offer the option of queries, as previously suggested -- if you find otherwise please let me know. All I have been able to do is create views.

A few things I have learned are:

1. you cannot filter by fields when using views in SQL 7. SQL2000 apparently allows this. So, no more "order by...."

2. You cannot reference controls on forms for use in queries. As far as I can tell, you must use the parameters collection of the command object, and pass them to stored procedures.

I find out more daily. I'm now struggling to get a handle on sprocs. good luck
------------
David L at 10/4/01 5:58:08 PM




------------
Cheryl Dodd at 10/4/01 8:41:24 AM

These queries that you're sure you've done using this technique....Find them! Find them! Pleeeaase! I know what you mean about the solution being obvious once you've found it....It's just frustrating trying to find it!

Cheryl



------------
David Westmore at 10/4/01 8:14:46 AM

I've so far tried very little with ADP development (I've been using ADPs since they came out just as a more friendly way of working with SQL...you know, things like a "Save as..." option on the file menù). I recently wrote a little application just for me and my immediate colleagues for handling SQL jobs (just can't stand that appalling Enterpise Manager interface to the jobs subsystem any more!).

I very much doubt that using an ADP will solve your specific problem. It sounds to me as if there is something terribly small and obvious (obvious once you've found it, of course) that is going wrong.

I would stick with queries in Access MDB, I just can't see why what you're trying to do shouldn't work. I'm sure that I do have some queries somewhere that use this technique and they seem to be working OK.

Oh well, back to the Euro conversions...

Regards

David Westmore

------------
Cheryl Dodd at 10/4/01 6:50:00 AM

At the moment, they are queries within Access97 and not Views although with all the hunting round I've been doing and I've been going round and round in circles, I've decided to try a different approach by trying to utilise an Access 2000 Project instead of an ordinary database. That way aswell as using existing Forms and Reports, I'll be able to use Views and/or Stored Procedures so hopefully this will work and I'll be very HAPPY If not, I haven't got a clue...but I don't want to think negative, I'm determined to get it working as it's becoming more urgent and it's not the only database that I will have to convert. We have a fair few Access97 databases which will need transferring over....Can't wait!

We have both SQL 7 and 2000 so they will eventually be 2000 same as all our servers and workstations when we upgrade our network...some time this century.

Anyway, if I manage to get it working, I'll let you know or if you do hear of any other methodes, PLEEASE let me know...oh and I'll check out that link that you passed on.

Thanks,
Cheryl


------------
David Westmore at 10/3/01 7:18:07 AM

Hi, for some reason for some time I don't get automatic notification of replies, so I only found your message today.

Hmmm, the stored procedure approach: in my humble opinion, this is THEORETICALLY correct, but I would suggest using SPs only when you know you're going to be doing updates or inserts in code (and, if you're using Access, you should be avoiding this as much as possible).

You can refer to an SP defining a pass through query, but I really think this is just distorting the situation. SPs are good when you're doing it in code, especially for inserts, updates, etc. (take a look at "Proc Blaster" on the http://www.lockwoodtech.com/ site, there is a freeware version which is useful for getting into how to use SPs in code).

It's not clear to me what your problem is with the queries that refer to form controls. Are these queries in Access or are they views in SQL? I don't really see why they should work any differently if they're Access queries.

Regards

David Westmore







------------
Cheryl Dodd at 10/2/01 8:46:16 AM

Hi David,

With you suggesting about the queries remaining in Access, to be honest, I have noticed that a lot of them still work successfully without having any impact on the performance. The only problem queries are the ones which refer to forms. They still work (to a fashion) - Basically when you select the relevant control, a box appears for you to enter the required code. Whether you enter anything or leave it blank, the correct information appears on the next form once you select OK. Obviously I want to stop the box appearing - This did not happen when the tables were in Access. The relevant form would appear straight away.

As I have been looking into info on Access to SQL conversions, one of the suggestions was to convert the queries into stored procedures. I have tried this with one query which executes fine in Query Analyzer but then how do you refer to it in Access ? The other thing is, the query I tested it with does not have any reference to info on a form. I'm sure if I created a stored procedure with reference to a form in Access, it's obviously not going to execute.

So really I'm happy to leave the queries as is in Access but it's knowing the best way to refer to forms.

Cheryl


------------
David Westmore at 10/2/01 7:11:01 AM

When you do this kind of conversion, all references in queries to local Access objects (e.g. controls in forms, VBA functions which do not have equivalents in Transact SQL, and so on), will have to remain as queries in Access.

It's not as bad as it sounds, you're not obliged to convert all queries into views. In principle, a view should be a bit more efficient. You can still wirte queries that reference tables (or views which you connect "as if" they were tables).

There are obviously a few big drawbacks you need to try to avoid...e.g. if you're selecting records from a very large table using the result of a VBA function applied to one or more fields, you could end up having to read every single record from SQL into Jet just ot pick out, say, 3 or 4. If possible, filtering conditions should be "server side".

However, I have found that Jet and ODBC do a much better job than you would expect e.g. when you filter records on the basis of a control in a form, it will send the value up to SQL to execute the query (I have verified this with Profiler).

Let's say it's an empirical question: you need to try it out to see what the performance is like, and if it's very bad, you may have to rethink the whole technique. But I suspect that your main concern now is getting things up and working fast.

I don't like the technique of putting references to controls in forms in queries, I'm convinced that there are cleaner ways of achieving the same effect, but I suppose that you have to work with the raw material you've got!

Regards

David Westmore


------------
Cheryl Dodd at 10/2/01 4:44:25 AM

I am currently converting over an Access database into SQL. I want to leave the existing Forms and Reports in Access. I have converted the tables but the problem I am having is with the Queries which have some sort of reference to a Form so I am after any tips on how to do this.

[Cheryl Dodd]

It's OK...I hit the enter key one too many times when I sent my first message! Anyway, still trying to sort it out...I'll let you know aswell if I have sorry I mean when I have some success...I'll be old and grey by then???!!

Cheryl

------------
David L at 10/4/01 6:07:19 PM


Sorry, I hit the enter key once too many times on the last reply. I caught on to the thread a bit late, and don't have too much to offer directly other than I am going through the same as you, Cheryl. A2K adp's don't offer the option of queries, as previously suggested -- if you find otherwise please let me know. All I have been able to do is create views.

A few things I have learned are:

1. you cannot filter by fields when using views in SQL 7. SQL2000 apparently allows this. So, no more "order by...."

2. You cannot reference controls on forms for use in queries. As far as I can tell, you must use the parameters collection of the command object, and pass them to stored procedures.

I find out more daily. I'm now struggling to get a handle on sprocs. good luck
------------
David L at 10/4/01 5:58:08 PM




------------
Cheryl Dodd at 10/4/01 8:41:24 AM

These queries that you're sure you've done using this technique....Find them! Find them! Pleeeaase! I know what you mean about the solution being obvious once you've found it....It's just frustrating trying to find it!

Cheryl



------------
David Westmore at 10/4/01 8:14:46 AM

I've so far tried very little with ADP development (I've been using ADPs since they came out just as a more friendly way of working with SQL...you know, things like a "Save as..." option on the file menù). I recently wrote a little application just for me and my immediate colleagues for handling SQL jobs (just can't stand that appalling Enterpise Manager interface to the jobs subsystem any more!).

I very much doubt that using an ADP will solve your specific problem. It sounds to me as if there is something terribly small and obvious (obvious once you've found it, of course) that is going wrong.

I would stick with queries in Access MDB, I just can't see why what you're trying to do shouldn't work. I'm sure that I do have some queries somewhere that use this technique and they seem to be working OK.

Oh well, back to the Euro conversions...

Regards

David Westmore

------------
Cheryl Dodd at 10/4/01 6:50:00 AM

At the moment, they are queries within Access97 and not Views although with all the hunting round I've been doing and I've been going round and round in circles, I've decided to try a different approach by trying to utilise an Access 2000 Project instead of an ordinary database. That way aswell as using existing Forms and Reports, I'll be able to use Views and/or Stored Procedures so hopefully this will work and I'll be very HAPPY If not, I haven't got a clue...but I don't want to think negative, I'm determined to get it working as it's becoming more urgent and it's not the only database that I will have to convert. We have a fair few Access97 databases which will need transferring over....Can't wait!

We have both SQL 7 and 2000 so they will eventually be 2000 same as all our servers and workstations when we upgrade our network...some time this century.

Anyway, if I manage to get it working, I'll let you know or if you do hear of any other methodes, PLEEASE let me know...oh and I'll check out that link that you passed on.

Thanks,
Cheryl


------------
David Westmore at 10/3/01 7:18:07 AM

Hi, for some reason for some time I don't get automatic notification of replies, so I only found your message today.

Hmmm, the stored procedure approach: in my humble opinion, this is THEORETICALLY correct, but I would suggest using SPs only when you know you're going to be doing updates or inserts in code (and, if you're using Access, you should be avoiding this as much as possible).

You can refer to an SP defining a pass through query, but I really think this is just distorting the situation. SPs are good when you're doing it in code, especially for inserts, updates, etc. (take a look at "Proc Blaster" on the http://www.lockwoodtech.com/ site, there is a freeware version which is useful for getting into how to use SPs in code).

It's not clear to me what your problem is with the queries that refer to form controls. Are these queries in Access or are they views in SQL? I don't really see why they should work any differently if they're Access queries.

Regards

David Westmore







------------
Cheryl Dodd at 10/2/01 8:46:16 AM

Hi David,

With you suggesting about the queries remaining in Access, to be honest, I have noticed that a lot of them still work successfully without having any impact on the performance. The only problem queries are the ones which refer to forms. They still work (to a fashion) - Basically when you select the relevant control, a box appears for you to enter the required code. Whether you enter anything or leave it blank, the correct information appears on the next form once you select OK. Obviously I want to stop the box appearing - This did not happen when the tables were in Access. The relevant form would appear straight away.

As I have been looking into info on Access to SQL conversions, one of the suggestions was to convert the queries into stored procedures. I have tried this with one query which executes fine in Query Analyzer but then how do you refer to it in Access ? The other thing is, the query I tested it with does not have any reference to info on a form. I'm sure if I created a stored procedure with reference to a form in Access, it's obviously not going to execute.

So really I'm happy to leave the queries as is in Access but it's knowing the best way to refer to forms.

Cheryl


------------
David Westmore at 10/2/01 7:11:01 AM

When you do this kind of conversion, all references in queries to local Access objects (e.g. controls in forms, VBA functions which do not have equivalents in Transact SQL, and so on), will have to remain as queries in Access.

It's not as bad as it sounds, you're not obliged to convert all queries into views. In principle, a view should be a bit more efficient. You can still wirte queries that reference tables (or views which you connect "as if" they were tables).

There are obviously a few big drawbacks you need to try to avoid...e.g. if you're selecting records from a very large table using the result of a VBA function applied to one or more fields, you could end up having to read every single record from SQL into Jet just ot pick out, say, 3 or 4. If possible, filtering conditions should be "server side".

However, I have found that Jet and ODBC do a much better job than you would expect e.g. when you filter records on the basis of a control in a form, it will send the value up to SQL to execute the query (I have verified this with Profiler).

Let's say it's an empirical question: you need to try it out to see what the performance is like, and if it's very bad, you may have to rethink the whole technique. But I suspect that your main concern now is getting things up and working fast.

I don't like the technique of putting references to controls in forms in queries, I'm convinced that there are cleaner ways of achieving the same effect, but I suppose that you have to work with the raw material you've got!

Regards

David Westmore


------------
Cheryl Dodd at 10/2/01 4:44:25 AM

I am currently converting over an Access database into SQL. I want to leave the existing Forms and Reports in Access. I have converted the tables but the problem I am having is with the Queries which have some sort of reference to a Form so I am after any tips on how to do this.

[Kevin]

Hello Cheryl and David,

Some thoughts:

Stored procedures are better than views, because they are compiled once (at creation/first run). Views are recompiled every time they are run. SPs can use the order by clause, which views cannot (SS7).

Access Data Projects make it fairly simple to pass a parameter. There is not a lot of documentation on it, but I am working my way through it now. I have found two methods. 1)Set RecordSource and InputParameters properties at design time, and deal with popup boxes when the form loads, or set them at design time by using the value of a control (txtControl.txt)

If you need a little help with stored procedures, let me know. If you need a lot of help, I can work out something with your firm.

Kevin

------------
Cheryl Dodd at 10/5/01 12:07:04 PM

It's OK...I hit the enter key one too many times when I sent my first message! Anyway, still trying to sort it out...I'll let you know aswell if I have sorry I mean when I have some success...I'll be old and grey by then???!!

Cheryl

------------
David L at 10/4/01 6:07:19 PM


Sorry, I hit the enter key once too many times on the last reply. I caught on to the thread a bit late, and don't have too much to offer directly other than I am going through the same as you, Cheryl. A2K adp's don't offer the option of queries, as previously suggested -- if you find otherwise please let me know. All I have been able to do is create views.

A few things I have learned are:

1. you cannot filter by fields when using views in SQL 7. SQL2000 apparently allows this. So, no more "order by...."

2. You cannot reference controls on forms for use in queries. As far as I can tell, you must use the parameters collection of the command object, and pass them to stored procedures.

I find out more daily. I'm now struggling to get a handle on sprocs. good luck
------------
David L at 10/4/01 5:58:08 PM




------------
Cheryl Dodd at 10/4/01 8:41:24 AM

These queries that you're sure you've done using this technique....Find them! Find them! Pleeeaase! I know what you mean about the solution being obvious once you've found it....It's just frustrating trying to find it!

Cheryl



------------
David Westmore at 10/4/01 8:14:46 AM

I've so far tried very little with ADP development (I've been using ADPs since they came out just as a more friendly way of working with SQL...you know, things like a "Save as..." option on the file menù). I recently wrote a little application just for me and my immediate colleagues for handling SQL jobs (just can't stand that appalling Enterpise Manager interface to the jobs subsystem any more!).

I very much doubt that using an ADP will solve your specific problem. It sounds to me as if there is something terribly small and obvious (obvious once you've found it, of course) that is going wrong.

I would stick with queries in Access MDB, I just can't see why what you're trying to do shouldn't work. I'm sure that I do have some queries somewhere that use this technique and they seem to be working OK.

Oh well, back to the Euro conversions...

Regards

David Westmore

------------
Cheryl Dodd at 10/4/01 6:50:00 AM

At the moment, they are queries within Access97 and not Views although with all the hunting round I've been doing and I've been going round and round in circles, I've decided to try a different approach by trying to utilise an Access 2000 Project instead of an ordinary database. That way aswell as using existing Forms and Reports, I'll be able to use Views and/or Stored Procedures so hopefully this will work and I'll be very HAPPY If not, I haven't got a clue...but I don't want to think negative, I'm determined to get it working as it's becoming more urgent and it's not the only database that I will have to convert. We have a fair few Access97 databases which will need transferring over....Can't wait!

We have both SQL 7 and 2000 so they will eventually be 2000 same as all our servers and workstations when we upgrade our network...some time this century.

Anyway, if I manage to get it working, I'll let you know or if you do hear of any other methodes, PLEEASE let me know...oh and I'll check out that link that you passed on.

Thanks,
Cheryl


------------
David Westmore at 10/3/01 7:18:07 AM

Hi, for some reason for some time I don't get automatic notification of replies, so I only found your message today.

Hmmm, the stored procedure approach: in my humble opinion, this is THEORETICALLY correct, but I would suggest using SPs only when you know you're going to be doing updates or inserts in code (and, if you're using Access, you should be avoiding this as much as possible).

You can refer to an SP defining a pass through query, but I really think this is just distorting the situation. SPs are good when you're doing it in code, especially for inserts, updates, etc. (take a look at "Proc Blaster" on the http://www.lockwoodtech.com/ site, there is a freeware version which is useful for getting into how to use SPs in code).

It's not clear to me what your problem is with the queries that refer to form controls. Are these queries in Access or are they views in SQL? I don't really see why they should work any differently if they're Access queries.

Regards

David Westmore







------------
Cheryl Dodd at 10/2/01 8:46:16 AM

Hi David,

With you suggesting about the queries remaining in Access, to be honest, I have noticed that a lot of them still work successfully without having any impact on the performance. The only problem queries are the ones which refer to forms. They still work (to a fashion) - Basically when you select the relevant control, a box appears for you to enter the required code. Whether you enter anything or leave it blank, the correct information appears on the next form once you select OK. Obviously I want to stop the box appearing - This did not happen when the tables were in Access. The relevant form would appear straight away.

As I have been looking into info on Access to SQL conversions, one of the suggestions was to convert the queries into stored procedures. I have tried this with one query which executes fine in Query Analyzer but then how do you refer to it in Access ? The other thing is, the query I tested it with does not have any reference to info on a form. I'm sure if I created a stored procedure with reference to a form in Access, it's obviously not going to execute.

So really I'm happy to leave the queries as is in Access but it's knowing the best way to refer to forms.

Cheryl


------------
David Westmore at 10/2/01 7:11:01 AM

When you do this kind of conversion, all references in queries to local Access objects (e.g. controls in forms, VBA functions which do not have equivalents in Transact SQL, and so on), will have to remain as queries in Access.

It's not as bad as it sounds, you're not obliged to convert all queries into views. In principle, a view should be a bit more efficient. You can still wirte queries that reference tables (or views which you connect "as if" they were tables).

There are obviously a few big drawbacks you need to try to avoid...e.g. if you're selecting records from a very large table using the result of a VBA function applied to one or more fields, you could end up having to read every single record from SQL into Jet just ot pick out, say, 3 or 4. If possible, filtering conditions should be "server side".

However, I have found that Jet and ODBC do a much better job than you would expect e.g. when you filter records on the basis of a control in a form, it will send the value up to SQL to execute the query (I have verified this with Profiler).

Let's say it's an empirical question: you need to try it out to see what the performance is like, and if it's very bad, you may have to rethink the whole technique. But I suspect that your main concern now is getting things up and working fast.

I don't like the technique of putting references to controls in forms in queries, I'm convinced that there are cleaner ways of achieving the same effect, but I suppose that you have to work with the raw material you've got!

Regards

David Westmore


------------
Cheryl Dodd at 10/2/01 4:44:25 AM

I am currently converting over an Access database into SQL. I want to leave the existing Forms and Reports in Access. I have converted the tables but the problem I am having is with the Queries which have some sort of reference to a Form so I am after any tips on how to do this.