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:
Does this sound reasonable or is there a better way?
Thanks in advance
Clive
I am considering:
- Installing an empty accdb containing the tables but no data.
- Then, for each table in the mdb, adding the records to the corresponding table in the accdb.
- 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
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Do you have any Relationships set up between the tables ? If so, that will likely complicate importing data into the empty tables ...
ASKER
Hi Joe,
I do have relationships between the tables. I intend to keep them in the front end only.
I do have relationships between the tables. I intend to keep them in the front end only.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"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.
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.
@ Jim.
re "Application.ConvertAccess
Does this take all of this into account ?
re "Application.ConvertAccess
Does this take all of this into account ?
<<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.
Not sure what you mean and maybe I'm missing something here, but you can convert a MDB to a accdb with it.
Jim.
Actually, ignore that question :-)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"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
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
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.
ASKER
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
Clive