Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2009
    Posts
    67

    Unanswered: Access limitations and dangers of linked tables?

    I'm curious as to when I should consider moving to SQL Server?

    We're managing something like 12,000 active rows for one table and no clue how many rows in another table as it's parsing an XLS file & 1 row in there equates to about 1 row in 4 different tables.

    I've been working with the original designer of this to make many normalization updates & I've made significant headway there. Now I'm running into him wanting to keep separate databases for different portions of the application for fear of data corruption nuking the whole DB.

    I'm a relative Access noob (11yr experience web developer in ASP/SQL Server & PHP/MySQL) & have just learned about linking tables and JOINS across different databases. How much concern do we have of potential corruption if tables reside in same DB or if they are linked in?

    The Electrical Engineer PhD I'm working for has developed a pretty high level application (pert near enterprise) all by himself and it got to the point where he was doing more web development than engineering, so he contracted me. Now my problem is, I'm more of a heads down coder vs a consultant on these types of things.

    How do I convince him to take the SQL Server that's been offered by Corporate IT, lock it down with a password, end using Windows Authentication for application access, etc etc?

    If I think of anything else, I'll add it, but this is where I'm at now.

    Thanks in advance . . .

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Splitting a database (ie. frontend/backend) is always a good idea but isn't necessarily due to data corruption. When you have multiple users in an mdb file, the mdb file itself can become corrupt/locked (not the data tables.) Even with a split database, if the frontend is designed poorly or the structure is setup poorly, you can get data corruption (ex: orphaned records due to an incorrect table structure or ex: a form designed poorly so it doesn't populate the neccessary fields in the table.) But there is a big difference between a locked mdb file and corrupt data tables.

    On a network type environment, the problem you deal with is hiccups or user's losing connection to the mdb which corrupts the mdb file (not the data). What happens when a user loses that connection, the mdb file itself becomes corrupt and needs to be repaired. Sometimes this can affect the data itself but typically it only affects the mdb file and prevents other users from opening the mdb file (ie. "Locked by another user" type errors). This is essentially the associated *.ldb file that get's created when a user opens an mdb file and that *.ldb file becomes corrupt when someone loses connection to the mdb file.

    If you go the route of splitting the mdb file (whether the backend be in another mdb file or on SQL Server) and all users are still using the same frontend, you have the same problem with a possible corrupt mdb file (again, not data.) To eliminate this, typically what's done after the data is split into a separate mdb file (or SQL Server), is that users are given their own frontends (or you use a script such as in the code bank) so that only the user's specific frontend needs repairing when it becomes corrupt.

    SQL Server has many, many benefits but the deciding factor to use SQL Server or another mdb file to hold the data tables should not be based on an mdb file becoming corrupt due to a locking *.ldb file. For example, say you split the backend/frontend and a user opens the frontend mdb file and then minimizes that mdb file for 5 hours without doing anything. Regardless if the backend is on SQL Server or in another mdb file, the problem lays with the frontend *.ldb file becoming confused on whether that user is still utilizing the frontend mdb file and will 'lock' the mdb file preventing other users from opening the same frontend mdb file.

    In regards to promoting SQL Server over using an MSAccess mdb file, there are again, many, many benefits. Data corruption though is usually not the convincing factor as you could still have data corruption on SQL Server with a poorly designed structure/frontend.

    As another note, you should NOT separate the data tables themselves into separate databases (unless you're dealing with an enterprise level application.) If it's just 1 application, keep the data tables in the same backend database.
    Last edited by pkstormy; 11-03-09 at 14:19.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Oct 2009
    Posts
    67
    Something right away I've forgotten to include by reading the 1st line of your response pkstormy:

    This is all done in Classic ASP front end, Access back end, ODBC less, windows authentication on selected .asp files!

    Sorry, I had meant to include that in my original post.

    I just spoke w/the engineer and now curious about the performance issue with a completely linked MDB (all the tables reside in other MDBs)? See screenshot.
    Attached Thumbnails Attached Thumbnails table-structure.bmp  

  4. #4
    Join Date
    Oct 2009
    Posts
    67
    Quote Originally Posted by pkstormy View Post
    SQL Server has many, many benefits but the deciding factor to use SQL Server or another mdb file to hold the data tables should not be based on an mdb file becoming corrupt due to a locking *.ldb file. For example, say you split the backend/frontend and a user opens the frontend mdb file and then minimizes that mdb file for 5 hours without doing anything. Regardless if the backend is on SQL Server or in another mdb file, the problem lays with the frontend *.ldb file becoming confused on whether that user is still utilizing the frontend mdb file and will 'lock' the mdb file preventing other users from opening the same frontend mdb file.

    In regards to promoting SQL Server over using an MSAccess mdb file, there are again, many, many benefits. Data corruption though is usually not the convincing factor as you could still have data corruption on SQL Server with a poorly designed structure/frontend.

    As another note, you should NOT separate the data tables themselves into separate databases (unless you're dealing with an enterprise level application.) If it's just 1 application, keep the data tables in the same backend database.
    He's expressed his fear as users from group A corrupting data for users in group B. There are essentially 2 different applications here, but they'll EVENTUALLY need some interaction. They also rely on many of the same tables (Vendors/Components/etc, again see previous screenshot).

    The portion of the application he's built is the one with the most rows as it's existed for 6 months. He's afraid anything the new application I'm building might some how affect the data of the tables we're not yet touching. Even when we do get to "touching" them, it's basically requesting data only, group A will never make significant modifications to the existing group B data (eventually setting a flag in one table about the existence of data in group B needing attention from group A).

    LOL, hope that makes some sense.

    As for separating the data into different tables, like I said, it's virtually enterprise. We're managing the components that go into products and for a pretty high level publicly traded company in at least 30 countries. So, on one hand you can say it's enterprise, but it's built by one guy for pretty much one group in that company. The products Oracle server db is managed by another group and they have it locked down we're not allowed to access it!

    I just don't get why he'd NOT use SQL Server with access to it. Neither of us are DBAs, which is one more headache, but it would be managed by Corporate IT, where the rack mount IIS server is for this whole project.

  5. #5
    Join Date
    Oct 2009
    Posts
    340
    with only 12k records and only using Access for the back end....there is no compelling reason to go to the sqlserver due to a fear of corruption....

  6. #6
    Join Date
    Oct 2009
    Posts
    67
    Quote Originally Posted by NTC View Post
    with only 12k records and only using Access for the back end....there is no compelling reason to go to the sqlserver due to a fear of corruption....
    12k in one table. And that's not likely to be the largest table. There are 31 tables amid the two applications, with lots of lookup tables.

    IE, the component can be made of many subcomponents. In the one example I've figured out (locally), it has 46rows in subcomponents table, 7 in another, 10 in another. I can forsee the subcomponent table growing rapidly.

    I just want to make sure we won't hit any bottlenecks with Access as I'm not sure of the performance peak of it.

    Thanks for the feedback.

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    yes NTC you are right

    But if they can jump to SQL do it
    I would make the jump to SQL only nowing database grow and grow (data that is) if they have it use it

    and yes there will have to be a few asp pages rewritten

    with only 12k of records it be easyer to jump now.

    I did and upgrade from access97 to SQL last year took a good weekend to get it all up and going don't forget month work setting everything up
    but once done the system ran HEAPS HEAPS faster they are happy campers now that only because it had over 10 years of data in it
    Last edited by myle; 11-03-09 at 19:59. Reason: speeling
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  8. #8
    Join Date
    Oct 2009
    Posts
    67
    Thanks for the feedback.

    Right now I did some quick calculations on one of the tables I'm working on. If we have 1000 forms uploaded, each with roughly 20 rows, 10 of those are unique (on average, not likely, gonna be more repetitive), that's 10,000 rows.

    On top of that, I will have to sort that data and compare it for unique values coming in. That's 10,000 rows to compare the next 20 rows to.

    Which brings me to my next question . . .

    If I wanted unique values of the incoming 20 rows compared to whats already in the database, what's the best way to find those unique items? I can do it programatically in ASP with arrays or in the database with maybe a temp table or stored procedure?

    I'm working on the ASP algorithm now, but want to consider both options.

    SELECT ID, name
    FROM table
    WHERE name IN (list, of, names)

    Gives me the duplicates and I can assign the ID to the list of names items it matches to insert into a lookup table.

    (I do a lot of thinking while typing!)

  9. #9
    Join Date
    Oct 2009
    Posts
    340
    if you are an experience sqlserver manager/developer - - then I wouldn't understand why you would begin with a web project using Access as the back end file....

    if you are not experienced in managing/developing sqlserver - - - then that is a very big issue unto itself ; as this is the Access forum - - I would say - if a project's requirements can be met with an Access solution - stay with Access because learning sqlserver is a very big task.

    but whether or not Access is the correct solution is the key. Access is a desktop solution with all the query ability in the front end. The back end is a passive table holder. sqlserver has the query ability in the back end server and so selecting the correct tool for the job is key...

  10. #10
    Join Date
    Oct 2009
    Posts
    67
    The one thing we do have going for us:
    This is an internal app only. Not public facing . . . AT ALL. Unless someone can accost the WAN to intercept.

    So, one of the aspects of "being an experienced SQL DBA" (essentially what you're saying) in regards to security is a lot more lax.

    As for being experienced enough to design a proper structure or understanding the query differences/limitations, I think for what we're doing, that's negligible as well. 99.99% of what we're doing are SIMPLE SELECT/INSERT/UPDATE statements and in fact, I can only think of one hairy query which might rank as juvenile to some here:

    Code:
    SELECT                                                             
          components.AgilePartNumber                                    
        , components.ComponentID                                        
        , components.Description                                        
        , components.DateOfEntry                                        
        , components.Customer                                           
        , components.EnteredID                                          
        , components.CompEngrAssignedID                                 
        , Authority.Location                                            
        , Authority.FirstName   AS EnteredByFirstName                   
        , Authority.LastName    AS EnteredByLastName                    
        , Authority_1.FirstName AS AssignedFirstName                    
        , Authority_1.LastName  AS AssignedLastName                     
    FROM Authority                                                     
    RIGHT JOIN                                                         
    (                                                                  
      Authority AS Authority_1                                       
      RIGHT JOIN components                                          
      ON Authority_1.AuthorityID = components.CompEngrAssignedID     
    )                                                                  
    ON Authority.AuthorityID = components.EnteredID                    
    WHERE                                                              
    (                                                                  
        (components.CompEngrChecked = False)                             
        AND                                                              
        (components.IsMostRecent = True)                                 
        AND                                                              
        (                                                                
            ComponentID IN                                               
            (                                                            
                SELECT configuration.ComponentID                         
                FROM profileClassification                               
                LEFT JOIN configuration                                  
                ON  profileClassification.classification                 
                    =                                                    
                    configuration.classification                         
                WHERE                                                    
                (                                                        
                     profileClassification.compEngID                     
                     =                                                   
                     178                                
                )                                                        
                ORDER BY configuration.ComponentID;                      
            )                                                            
        )                                                                
        AND                                                              
        (                                                                
            (                                                            
                ChangeOrderID IN                                         
                (                                                        
                    260,256,254,246,240,225,213,202,197,195,194,193,192,191,190,178
                )                                                        
            )                                                            
            OR                                                           
            (                                                            
                ChangeOrderID = 178                     
            )                                                            
        )                                                                
    )                                                                  
    ORDER BY components.DateOfEntry;

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by LAYGO View Post
    "He's expressed his fear as users from group A corrupting data for users in group B. There are essentially 2 different applications here, but they'll EVENTUALLY need some interaction. They also rely on many of the same tables (Vendors/Components/etc, again see previous screenshot).

    The portion of the application he's built is the one with the most rows as it's existed for 6 months. He's afraid anything the new application I'm building might some how affect the data of the tables we're not yet touching. Even when we do get to "touching" them, it's basically requesting data only, group A will never make significant modifications to the existing group B data (eventually setting a flag in one table about the existence of data in group B needing attention from group A).

    LOL, hope that makes some sense.

    As for separating the data into different tables, like I said, it's virtually enterprise. We're managing the components that go into products and for a pretty high level publicly traded company in at least 30 countries. So, on one hand you can say it's enterprise, but it's built by one guy for pretty much one group in that company. The products Oracle server db is managed by another group and they have it locked down we're not allowed to access it!

    I just don't get why he'd NOT use SQL Server with access to it. Neither of us are DBAs, which is one more headache, but it would be managed by Corporate IT, where the rack mount IIS server is for this whole project.
    Yes. I know exactly where your coming from. Some dba's (mostly Oracle-based) become very protective of their system. It's all about what the IT person is most comfortable with. Personally, I found Oracle a bit more complex than SQL Server and I didn't like the way it assigned permissions. I gave it a test run for 6 months and went back to SQL Server. Oracle/SQL Server are almost 2 different classes of people. Oracle people rarily like Microsoft or SQL Server, given that Oracle was around before SQL Server. You'll good and bad on both. I personally have used all versions of SQL Server and used SQL Server 2000 to store all the US experian credit card data, and all the Wisconsin energy savings/programs/geocoding data (about 10 million records). I never had a problem with SQL Server.

    The only reasons I could think of where I wouldn't want anyone touching my data are:
    1. It's sensitive data and they didn't get appropriate approval from higher authorities.
    2. I can't find a proper way/time to have them update just 1 field.
    3. Other routines might be running and a timing issue would need to be worked out. Some Oracle based systems run 24x7.
    4. They're stubborn. Negative approaches in an IT world are never ideal, especially professionally. I found there's usually a way to get something done IT-wise if enough people give up a bit of their protective power and find a way.

    A company who is Oracle bound will almost always bawk at SQL Server. The 2 products don't always play well together and (I think) changes in Oracle are more difficult. Keep in mind that asking a company to change their database program can be like asking a car company to change all their engines.
    Last edited by pkstormy; 11-05-09 at 23:46.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Oct 2009
    Posts
    340
    in terms of the table, records, lookup counts estimated in this dialog - - none of it - even at its highest estimate range - is a problem for Access; in terms of a straight Access database solution.

    but Access is not really intended to be a web architecture component; and whether or not that web site is on an intranet vs the internet - doesn't change this.

    Having said that, Access is very adequate to be the passive table holder behind a web server. (There is no vba or sql logic in an Access back end file.) So you must put all the sql logic into the web server code. Which is fine. But if you need triggers and other more advanced functionality - - that is what sqlserver was invented for....

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by NTC View Post
    in terms of the table, records, lookup counts estimated in this dialog - - none of it - even at its highest estimate range - is a problem for Access; in terms of a straight Access database solution.

    but Access is not really intended to be a web architecture component; and whether or not that web site is on an intranet vs the internet - doesn't change this.

    Having said that, Access is very adequate to be the passive table holder behind a web server. (There is no vba or sql logic in an Access back end file.) So you must put all the sql logic into the web server code. Which is fine. But if you need triggers and other more advanced functionality - - that is what sqlserver was invented for....
    I agree MSAccess would work for a small recordset, but you still get better data management/performance using a server type backend such as Oracle or SQL Server. The point-in-time backup/restores is what sells me on SQL Server if the data itself is of any importance and updated regularly. (Otherwise you depend on the last backup of the mdb file.)

    I personally like SQL Server as a backend, MSAccess as the frontend, and user's connecting to the network mdb file either via citrix or VPN to update the data via the frontend. For the web frontends (which I leave up to the web developers), I'll design the php scripts or SQL Server stored procedures for them to then use in their code to retrieve/update the data. I use the same type of principle for updating our IVR system by using php scripts to be the gateway between the data and the IVR.
    Last edited by pkstormy; 11-06-09 at 22:07.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  14. #14
    Join Date
    Oct 2009
    Posts
    340
    no disagreement here ....though I would say that if you have sqlserver development skills that I would make the front end with visual studio and not deploy Access at all....

    My view is that Access's role in a web environment is very limited; primarily limited to a web developer that is not oriented to hard core db driven sites but occasionally needs a "table holder"....and doesn't have sqlserver or mysql experience.

    But Access is a great desktop db solution with its built in form generator, report generator and visual query designer. And now that PCs are so powerful - many db applications should be considered as viable with Access. It just seems people attempt to force it into a web environment incorrectly. Depending on the application's requirement I would either go full web architecture and abandon Access - or - abandon a web solution and go full Access and sync the tables between remote locations with the AccessTables.com - Home service.

    Just philosophical opinions of course...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •