I have an Access97 db and I converted it to Access2K. It is actually 2dbs because one links to another. We will call it db1 and db1link. In need to have the exact same dbs (with some minor modifications) on the same machine. So I need it to be db2 and db2link. Where db1 is the front end that writes to db1link and db2 is the front end that writes to db2link. Is there any way to do this without re-writing all of the queries, reports and VB code? I looked around and could not find simular questions. Thanks in advance.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Access DB 97 to 2K - linked tables
- Thread starter bkast3
- Start date
bkast3,
You may have a significant number of issues to address here that may be better served asking your question in the Access forum, but I will try to address one or two.
If I read this correctly you now have 2 different front-ends and 2 different back-ends. Now you need to link the new db2 front-end to its back-end db2link. You do this by going into db2 and using the Tools, Database Utilities, Linked Table Manager to point to the correct database to use db2link. At that point the front-end and the back-end for db2 and db2link are completely independent of db1 and db1link on the surface.
However, there may be some issues with the VBA code. Someone will need to ensure that there are no hard-coded paths that link back to the original db1 files in any fashion. Also, you may have some issues between ADO and DAO references in your code. Some can be handled via Tools, References in the VBA Editor, but depending on how you distribute the database this may not be enough.
Now, all that being said, I am confused about you asking how to set this up since you stated that you converted the database. If you've converted it properly then you had to rename the new version (I usually suffix them with _ac97 or _ac2k as needed).
However, if you are asking of a quick way to perform the entire thing then you can convert the db1 and db1link files and rename them upon conversion to the new version. You do this by opening them in Access 2000 and it will ask you to convert or open the database. Then use the Linked Table Manager to set the proper link. I STRONGLY SUGGEST that you backup BOTH files before proceeding with any of this, just in case. Strange things happen upon conversion and if you have a Switchboard I would not use the convert method, but the following method.
Otherwise, I would create a new Access 2000 database and import all the items via Edit, Get External Data, Import from the Access 97 database into the new database. I would import all objects except for the Switchboard Form and its associated Switchboard Items table. They don't really convert well and should really be recreated or you should create your own. Do the same for both the front-end and back-end. Then use the Linked Table Manger as needed.
Hope this helps.
You may have a significant number of issues to address here that may be better served asking your question in the Access forum, but I will try to address one or two.
If I read this correctly you now have 2 different front-ends and 2 different back-ends. Now you need to link the new db2 front-end to its back-end db2link. You do this by going into db2 and using the Tools, Database Utilities, Linked Table Manager to point to the correct database to use db2link. At that point the front-end and the back-end for db2 and db2link are completely independent of db1 and db1link on the surface.
However, there may be some issues with the VBA code. Someone will need to ensure that there are no hard-coded paths that link back to the original db1 files in any fashion. Also, you may have some issues between ADO and DAO references in your code. Some can be handled via Tools, References in the VBA Editor, but depending on how you distribute the database this may not be enough.
Now, all that being said, I am confused about you asking how to set this up since you stated that you converted the database. If you've converted it properly then you had to rename the new version (I usually suffix them with _ac97 or _ac2k as needed).
However, if you are asking of a quick way to perform the entire thing then you can convert the db1 and db1link files and rename them upon conversion to the new version. You do this by opening them in Access 2000 and it will ask you to convert or open the database. Then use the Linked Table Manager to set the proper link. I STRONGLY SUGGEST that you backup BOTH files before proceeding with any of this, just in case. Strange things happen upon conversion and if you have a Switchboard I would not use the convert method, but the following method.
Otherwise, I would create a new Access 2000 database and import all the items via Edit, Get External Data, Import from the Access 97 database into the new database. I would import all objects except for the Switchboard Form and its associated Switchboard Items table. They don't really convert well and should really be recreated or you should create your own. Do the same for both the front-end and back-end. Then use the Linked Table Manger as needed.
Hope this helps.
- Thread starter
- #3
I think that you may have answered some of the problem. I will try to clear up the question. There is a switchboard on the front end. I had a 97db created to track clients and services provided to them, and a few other things. (5 total linked tables). We now have to serve a different client population that we will bill an insurance co. I was informed that the old db would work for the data the supervisors needed except that they had to keep these clients separate due to some of the previous written reports. (they would pull all services to a billing form so we can bill the county, which now this set of clients will be billed to an insurance)So all I did was copy the 97db and then convert it to 2K (because that is what we use for all other dbs other than this one due to conversion issues. Trying to convert 300,000 plus rows of data from one table.) I then added some calculations to some reports and added a few fields to some tables and fixed some VB code and it appears to work fine. My only problem is that no matter what I name the front end it looks for the same backend. It's true name is HFIDataConvert. So if this changes any answers then let me know, or at least acknowledge that you read this, and I will look at the few things you posted. I currently will have to run these on two different machines, which I propably would have to because of them being on two different access versions, 97 and 2K.
Thanks.
Brian
Thanks.
Brian
- Status
Similar threads
- Locked
- Question
- Locked
- Question