How to convert an mdb to an accdb in VBA

I need to convert the backend database of my application from an mdb to an accdb.  I have about 200 installations so I need to do this as a data conversion in VBA.

I am considering:
  1. Installing an empty accdb containing the tables but no data.  
  2. Then, for each table in the mdb, adding the records to the corresponding table in the accdb.  
  3. Finally, unlink the table in the mdb and relink to the tables in the accdb.

Does this sound reasonable or is there a better way?

Thanks in advance

Clive
Clive BeatonAccess DeveloperAsked:
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.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Do you have any Relationships set up between the tables ?  If so, that will likely complicate importing data into the empty tables ...
0
Clive BeatonAccess DeveloperAuthor Commented:
Hi Joe,
I do have relationships between the tables.  I intend to keep them in the front end only.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Keep what in the Front End ?
Do you have a split configuration, Front End and Back End ?
If so ... Relationships only occur in the Back End.  If you have Relationships on the Back End, and you open the Relationships window in the Front End ... Access will display the Relationships of all linked table ... mainly for convenience. So don't fool yourself about Relationships actually residing in the Front End.  The Relationships you 'see' in the Front End cannot be modified from within the Front End.
0

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Clive BeatonAccess DeveloperAuthor Commented:
Thanks, Joe.  I didn't know that.  Yes, backend and frontend.  I can create the relationships in code.  It's tedious but I've done it before.  How about if I create the relationships in the 'empty' accdb?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Clive,

 There is the Application.ConvertAccessProject Method:

https://msdn.microsoft.com/en-us/vba/access-vba/articles/application-convertaccessproject-method-access

 Specify a source, destination file, and the format you want.

Jim.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"How about if I create the relationships in the 'empty' accdb?"
Well I guess. But keep in mind that ... for example ... in a One To Many Relationship (typical Lookup table situation), you will need the records to be in the One table before you can import records - that need the Key from the One table - into the Many table.  This is what I am referring to.
And if you have a Many To Many Relationship with a 'junction table' ... that you have to take a similar paradigm into account.
So mainly ... you just have to plan ahead.

I'm facing a similar challenge here at work.  I'm about to convert an Access backend with a somewhat table structure into SQL Server. Creating the tables in SQL Server is no problem. But, because there are several One To Many relationships, getting the data in is a tad more challenge.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
@ Jim.
re "Application.ConvertAccessProject Method:"

Does this take all of this into account ?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Does this take all of this into account ?>>

  Not sure what you mean and maybe I'm missing something here, but you can convert a MDB to a accdb with it.

 Jim.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Actually, ignore that question :-)
0
PatHartmanCommented:
I do have relationships between the tables.  I intend to keep them in the front end only.
Joe told you that the relationships need to exist in the BE but he didn't explain why.  The "WHY" is because in order for the database engine to enforce the relationships (which is the whole point of defining them), it needs to know about them.  It has no way of knowing about them if they are in some random FE on some other server or local drive.  The BE can be used by any Access database that links to the tables in it or it could be used by some app created by VB.Net or even a web page.  None of those other FE's would have any knowledge of the FE where you think you defined the relationships.  This is simply the way ALL database engines work.  Even though you can create queries that join tables from different BE's including different file types such as SQL Server - ACE or Oracle - SQL Server, etc.,, it is not possible to enforce RI except that which is defined within the database that holds a table.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"but he didn't explain why."
really. So what?
But I *did* explain *why* he would be fooling himself by looking at them in the FE, which was the relevant point at the moment. Not to mention that the OP was not asking why Relationships s/b created in the BE
0
PatHartmanCommented:
Joe, I wasn't intending to offend you or to be critical.  I was attempting to acknowledge that you provided the salient information and that I wasn't going to contradict it.  We can't possibly tell people everything we know all at once.  Mostly because they aren't going to be able to absorb it but in this case,  I could tell by the follow on question that Clive probably needed to know why or he was going to keep looking for a reason to not do what you recommended that he do.
0
Clive BeatonAccess DeveloperAuthor Commented:
Thank you all very much.  I can't believe I didn't understand that in over 2 decades of Access development.  BTW, I've always used relationships in the backend.

Clive
0
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
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.