April 22, 2008 at 12:06 am
My manager has directed me to so called redevelop one of our applications written in VB 6, which currently points to and Access 2000 database.
He has directed me to migrate the tables/queries/data etc from the access database to our SQL Server database and then use link tables from the Access database to the SQL Server database. This way he says the database is on a production quality database but we don't need to make any code changes. The current code can talk to the sql database thought the Access database link tables.
I really really hate the idea, but I cannot come up with any arguments to persuade him other wise.
So I guess I a looking for some arguments I can put to my manager to convince him not to do this.
April 22, 2008 at 5:53 am
Sorry to say, it's not a horrible temporary solution. I am not a big fan of using MS Access for any production system, but as a migration strategy, creating linked tables is not the worst place to start. This is, of course, assuming that you will then migrate everything over to directly connecting to the SQL server a piece at a time.
Leaving it permanently connecting through the access database and linked tables is probably a bad idea. Probably the biggest problem will be performance. Every call will use a Jet driver to connect to the MS Access database and then (most likely) an OLEDB/ODBC connection to the SQL Server. If you make a call that joins two tables in the access database (not using a pass-through query) you will actually force ODBC to do the query rather than allowing the SQL server to do the work. This will essentially remove all of your indexing and any query optimization the SQL Server could do for you.
This could be pretty easily illustrated by running a query on the Access database with the linked servers and tracing it on the SQL Server. Then, run the same query on the SQL Server through management studio. You will see a huge difference.
The other problem you are likely to have is simple reliability and scalibility. Linked tables or not, MS Access is not well designed for multi-user environments and you will find a limit to the number of users your application will support.
April 22, 2008 at 7:09 am
Hmmm...
Access Issues:
- Multi-user/network corruption
- Huge Loss of indexing/performance
- Driver bottleneck.
- Jet engine vs sql engine
- scalability
Need I go on....
Convince your boss to byte the bullet and do the conversion.
April 23, 2008 at 1:35 am
I suggest you use an Access Project, then all the calls will not be through ODBC or Jet.
I agree it's not the best solution for a production system but it can be a quick to create solution with quite passable results.
April 23, 2008 at 7:17 am
If you have less than 10 concurrent users, then this might work. Otherwise, the performance will always be an issue.
Particularly, the power Access useres will treat these linked tables as native access tables, and filter data against it, then leave the session open. You will see deadlocks popup all over your SPIDs.
I have heard that new version of Access is doing better on locking up table though. They only lock up rows.
April 23, 2008 at 7:31 am
Tim Curtin (4/22/2008)
Hmmm...Access Issues:
- Multi-user/network corruption
- Huge Loss of indexing/performance
- Driver bottleneck.
- Jet engine vs sql engine
- scalability
Need I go on....
Convince your boss to byte the bullet and do the conversion.
If you've migrated the tables to SQL server, then the indexing/performance/driver bottleneck/multi-user/network corruption are out the window as disadvantages...
Assuming you use this correctly - it's actually a viable scenario for a while. The fact that the original config has been running for so long would tend to point to the fact that this wouldn't run any worse than the original config, and might run quite a bit faster if you use some of the advantages you now have access to (like pass-though queries, linked views, ADO command objects, etc...)
And no - it's not a complete solution, so you can't just limit yourself to migrating the tables and call it a day. But I've had apps in play for years under just this scenario while a conversion/transition happens.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 23, 2008 at 8:37 am
Maintenance problem:
In Access, when you create a link to an ODBC data source, Access will memorize the schema of the underlying table. If you make a change to the table, you will need to open Access and manually refresh the link.
April 24, 2008 at 12:11 am
Once you have migrated the tables/queries etc from Access to SQL server why can't your VB6 application link to SQL server directly?
April 24, 2008 at 10:23 am
WILLIAM MITCHELL (4/23/2008)
Maintenance problem:In Access, when you create a link to an ODBC data source, Access will memorize the schema of the underlying table. If you make a change to the table, you will need to open Access and manually refresh the link.
I use an autostart module to handle that...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 24, 2008 at 10:35 am
Matt,
The OP was going to connect from VB6 thru Access to SQL Server, and I don't think that AutoExec would run in that scenario.
April 24, 2008 at 10:47 am
create a Sub Main in VB5 and run the 'startup' code there.
Why not just rewrite the app as an asp.net app with SQL Server? MS isn't supporting VB6 anymore.
April 24, 2008 at 11:33 am
WILLIAM MITCHELL (4/24/2008)
Matt,The OP was going to connect from VB6 thru Access to SQL Server, and I don't think that AutoExec would run in that scenario.
I guess I'm making assumptions as to what is doing the heavy lifting. I was assuming that the VB6 was actually automating Acc2000 (meaning - opening some amount of Access objects and screens, etc...), and actually using some of the UI objects in Access. If the only thing it's doing is going there for data, then yes - something else would be needed.
Of course - if that's the case, then the VB program has ADO/DAO calls that should be updated to point at SQL server directly, and take Access out of the mix altogether.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 24, 2008 at 12:04 pm
I was assuming that Access was just going to be an additional layer in-between the VB app and SQL Server. If the VB app uses ADO then just changing the connection strings could avoid going thru Access, but if the app uses DAO OpenDatabase methods then that would be a nightmare for sure.
BTW as far as MS is concerned, Classic VB is dead. But a recent issue of Redmond Developer News mentions a Gartner Inc. report that 11 billion lines of VB6 code still exist out there in the enterprise, and that the VB runtime will continue to be supported for the lifetime of Vista.
The king is dead, long live the king.
April 27, 2008 at 7:31 pm
Thanks for the reply guys.
The vb6 app does use DAO (cringe) and yes the access database would just become an additional layer between the vb6 app and the sql server. That is why they are going this way, if it was ADO as you have already mentioned we could just change the connection string.
I would like to re write the code in .NET but if management are to tight to pay for a proper database conversion project I doubt they are going to cough up the cash for a full rewrite.
April 28, 2008 at 9:04 am
Simon, it should not be that difficult (only tedious) to go from VB6 /ADO/SQL. There's seems to be no reason to use access in the middle.
Most of the ADO/DAO objects are similar and you may have to rename some enums to make it work. Watch out for inadvertent use of client side cursors when you switch to ADO.
HTH
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply