If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#51
|
|||
|
|||
How do you mulitply in a field?
On 23 Aug, 13:03, "BruceM" wrote:
Knowledgeable people have pointed out that there are many performance factors to be considered before physical ordering on the disk, and have questioned whether such physical ordering is really relevant, at least in an office-sized database. While I accept the validity of a multi-field index (or several single-field indices), I don't accept the physical ordering argument. Yes, there are many factors to consider as regards performance. Yes, different people will rate these factors differently. But here's a link to a MSDN article about query performance in an Access database which places physical ordering at the top of its list of "Tips to improve query performance": Information about query performance in an Access database http://support.microsoft.com/kb/209126 "To improve query performance, try these tips: Compact your database. When you compact your database you can speed up queries. When you compact your database, the records of the table are reorganized so that the records reside in adjacent database pages that are ordered by the primary key of the table. This improves the performance of the sequential scans of records in the table because only the minimum number of database pages now have to be read to retrieve the records that you want." Here is another a MSDN article which has a list of "Optimization Tips and Techniques" and physical ordering is the second one listed: Microsoft Jet 3.5 Performance Overview and Optimization Techniques http://msdn.microsoft.com/archive/de...baseEngine.asp "From a performance perspective, there are many reasons to frequently compact a database. One reason is that compacting will create a new database that stores all table rows in a contiguous order. If a primary key or unique index is defined, the rows will be sorted in order of the primary key or unique index. This allows Microsoft Jet to take full advantage of its read-ahead cache and also reduces disk I/O when doing sequential scans of a table." Out of interest, the first one listed is "Prevent Unnecessary Query Recompilation" and seems to relate only to stored queries and then only in conjunction with DAO's infamous memory leak bug (which I understand has since been fixed). In case it's not clear what these articles are getting at, here are a few aircode examples of 'sequential scans' that will favour physical ordering: SELECT * FROM TelephoneDirectory WHERE last_name = 'Collins'; SELECT * FROM SalaryHistory WHERE employee_number = '454818181' AND start_date BETWEEN #2006-01-01 00:00:00# AND AND #2006-12-31 23:59:59#; SELECT employee_number, COUNT(*) AS salary_review_tally FROM SalaryHistory GROUP BY employee_number; When was the last time you used BETWEEN or GROUP BY on an autonumber column? Now, be fair, where are these knowledgeable people's lists and where exactly does physical ordering appear in their lists? If they've omitted physical ordering from their lists, how do we know they've even considered it? TIA. I grant permissions to groups, which is the most common scenario, but is in the same category as granting permissions to individual users. OK so you give people the permissions... Even if their intentions are the best, they should not be able to get into the tables directly. Then why give you give them (via their groups) the permissions to do so? If they have the permissions of which you speak ... ... you just confirmed that they do... ...they are able to add incorrect data that fulfill the table-level constraints. So why not conclude, "I'd better put in some table-level constraints to prevent incorrect data"? Integrity constraints are one thing, but I don't know that my time would be well spent devising what amount to backup validation rules at the table level. Funny, I start with the table-level constraints first (i.e. they are not back ups) then sigh when I think about having to duplicate the effort in the front end g. You design your tables *before* you design your forms, right? Why not the same for constraints? I have attempted to agree with you, only to have you fire back as if I had never said anything. That gets old in a hurry. Oops, sincere apologies. Please believe I don't do that on purpose. I think it must happen only when I've missed your point (or I think you've missed my point when you haven't etc). Jamie. -- |
#52
|
|||
|
|||
How do you mulitply in a field?
"Jamie Collins" wrote in message ups.com... On 23 Aug, 13:03, "BruceM" wrote: Knowledgeable people have pointed out that there are many performance factors to be considered before physical ordering on the disk, and have questioned whether such physical ordering is really relevant, at least in an office-sized database. While I accept the validity of a multi-field index (or several single-field indices), I don't accept the physical ordering argument. Yes, there are many factors to consider as regards performance. Yes, different people will rate these factors differently. But here's a link to a MSDN article about query performance in an Access database which places physical ordering at the top of its list of "Tips to improve query performance": Information about query performance in an Access database http://support.microsoft.com/kb/209126 "To improve query performance, try these tips: Compact your database. When you compact your database you can speed up queries. When you compact your database, the records of the table are reorganized so that the records reside in adjacent database pages that are ordered by the primary key of the table. This improves the performance of the sequential scans of records in the table because only the minimum number of database pages now have to be read to retrieve the records that you want." I never argued against compacting. Here is another a MSDN article which has a list of "Optimization Tips and Techniques" and physical ordering is the second one listed: Microsoft Jet 3.5 Performance Overview and Optimization Techniques http://msdn.microsoft.com/archive/de...baseEngine.asp "From a performance perspective, there are many reasons to frequently compact a database. One reason is that compacting will create a new database that stores all table rows in a contiguous order. If a primary key or unique index is defined, the rows will be sorted in order of the primary key or unique index. This allows Microsoft Jet to take full advantage of its read-ahead cache and also reduces disk I/O when doing sequential scans of a table." Out of interest, the first one listed is "Prevent Unnecessary Query Recompilation" and seems to relate only to stored queries and then only in conjunction with DAO's infamous memory leak bug (which I understand has since been fixed). There's also something about judicious use of indexes, and something about the use of expressions in subqueries. The latter includes the recommendation that expressions be used in the front end (i.e. the form) to produce the necessary information in subforms. Anyhow, it looks like a good article. I have bookmarked it. In case it's not clear what these articles are getting at, here are a few aircode examples of 'sequential scans' that will favour physical ordering: SELECT * FROM TelephoneDirectory WHERE last_name = 'Collins'; SELECT * FROM SalaryHistory WHERE employee_number = '454818181' AND start_date BETWEEN #2006-01-01 00:00:00# AND AND #2006-12-31 23:59:59#; SELECT employee_number, COUNT(*) AS salary_review_tally FROM SalaryHistory GROUP BY employee_number; When was the last time you used BETWEEN or GROUP BY on an autonumber column? Gosh, I can't remember when I last did that. But what a probing and thought-provoking question!! sarcasm I have been saying the same thing over and over, and will stop doing so after this message. I do not rely on autonumber to make a record unique. I use autonumber when no "natural" field meets my needs for an Access PK. You have not answered my objection to linking tables through fields that are subject to change. Now, be fair, where are these knowledgeable people's lists and where exactly does physical ordering appear in their lists? If they've omitted physical ordering from their lists, how do we know they've even considered it? TIA. I have seen the stuff about compacting. No argument. Once again, I am not arguing against compacting. That is to say, I think compacting is a good thing, and I have observed performance differences after compacting. However, I have seen information about how indexing choices can degrade performance (for instance, when using indexes on City and PostalCode fields). As the article points out, there is no single correct answer about indexing. I grant permissions to groups, which is the most common scenario, but is in the same category as granting permissions to individual users. OK so you give people the permissions... Even if their intentions are the best, they should not be able to get into the tables directly. Then why give you give them (via their groups) the permissions to do so? I don't. I never said I did. Unless you know some sort of back door (or are a member of the appropriate group), you would not be able to open the back end directly. If they have the permissions of which you speak ... .. you just confirmed that they do... Not to access the tables directly. ...they are able to add incorrect data that fulfill the table-level constraints. So why not conclude, "I'd better put in some table-level constraints to prevent incorrect data"? I wouldn't know how to limit at the table level the list of models if "SUV" is chosen as the class of vehicle. So far I see no reason to learn. Integrity constraints are one thing, but I don't know that my time would be well spent devising what amount to backup validation rules at the table level. Funny, I start with the table-level constraints first (i.e. they are not back ups) then sigh when I think about having to duplicate the effort in the front end g. OK, fine. You design your tables *before* you design your forms, right? Why not the same for constraints? Of course I design the tables (and relationships) first. However, I have changed elements of the table design after development of forms, etc. is well under way. I have also gone back and re-thought the design. You? I have attempted to agree with you, only to have you fire back as if I had never said anything. That gets old in a hurry. Oops, sincere apologies. Please believe I don't do that on purpose. I think it must happen only when I've missed your point (or I think you've missed my point when you haven't etc). Jamie. -- |
#53
|
|||
|
|||
How do you mulitply in a field?
On 23 Aug, 16:33, "BruceM" wrote:
I have been saying the same thing over and over, and will stop doing so after this message. I do not rely on autonumber to make a record unique. I've heard you say this over and over and I believe you. Good for you. Neither do I. I'm glad we agree. Actually, I think we agree on most things. BTW no sarcasm intended. You have not answered my objection to linking tables through fields that are subject to change. I've not tried to *refute* your objection because I think you have every right to hold that view. I cannot tell you whether joining your tables on fields that are subject to change is good or bad or indifferent because I don't know anything about their structure or content (but, sight unseen, my money would be on 'indifferent'). However, I feel I have *answered* your objection e.g. on 16 August in this thread when I said, "I think the SQL DBMS should store the key value only once and use an internal surrogate." We both agree that physically storing the key value in all the referencing tables is a Bad Thing but we have different responses to it. My response is to shrug: it's a 'feature' of the product and if I thought the product's performance, CASCADE referential actions, etc were not up to the job then the product is not fit for purpose and it's a free market. Your response is to roll your own surrogate using autonumber. I don't feel I've tried to push my approach down your throat though I have suggested you to consider the effects of placing a *physical* index on an autonumber column and given some examples of how a sole autonumber PK for referencing in FKs can work against some design patterns such as subclassing. I've acknowledged that your way valid but is not for me, primarily because it makes referencing tables' data harder to read, but have wished you luck with yours. In the same spirit, may I press you further for detail of, "Knowledgeable people [who] have pointed out that there are many performance factors to be considered before physical ordering on the disk," please? In my experience, most supposedly knowledgeable people give it no consideration; others don't seem to believe that clustering actually happens in Jet at all! I think compacting is a good thing, and I have observed performance differences after compacting. However, I have seen information about how indexing choices can degrade performance (for instance, when using indexes on City and PostalCode fields). As the article points out, there is no single correct answer about indexing. Again, we agree there are many factors to performance. You said you'd heard physical ordering was not at the top of some lists. I've shown two from Microsoft where it is. One thing I'm still not clear on. You said earlier, "I don't accept the physical ordering argument." I thought you meant you did not see how it can be relevant to either choice of PK or performance implications. Did I misunderstand what you meant? Has anything I've presented caused you to revise this earlier held view? I get how you choose your PKs (unchanging field values, single column FKs) but do you get how physical ordering might affect PK choice for someone else? Then why give you give them (via their groups) the permissions to do so? I don't. I never said I did. It seems I'm not understanding your approach. I see two basic approaches: 1) Users can perform updates to your tables' data via your application because you have granted the privileges on the tables to the users (but does not preclude the same users from performing updates to your tables' data via another route). 2) You have not granted the privileges on the tables to the users but they can still perform updates to your tables' data via your application because you have granted privileges on the application to the users and granted privileges on the tables to your application (the advantage here is that they can *only* perform updates via your application). Is your approach one of these, a variation or something completely different? Note a common third approach is that everyone runs as an administrator but that would not seem to apply to you. Jamie. -- |
#54
|
|||
|
|||
How do you mulitply in a field?
"Jamie Collins" wrote in message oups.com... On 23 Aug, 16:33, "BruceM" wrote: I have been saying the same thing over and over, and will stop doing so after this message. I do not rely on autonumber to make a record unique. I've heard you say this over and over and I believe you. Good for you. Neither do I. I'm glad we agree. Actually, I think we agree on most things. BTW no sarcasm intended. You have not answered my objection to linking tables through fields that are subject to change. I've not tried to *refute* your objection because I think you have every right to hold that view. I cannot tell you whether joining your tables on fields that are subject to change is good or bad or indifferent because I don't know anything about their structure or content (but, sight unseen, my money would be on 'indifferent'). However, I feel I have *answered* your objection e.g. on 16 August in this thread when I said, "I think the SQL DBMS should store the key value only once and use an internal surrogate." We both agree that physically storing the key value in all the referencing tables is a Bad Thing but we have different responses to it. My response is to shrug: it's a 'feature' of the product and if I thought the product's performance, CASCADE referential actions, etc were not up to the job then the product is not fit for purpose and it's a free market. Your response is to roll your own surrogate using autonumber. I don't feel I've tried to push my approach down your throat though I have suggested you to consider the effects of placing a *physical* index on an autonumber column and given some examples of how a sole autonumber PK for referencing in FKs can work against some design patterns such as subclassing. I've acknowledged that your way valid but is not for me, primarily because it makes referencing tables' data harder to read, but have wished you luck with yours. I thought a PK field is indexed, no duplicates by default (and without options to change those aspects). In the same spirit, may I press you further for detail of, "Knowledgeable people [who] have pointed out that there are many performance factors to be considered before physical ordering on the disk," please? In my experience, most supposedly knowledgeable people give it no consideration; others don't seem to believe that clustering actually happens in Jet at all! In this listing physical ordering is not listed directly, but at least one of the referenced articles discusses indexing. I don't know how indexing and physical ordering on the disk are related. In previous discussions several people who have demonstrated a good command of Access and of databases in general questioned the importance of clustered indexes for physical ordering on the disk. http://groups.google.com/group/micro...cf0d56 a19f15 http://groups.google.com/group/micro...209 cee5d14d7 There is a variety of views within those threads. By the way, if there is a better way to reference newsgroup discussions I would be glad to know about it. I think compacting is a good thing, and I have observed performance differences after compacting. However, I have seen information about how indexing choices can degrade performance (for instance, when using indexes on City and PostalCode fields). As the article points out, there is no single correct answer about indexing. Again, we agree there are many factors to performance. You said you'd heard physical ordering was not at the top of some lists. I've shown two from Microsoft where it is. If the listings are in order of importance, then I guess you are correct. As I have stated several times already, I finally understand (I think) a distinction you made between PKs and indexes. I still doubt there is a performance issue between indexing on an arbitrary number such as EmployeeID and and arbitrary PK. Also, I may choose to list employees by lastname, firstname, but have many occasions to order or group by department, startdate, or what have you. BTW, one of the articles states that the rows will be ordered according to the primary key OR the unique index, but leaves me guessing as to what happens if both exist. One thing I'm still not clear on. You said earlier, "I don't accept the physical ordering argument." I thought you meant you did not see how it can be relevant to either choice of PK or performance implications. Did I misunderstand what you meant? Has anything I've presented caused you to revise this earlier held view? I get how you choose your PKs (unchanging field values, single column FKs) but do you get how physical ordering might affect PK choice for someone else? I probably did not accept the argument because I may order on one of several fields, depending on how I wish to present the data. If one of those fields is the unique index, but it is not the most common ordering I will use, I wonder if there is an advantage. Then why give you give them (via their groups) the permissions to do so? I don't. I never said I did. It seems I'm not understanding your approach. I see two basic approaches: 1) Users can perform updates to your tables' data via your application because you have granted the privileges on the tables to the users (but does not preclude the same users from performing updates to your tables' data via another route). 2) You have not granted the privileges on the tables to the users but they can still perform updates to your tables' data via your application because you have granted privileges on the application to the users and granted privileges on the tables to your application (the advantage here is that they can *only* perform updates via your application). As far as I know, properly implemented user-level security prevents back-door updates to the tables. I do know that there is no direct way of opening the back end (and getting to the table data that way) without going through the secure mdw file (although I am aware that user-level security is not as robust as other methods available to SQL). What you described with the Excel thing is in fact a back-door update, undertaken by somebody who is deliberately and knowingly circumventing the intended route for entering data. In my book that is hacking, not an innocent attempt to be more efficient. Users have full permissions to the table links in the FE, and limited permissions (according to the group of which they are a member) to the actual BE tables. All updates are intended to happen by way of the user interface. As of now users can still use the Shift key bypass, but there are ways to prevent that. I haven't had the chance to implement that yet, as other matters have my immediate attention, but plan to do so before long. As it is, unsecured databases have been unmolested for quite some time, and very few people even know about the Shift bypass option, so I'm not too concerned in the immediate short term. Is your approach one of these, a variation or something completely different? Note a common third approach is that everyone runs as an administrator but that would not seem to apply to you. Jamie. -- |
#55
|
|||
|
|||
How do you mulitply in a field?
On 24 Aug, 13:37, "BruceM" wrote:
In previous discussions several people who have demonstrated a good command of Access and of databases in general questioned the importance of clustered indexes for physical ordering on the disk. http://groups.google.com/group/micro...browse_thread/... There's 101 posts in that thread and you linked to one which I wrote! Who do you think is the knowledgeable person in this thread? I recall David W Fenton being particularly obstructive telling he knew of lots of things but wouldn't deign to reveal them and ironically did bother to come back several times to tell us he couldn't be bothered g. The only relevant post I can pick out is this: [David W Fenton] Given that with Jet you can only have the one clustered index, I really think this is a pretty irrelevant consideration. I never use the BETWEEN operator on anything but date fields, which in the vast majority of tables could not possibly ever be a candidate for PK (and very seldom even a candidate for inclusion in a compound natural key, which wouldn't give you the clustered index benefit, anyway, unless the date was the first field of the compound key). So, I just don't see any practical benefit in your pointing out the performance advantage of the clustered index. You can only have one clustered in index period -- it's not a Jet restriction, more like the laws of physics g! In *this* thread I posted the salary history example where dates are part of the candidate keys and we see history table designs cropping up regularly in the Access groups. And he answered his own point about including such a date in a compound key i.e. make it the first column in the key's definition. I generally get the feeling he was looking at things the wrong way to be able to see my point i.e. he was considering considers the clustering resulting from his existing choice of PK, whereas I'm proposing one identifies the columns for clustering then choose the PK to fit. There are some later exchanges but he seems to dismiss the whole idea ("I think your suggestion is bloody stupid", "I think you're mis-using your RDBMS") without giving solid reasons. http://groups.google.com/group/micro...tablesdbdesign... That one's you and I again! And the only other person who commented on clustering is David W Fenton again but this time he's actually acknowledged the point: [David W Fenton] I can't imagine a telephone number table that would *need* a PK that didn't involve the parent foreign key. In any event, you'd have to have hundreds of thousands of records in the telephone table for it to make a noticeable difference. Again, he seemingly hasn't made the shift to, "What if you chose the PK to purposely change the clustering...?" But he does seem to see the light. While I'm not sure he's correct when he says "hundreds of thousands of records" (the MSFT articles don't hint at this), tables of such proportions are hardly a rare occurrence in the Access groups. One thing that bothers me is there is no frame of reference in this thread e.g. David W Fenton does not give any comparison to other optimization techniques. The Microsoft articles rated it highly, after all. There is a variety of views within those threads. Indeed. Notice how a few people needed convincing that clustering existed in Jet at all, after which they dropped out of the discussion. I still doubt there is a performance issue between indexing on an arbitrary number such as EmployeeID and and arbitrary PK. Agreed. I can't see either would offer any advantage. Clustering is better suited to meaningful data. Also, I may choose to list employees by lastname, firstname, but have many occasions to order or group by department, startdate, or what have you. You then have to look at the relative usage frequency, importance, etc. I didn't say optimization was easy g. BTW, one of the articles states that the rows will be ordered according to the primary key OR the unique index, but leaves me guessing as to what happens if both exist. PK takes precedence, as advised by Lyle Fairfield in the first thread you linked to. I do know that there is no direct way of opening the back end (and getting to the table data that way) without going through the secure mdw file Users have full permissions to the table links in the FE, and limited permissions (according to the group of which they are a member) to the actual BE tables. What you described with the Excel thing is in fact a back-door update, undertaken by somebody who is deliberately and knowingly circumventing the intended route for entering data. In my book that is hacking, not an innocent attempt to be more efficient. If they've used their own credentials via your mdw (i.e. utilizing your security mesures rather than circuventing them) to connect to the data in the BE from within Excel then in my book that is legitimate usage and IMO should be accommodated. Jamie. -- |
#56
|
|||
|
|||
How do you mulitply in a field?
"Jamie Collins" wrote in message
oups.com... On 24 Aug, 13:37, "BruceM" wrote: In previous discussions several people who have demonstrated a good command of Access and of databases in general questioned the importance of clustered indexes for physical ordering on the disk. http://groups.google.com/group/micro...browse_thread/... There's 101 posts in that thread and you linked to one which I wrote! I was trying to point to the thread, not to a specific message. Albert argued that there is no reason not to use PK. Presumably if a non-maintained clustered index was important that would have been worth mentioning. He did say that there are perfectly valid reasons to use a natural key, too. Amy had some observations on the subject, including regarding it as silly (or some such) that a PK (or non-null unique index) would be chosen for clustering reasons. Who do you think is the knowledgeable person in this thread? I recall David W Fenton being particularly obstructive telling he knew of lots of things but wouldn't deign to reveal them and ironically did bother to come back several times to tell us he couldn't be bothered g. The only relevant post I can pick out is this: [David W Fenton] Given that with Jet you can only have the one clustered index, I really think this is a pretty irrelevant consideration. I never use the BETWEEN operator on anything but date fields, which in the vast majority of tables could not possibly ever be a candidate for PK (and very seldom even a candidate for inclusion in a compound natural key, which wouldn't give you the clustered index benefit, anyway, unless the date was the first field of the compound key). So, I just don't see any practical benefit in your pointing out the performance advantage of the clustered index. You can only have one clustered in index period -- it's not a Jet restriction, more like the laws of physics g! In *this* thread I posted the salary history example where dates are part of the candidate keys and we see history table designs cropping up regularly in the Access groups. And he answered his own point about including such a date in a compound key i.e. make it the first column in the key's definition. I generally get the feeling he was looking at things the wrong way to be able to see my point i.e. he was considering considers the clustering resulting from his existing choice of PK, whereas I'm proposing one identifies the columns for clustering then choose the PK to fit. There are some later exchanges but he seems to dismiss the whole idea ("I think your suggestion is bloody stupid", "I think you're mis-using your RDBMS") without giving solid reasons. http://groups.google.com/group/micro...tablesdbdesign... That one's you and I again! And the only other person who commented on clustering is David W Fenton again but this time he's actually acknowledged the point: One thing that bothers me is there is no frame of reference in this thread e.g. David W Fenton does not give any comparison to other optimization techniques. The Microsoft articles rated it highly, after all. There is a variety of views within those threads. Indeed. Notice how a few people needed convincing that clustering existed in Jet at all, after which they dropped out of the discussion. I guess that makes you the winner. After this discussion you can add me to the list of the vanquished, because I don't feel like extending this much longer. I still doubt there is a performance issue between indexing on an arbitrary number such as EmployeeID and and arbitrary PK. Agreed. I can't see either would offer any advantage. Clustering is better suited to meaningful data. Also, I may choose to list employees by lastname, firstname, but have many occasions to order or group by department, startdate, or what have you. You then have to look at the relative usage frequency, importance, etc. I didn't say optimization was easy g. BTW, one of the articles states that the rows will be ordered according to the primary key OR the unique index, but leaves me guessing as to what happens if both exist. PK takes precedence, as advised by Lyle Fairfield in the first thread you linked to. I do know that there is no direct way of opening the back end (and getting to the table data that way) without going through the secure mdw file Users have full permissions to the table links in the FE, and limited permissions (according to the group of which they are a member) to the actual BE tables. What you described with the Excel thing is in fact a back-door update, undertaken by somebody who is deliberately and knowingly circumventing the intended route for entering data. In my book that is hacking, not an innocent attempt to be more efficient. If they've used their own credentials via your mdw (i.e. utilizing your security mesures rather than circuventing them) to connect to the data in the BE from within Excel then in my book that is legitimate usage and IMO should be accommodated. Is there documentation that what you describe is possible? Jamie. -- |
#57
|
|||
|
|||
How do you mulitply in a field?
On 24 Aug, 18:52, "BruceM" wrote:
If they've used their own credentials via your mdw (i.e. utilizing your security mesures rather than circuventing them) to connect to the data in the BE from within Excel then in my book that is legitimate usage and IMO should be accommodated. Is there documentation that what you describe is possible? You serious? Try these KB articles: ACC97: How to Use IDC Files to Query a Secure Microsoft Access Database http://support.microsoft.com/kb/161172 How to open a secured Access database in ADO through OLE DB http://support.microsoft.com/kb/191754 How To Open an Access Database with User Security [using DAO] http://support.microsoft.com/kb/163002 How to open a secured Microsoft Access database in Visual C++ http://support.microsoft.com/kb/270663 HOW TO: Connect to a Security-Enhanced Microsoft Access Database by Using Microsoft Visual Studio .NET http://support.microsoft.com/kb/823927 How To Use ADO to Refresh/Create Linked Table for Password Secured Jet 4.0 Database http://support.microsoft.com/kb/240222 etc etc Try these simple steps: Open Excel 2003. On the menu, choose: Data, Import External Data, New Database Query. In the resulting 'Choose Data Source' dialog, select New Data Source, and click OK. In the resulting, 'Create New Data Source' dialog, step 1: enter a name (for the DSN), step 2: choose Microsoft Access Driver, step 3: click the 'Connect' button. In the resulting 'ODBC Microsoft Access Setup' dialog, choose you mdb 'Database' (I recommend you use a copy here), choose your mdw 'System Database', click the 'Advanced' button and enter a valid username and password combination. OK to each dialog to get you back to the 'Choose Data Source' dialog, select the newly- created DSN and hit OK to take you to the main MSQuery dialog (cancel the 'Add tables' dialog if it shows on start up). Hit the SQL button, type in a Jet syntax SQL statement (e.g. CREATE VIEW DropMe AS SELECT 1, click the OK button, continue past the odd 'query cannot be displayed graphically' message and the SQL will be executed. Jamie. -- |
Thread Tools | |
Display Modes | |
|
|