vb6 and Access 95 mdb upgrade to vb.net and accdb

Hi Experts
I am working on a 20+year old legacy system written in vb6 and uses Access 95 database. I am going to rewrite this application using Visual Studio(latest) and latest Access Database.
I am trying to find a way to upgrade my databases. we have an mdb file for each job. so conversion has to be done pragmatically. I was thinking of writing up a utility that exports mdb table to .csv file. and Read that .csv file into the latest Access database.. Am i on the right path? if so, how would i go about it? I am thinking o sticking to vb.net to reduce the learning curve. I will  highly appreciate any advice/suggestion.
Thank you
RekhaShahAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark EdwardsChief Technology OfficerCommented:
If you have the latest version of Access, you can write a VBA program to convert a database to a different version.  Just use a file picker or a list of db locations to feed the dbs one-at-a-time to the process.

Simple.... right, or are there other conditions/stipulations....?
Mark EdwardsChief Technology OfficerCommented:
by the way, you don't need the "latest and greatest" version of Access to make a back-end file as the structures there haven't really been changed/improved since version A2007.
PatHartmanCommented:
Access VBA is probably less of a learning curve  from vb6 than going to vb.net

If you are going to redo the app, you should probably think about its future rather than replicating what was a poor design even 20 years ago.

To convert the database  from .mdb to .accdb and continue the current poor design of a separate database for each job, you have to simply open it with a current version of Access and save as in the new .accdb format.  Depending on how many files you have to convert, it would probably just be simpler to do this the curmudgeon way rather than writing code and automating the procedure.

Why are all the Jobs kept in a separate database?  Do you think Amazon keeps a separate database of orders for each customer?  Unless each job is in a different physicaly location and the LANs are not connected, you should really use a single database to hold all the jobs.  The job table needs an identifier so you can always group by job and that allow you to use security to restrict access if that is necessary so that only a certain group of users get access to JobA data and others get JobB, etc.  That allows you to create other groups where higher management can have reports that consolidate all the data from all the jobs.
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You can convert to the new ACE format found in Access 2007 and up, but chances are you will first need to convert it to JET 4.0 using Access 2000-2010.

Starting with A2013, you can only read/convert JET 4.0 format.

I can help with that if needed and yes, you could do this with code.

Jim.
John TsioumprisSoftware & Systems EngineerCommented:
Since you have a working system i think the best way would be
1, change the Access .mdb to Access 2003 format...thus making readable from Access versions till now ..
2. Refactor your code so that you move all the "auxiliary" code into a separate module..this will serve as the in between stage for .NET...first you will "translate" this specific code to .NET ...you can also make a .NET .dll that is accessible from VB6
3. Time to move the "core" of the application to .NET ...the more work you have done in step 2 the less here and of course less headaches.
Gustav BrockCIOCommented:
As for the learning curve, you could just as well use C#. Writing VB.NET is not closer to VBA than C# is in any way - it may only appear so by a first glance.

And now you are at it, consider using SQL Server as the backend. The Express edition is free to use, and its baby brother, LocalDB will silently install locally.
RekhaShahAuthor Commented:
I thank you all so much! I really appreciate your input.
Ok, let me explain the purpose of my rewrite and upgrading the application to new platform. This application is developed for an engineering company.
1.  VB6 is a dead product
2.  It is a huge application written very poorly, probably the person who wrote learned while he developed without any  knowledge of basic programming principles.
3. User(Engineer)  enters the data , application stores that data in mdb file and makes binary files and those binary files are loaded in to the chips . Each database is for a different building (which may be under construction ) so may not have access to internet. Many times, i have to modify the database , and give back to the technicians (who are my end users), so it must have a portable database.
4. I have many versions of the same application because we modify the chips and not all building upgrade to the new chips in their system. And now binary files are different so they are not compatible with old version. Thus I maintain lots of versions.

So now if user wants to update to this new app and loads the older database for modification,  my application must update the loaded database to new format  (whichever portable database I end up using).  So basically i have 3-fold work lined up for me : (1) Use newer tools( platform, database, reports)  (2)  rewrite it in an OOP fashion and(3) current database must be updated to new one automatically.

So from all your suggestions,
Gustav, i think, i may take the route of using c# instead of VB.net.

Mark, I am still using Access 95 , so must move to a later version, I might as well use the latest version.

Pat,  Yes, each job is in a different physically location and the LANs are not connected many times, i must assume that there is no internet.

Mike, i might take you up on your help with the code to convert to ACE if i end up using Access.

John, You are saying I should make most of my changes in vb6 application as an intermediate step?

Jim, I will take you up on your offer to help me write the code to update to ACE format

I thank you all and look forward to your further suggestions now that you have better knowledge of what i want to achieve.
Gustav BrockCIOCommented:
The important thing is, that you know exactly what the application must deliver.
As the old app is crappy, and if you have the time, I would certainly go for Visual Studio. It will take longer time to get up to speed, but in the long run you won't regret the investment.

My son-in-law once said: If you start coding in Visual Studio, you won't look back. I forgot that, but after a couple of years I had a project which Access didn't fit at all (picture manipulation) and I turned to VS. After a while, I recalled his warning, and he was right. I haven't looked back - for new projects. Of course, I still use Access a lot, but for maintenance only.
RekhaShahAuthor Commented:
Sorry, I had to work on an urgent project.
 Ready to re-engineer my legacy system.
 I have decided that i will use C# ( i might use WPF Project as opposed to Winform) and Access database . But to update my access 95 database to Access 2013 or the later one, I do need help. Jim Dettman, will you please help me with how i can convert the database through code? i will greatly appreciate your help.
Any suggestion on if i should use WPF or winforms? My boss thinks, we may one day take a Web Based route.
Thank you very much
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Jim, I will take you up on your offer to help me write the code to update to ACE format>>

  Do you have a version of Access prior to 2013?   If not, I will convert the DB for you.

 Jim.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
The current version of Access cannot read A95 databases.  You will have to go back to an earlier version  and perhaps do this in two stages.  A95-A2003 and A2003 - A2019.

To convert, you just open the database in the "new" version and save the database.  So open the A95 database using A2003 and use the save as option.   Given your situation, I don't think you need to convert any existing applications.  I would just start with building the app for new projects going forward.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
If you don't have a version prior to A2013, send the DB to jimdettman@online-computer-services.net and I'll get it converted.

Jim.
RekhaShahAuthor Commented:
Pat Hartman,
I can not ask my clients to convert the database from 95 to 2003 when i release my new application. My users don't have Access Database, all they have is .mdb file and my application. The process should be transparent. That is why i am looking for a code that will convert the database from my new application.  I don't mind writing that application in VB6 that can read both 95 and 2003 mdb, but i still need help how to save it, do i open both access engines?
Thanks
Mark EdwardsChief Technology OfficerCommented:
I don't think you can convert an A95 .mdb file to an Access .accdb file without Access.  If your clients don't have Access, then they, or you, will need to get it, so you can run the conversion, even if you do it programmatically.

It's sounding like you really need to consider SQL Server Express, since an Access db of any kind depends on the client app/machine for data processing.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
That is why i am looking for a code that will convert the database from my new application.  I don't mind writing that application in VB6 that can read both 95 and 2003 mdb, but i still need help how to save it, do i open both access engines?

 Ah, understand now.   Let me dig out the code you need.

Jim.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Let me ask; do you plan to use VB6 for the conversion, or are you going to write the conversion as part of the new app in .Net?

Jim.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
and to John's point, I'm assuming this DB only has data in it, yes?

Jim.
PatHartmanCommented:
I can not ask my clients to convert the database from 95 to 2003 when i release my new application.
Why would you expect the users to do the conversion? YOU need to do the conversion before you send the database to them.  I don't know if there is an ADO or DAO command that will convert the Jet/ACE database to a new version.  That isn't part of standard SQL.

What you could do is send them a new, empty copy of the upgraded database.  Then you would have them run an app that you create that copies data from the old database and appends it to the new database.  This would be a one-time action that happens only when you distribute the upgrade for the first time.  Your app can contain code to check the Access version of the .mdb and use that to trigger the code yourself without user intervention.  You should keep the old database, just rename it, in case you need to go back to the prior version.
RekhaShahAuthor Commented:
Jim,
 I can either write a conversion program in vb6 or as a part of the new application. It will be easier for me to write the conversion program in VB6.
 .mdb files have only data .
Pat, that is exactly what i am trying to do.  Write a conversion program for which i needed help.
RekhaShahAuthor Commented:
MArk,
Are SQL Server Express files portable?  Many times, i have to change the data in Access for the users  and give them the database.
Mark EdwardsChief Technology OfficerCommented:
Depends on what you mean as "portable".  You can make a backup of a SQL Server/Express database (SQL Server is an app (commonly referred to a "server") that manages/runs multiple SQL Server databases) and then add/restore it to another SQL Server/Express server installation somewhere else.  I do it all the time.  There's even sample/practice databases (example: "AdventureWorks") that you can get off the internet and add to your own SQL Server/Express installation.
Mark EdwardsChief Technology OfficerCommented:
It should also be mentioned that one of the major reasons for using SQL Server is so you can put both the data AND the processing in the SQL Server db, then it doesn't matter what the front-end is (Access, VB6, VB.Net, Web, Excel...whatever....), you can use any front end to run it as long as it can send data requests to SQL Server.  Just slap on a new UI app and point to SQL Server and make the requests.

However, as with any db, a lot depends on the settings in the host app (SQL Server installation).
John TsioumprisSoftware & Systems EngineerCommented:
Isn't wiser to continue the talk to a new question...points awarded so case closed...
RekhaShahAuthor Commented:
Actually it is not, because we are still trying to figure out the best solution, process , IDE, Software,database etc
I can post it as a new question if you are concerned about the points.
Mark EdwardsChief Technology OfficerCommented:
I should also mention that there is an "Access-to-SQL Server" upsizing tool that someone may let you have a copy of (Microsoft handed it out free) to use for converting your data-only Access databases to SQL Server databases - since you only have tables in your db's, it should do everything for you - you just select the Access db, the target SQL Server, and fire.
Mark EdwardsChief Technology OfficerCommented:
Yea, that's what John is talking about... Once a solution is selected, no one else can get points for further contributions.
PatHartmanCommented:
Pat, that is exactly what i am trying to do.  Write a conversion program for which i needed help.
I explained how to do it.  You are currently being helped by Access people.  If you need help with code, you should be using a C# or VB.Net forum.

If there is no ADO/DAO way to convert the database - and I can't find one but that doesn't mean there isn't one, then use the method I suggested which sends the user an empty database in the new format.  Your code will simply run a bunch of append queries to copy data from a to b.  If you have used RI, you will need to be very careful to run the append queries in the sequence needed so that parent table data exists before child records get added.  There isn't actually any difficult code logic that you need.  If you know how to run a query in your target language, that should be all that is necessary.

I'm not sure what your programming background is but you can probably do this with an Access FE if you are comfortable with VBA.  Your clients DO NOT need to have MS Access installed.  They can install the Access runtime engine which is free.  You can build an install script to do it for them or you can send a copy of the runtime engine or a link to where they can download it.
PatHartmanCommented:
I can post it as a new question if you are concerned about the points.
Points are the currency you use to pay the experts who spend their own time to help you for free for something you earn actual money for.  This is what Socialism is all about.  From experts according to their ability, to others according to their need.
RekhaShahAuthor Commented:
My apologies. I am sorry if I have offended any one. I have not used this site in a long time and was not sure which forum to use to post my question.  I thought once i have some help from Jim with the code to convert database, i will be done. I did not know that once you accept the solution , you can not discuss any further.
I will be mindful of it in future.
And i am very grateful for all of you experts you spend their own time to help people who need. I totally get it!
John TsioumprisSoftware & Systems EngineerCommented:
Don't apologize..and i am sorry if somehow i triggered something, but consider the analogy Pat gave....points is the reward for whatever it means for each and everyone that participate in every question...but after points are "earned" its a bit meaningless to continue...
Lets assume that a question is resolved and points awarded and an expert comes with a better solution...simply no points would be awarded no matter how much "better" his/her solution is compared to the originally accepted.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I'm working on the specifics now and will post back in a little bit.

Jim.
Mark EdwardsChief Technology OfficerCommented:
From experts according to their ability, to others according to their need.

...wow Pat... heavy stuff.....  ;-)
Mark EdwardsChief Technology OfficerCommented:
yea, RekahShah….  EE "experts" get points in several ways, such as having their solution selected as the "accepted solution", and posting articles, videos, etc.  As with any "social" platform, EE has its own high-scoring "point trolls".  Some have even berated an author of a question for not choosing their solution.

….goes with the territory.... don't let it get to you.... "drink coffee and carry on".
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
First, you will need to download and install the ACE Redistributable:

https://www.microsoft.com/en-us/download/details.aspx?id=13255

 Use the 32 bit version.  This  will give you the drives and COM object you need.

Now in VB6, set a reference to:

"Microsoft Office 14.0 Access database engine Object"

and last, you can now use:

dbEngine.CompactDatabase(SrcName, DstName, DstLocale, Options, password)

SrcName is the path\dbname
DstName is the new output DB
DstLocale you can omit

and for Options, you want:   dbVersion120, which will give you the ACE format.

More details on that are here:

https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/dbengine-compactdatabase-method-dao

Jim.
RekhaShahAuthor Commented:
Thank you, Jim,
I will try out tomorrow.
PatHartmanCommented:
No one was offended RekhaShah.  It is just that because membership in this site is not entirely free, many members get the impression that the people who answer their questions get paid to do it.  We do not.  That's all.  The experts on this site contribute their time and expertise to help others.  It is pretty altruistic.  Keeping score with points is a way of knowing if our responses are actually helpful.  And as Mark mentioned, there are many Trolls here who want to be rewarded for participating in as many threads as they can.  For them it is quantity rather than quality.

Looks like Jim found a potential solution.  However, it isn't a magic bullet.  I'm pretty sure it still has the version restrictions I mentioned above.  I.e.  You can't use a current version of the ACE library to convert a version older than A2003.  So, you may still be left using my suggestion.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<You can't use a current version of the ACE library to convert a version older than A2003.  So, you may still be left using my suggestion.>>

  Depends n what you mean by "current".   the ACE 2010 redist will be able to read any format all the way back.  A2013 was the cut-off for the older JET formats.

Jim.
PatHartmanCommented:
If it takes a specific library to do the conversion, then that library must be installed on every computer that will have to do the conversion which seems to be every computer that runs this app.  I work with Access so I don't ever install the Jet/ACE libraries by themselves.  Is there any problem with having multiple versions installed or would everyone have to replace the version they currently use with the A2010 version?  That one is 9 years old (and three version old) already.  How much longer until it is deprecated?
RekhaShahAuthor Commented:
Pat,
I am just in the design phase of the application. But since all the databases will have to be upgraded when they move to this new app, i thought i would take care of this issue first since conversion issue is kind of intimidating to me and it must happen.   This is an in-house application so worst come to worst, engineers  will have to run this 'Conversion' application before they can upgrade to new app, many times they send mdb files to me if they want any changes made that are not supported by the app. So I can have they send me the database to me, i run the conversion from my desktop and give them back. Technicians take the binary files that my application generates, but they also have the laptop with my app and the database for that building. Many times they don't have access to  internet .
Hope this explains the functionality of  my app .
Thank you for your time.
PatHartmanCommented:
I am not confused about the conversion.  I was trying to make it clear that even though you were given code, the code might not actually solve your problem so you need to understand more about what your environment needs to be and where the conversion will happen.

If each of these applications is a data silo and you maintain multiple code bases for multiple versions, I am still at a loss to understand why you need to convert old databases.  But I don't need to understand.  Only YOU need to understand so good luck and I hope the code works for you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.