|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Request Advice: Big Tables, Expr. Complex ErrorIt was suggested to me the reason why I'm getting a "Expression is to
Complex" error message in my local ADO.Net application is because the generated update query has too many AND's in the WHERE clause and the the MS Access Jet provider is limited for this many fields (columns) ( max is 100 fields for DA). The data source/set is a MS-Access Main table with 110 fields (columns). The table grew from a little table to a big table overnight because of spec changes. I know how to normilize the table in Access but not in ADO.Net environment plus, that seems like a lot of work since the application is almost done. What are my options to remove the Expression is to Complex error message ? Since I'm using MS-Access on the server, is it time to step-up to MS-SQL Server? Does SQL Serverl use the Jet provider? Do you think the complex error will go away with SQL Server? Comments? Steve Perhaps you could make a query that filters most of your records (not all of
them) and then you can use the power of your programming language to loop through all of the columns and only pick the records that match your criteria. Remember that if you are using such big criteria then you won't be able to build a small index and you will end up scanning the WHOLE table every time you query your table. This is even worst with Jet because if your database is in a network server Jet will have to return the whole table to your computer so it can run the query (because of the lack of a good Index). The best thing for you to do is to build an Index with the columns that matter most on your query and then use that index to retrieve the minimum amount of row that you can. After that you can do what I suggested at the beginning of my respond to filter out the rows that are a real match. Just a suggestion. Show quote "Steve B." <Ste***@discussions.microsoft.com> wrote in message news:4B3E8A36-C24C-49C5-8D13-0AA394712E37@microsoft.com... > It was suggested to me the reason why I'm getting a "Expression is to > Complex" error message in my local ADO.Net application is because the > generated update query has too many AND's in the WHERE clause and the the > MS > Access Jet provider is limited for this many fields (columns) ( max is 100 > fields for DA). > > The data source/set is a MS-Access Main table with 110 fields (columns). > The table grew from a little table to a big table overnight because of > spec > changes. I know how to normilize the table in Access but not in ADO.Net > environment plus, that seems like a lot of work since the application is > almost done. > > What are my options to remove the Expression is to Complex error message ? > Since I'm using MS-Access on the server, is it time to step-up to MS-SQL > Server? Does SQL Serverl use the Jet provider? Do you think the complex > error will go away with SQL Server? Comments? > > Steve > > Thanks Rene for the recommendation. The problem is almost all the fields are
filled with different data. In general, these are aircraft parts. The parts have about 20 fields of general info and then 30 characteristics of manufacture. Each characteristic has 3 fields/characteristic so for a typical part you have 20 + (30 *3) fields containig different info. However, I could do what you suggest for the 20 fields. As a matter of fact maybe I could ask the user if they want to see the 15 or 30 characteristics. Thanks Steve How is the part narrowed? I doubt that one person searches for a part using
all 110 criteria fields do they?? I don't know anything about your system but if I was designing an inventory system I would probably begin by asking the user to filter the key elements of the part. for example: Part (Wing, Tire, Bolt), Manufacturer, etc. This key criteria items could be easily loaded to your application into a combo box dropdown list. Once the user selected this key elements you will run your query and return all the matching rows. The user will then have to pick the part from the list. You could allow him or her to filter the result using your grid filter and sort capabilities. I don't think is a good idea to have such huge criteria, I would suggest rethinking the problem. Show quote "Steve B." <Ste***@discussions.microsoft.com> wrote in message news:B79E920D-D475-4276-A014-785C3A953C0D@microsoft.com... > Thanks Rene for the recommendation. The problem is almost all the fields > are > filled with different data. > > In general, these are aircraft parts. The parts have about 20 fields of > general info and then 30 characteristics of manufacture. Each > characteristic > has 3 fields/characteristic so for a typical part you have 20 + (30 *3) > fields containig different info. However, I could do what you suggest > for > the 20 fields. As a matter of fact maybe I could ask the user if they > want > to see the 15 or 30 characteristics. > > Thanks Steve Steve B. wrote:
> Thanks Rene for the recommendation. The problem is almost all the fields are I think you should change the design of you database, and have a table > filled with different data. > > In general, these are aircraft parts. The parts have about 20 fields of > general info and then 30 characteristics of manufacture. "AircraftParts" with a to many relationship with "Characteristics" AircraftParts ============= PartID (PrimaryKey) PartName Characteristics ============= CharacteristicsID (Primary Key) CharacteristicName CharacteristicField1 CharacteristicField2 CharacteristicField3 PartID(Foreing key to AircraftParts) Each characteristic Show quote > has 3 fields/characteristic so for a typical part you have 20 + (30 *3) > fields containig different info. However, I could do what you suggest for > the 20 fields. As a matter of fact maybe I could ask the user if they want > to see the 15 or 30 characteristics. > > Thanks Steve Steve,
Going to another database don't help you. The 100 columns limit is in ADONET a limit for all wizards. Better is to rearange your database in more tables. (Otherwise you have to do all by hand, by instance naming all first 100 and than adding the other 10 in the code) I hope this helps, Cor What about upgrading to SQL Server? Does SQL Server use the Jet provider? Do
you think the complex error will go away with a SQL Server provider? Comments? Currentlly, I'm using Access on the server. Note: I just stripped the the DA/DS from the wizard/designer and put it in code. I haven't tested it yet but I guess I'm still limited by Jet provider?? Also, as everybody is suggesting, if I normalize the Table into smaller tables how do I incorporate multiple tables in to the ADO.Net Interface? Web site available with info? Steve Show quote "Cor Ligthert [MVP]" wrote: > Steve, > > Going to another database don't help you. > The 100 columns limit is in ADONET a limit for all wizards. > > Better is to rearange your database in more tables. > > (Otherwise you have to do all by hand, by instance naming all first 100 and > than adding the other 10 in the code) > > I hope this helps, > > Cor > > > > What about upgrading to SQL Server? Does SQL Server use the Jet provider? You could if you wanted to but that won't be the most sensible thing to do. If you are going to use SQL Server then you should use the "Microsoft OLE DB Provider for SQL Server" provider. Thanks, thats my question how much different is Microsoft OLE DB
Provider for SQL Server then the Microsoft Jet provider AND will the Microsoft OLE DB Provider address my Expression to Complex error message Steve Show quote "Rene" wrote: > > What about upgrading to SQL Server? Does SQL Server use the Jet provider? > > You could if you wanted to but that won't be the most sensible thing to do. > If you are going to use SQL Server then you should use the "Microsoft OLE DB > Provider for SQL Server" provider. > > > > Thanks, thats my question how much different is Microsoft OLE DB I doubt that anyone here can answer that question, you will have to try it > Provider for SQL Server then the Microsoft Jet provider AND will the > Microsoft OLE DB Provider address my Expression to Complex error message and see what happens. I believe that Access (at least the one on the Office XP version) come with an Upsizing Wizard to move your database to an SQL server easily, after that all you have to do (theorically) is to change the connection string to connect to the SQL server instead of Access and try it. For better results you should use the SQL data objects. Show quote > > Steve > > "Rene" wrote: > >> > What about upgrading to SQL Server? Does SQL Server use the Jet >> > provider? >> >> You could if you wanted to but that won't be the most sensible thing to >> do. >> If you are going to use SQL Server then you should use the "Microsoft OLE >> DB >> Provider for SQL Server" provider. >> >> >> Hi Rene
It's strange you say that because just yesterday I was talking to somebody about the problem and then I remembered reading a MSDN newsletter that you could get 180 day trial version of the new SQL Server/VS. So, in about 5 minutes I'm going to order the trial version of SQL Server and try it on the table. FYI - MS Access allows 256 columns I really don't think 110 fields is too many but, as I order SQL Server/VS evaluation copy I'm going to break up the table just in case SQL Server doesn't solve my problem and do what the experts say (normalize the table). I'm also going to a SQL Server/VS launch event on 12/15 in Boston https://www.tryvs2005.com/profile.aspx Thanks Rene Steve Show quote "Rene" wrote: > > Thanks, thats my question how much different is Microsoft OLE DB > > Provider for SQL Server then the Microsoft Jet provider AND will the > > Microsoft OLE DB Provider address my Expression to Complex error message > > I doubt that anyone here can answer that question, you will have to try it > and see what happens. I believe that Access (at least the one on the Office > XP version) come with an Upsizing Wizard to move your database to an SQL > server easily, after that all you have to do (theorically) is to change the > connection string to connect to the SQL server instead of Access and try it. > > For better results you should use the SQL data objects. > > > > > > > Steve > > > > "Rene" wrote: > > > >> > What about upgrading to SQL Server? Does SQL Server use the Jet > >> > provider? > >> > >> You could if you wanted to but that won't be the most sensible thing to > >> do. > >> If you are going to use SQL Server then you should use the "Microsoft OLE > >> DB > >> Provider for SQL Server" provider. > >> > >> > >> > > >  |
|||||||||||||||||||||||
Other interesting topics