working with external databases

[Pachamuthu]

I have four Access databases kept at different places.

DB1. is Toolmaster.accd b, which has tables like "tool" ,etc. It has form also like "frmTool".
The fields of the table are like ToolID, Tooldesc, etc

DB2. is Toollife.accdb, which has a table "Toollife" and the fields are ToolID, Lifeavg, etc

DB3 is Schedule.accdb which has table "schedule" and the fields are like Partno, ToolID, Quantity, etc

DB4 is Toolstatus.accd b which has a table "Toolinvent ory"

My problems are
1. From DB4, how to connect and open the DB1, DB2, DB3 database, its table, its records, and its form. through VBA
2. DB4 has a form "frmStatus" . How to see the DB1 table data in this form through VBA

I am learning Access and VBA. Kindly help.

[Seth Schrock]

You can create a link to the tables in DB1, 2 and 3 in DB4. You can then use VBA just as if they were local tables. What I would do is to copy the forms to DB4 and then you can use those locally as well.

[Pachamuthu]

Thank you Sir for the reply. We can link the tables and use them locally. I like to know how to do this through VBA. Regards, Pachamuthu

[Seth Schrock]

If you are wanting to do the linking in VBA, then see this link. The second post tells how to link to another Access database. What I would do would be to use a Debug.Print statement to get what the connection string should be when you manually link to the database and then you can build the string in VBA to do it in an automated fashion.

[Pachamuthu]

Dear Sir,
Thanks for the guidance and immediate response. I am a mechanical engg person and trying to learn Access and VBA. I have seen the link mentioned above. I have to understand the codes mentioned. I have to understand about handling external databases as my data need that. Can you please share an example project for understanding. Regards, Pachamuthu

[PhilOfWalton]

Why are your tables in different databases?

Phil

[zmbd]

+ I am with Phil here: the only reasons, given the names of the databases, I can see to be doing this in multiple database "backends" would be to get around the 2GB limit or as a method to limit access to certain data for each user and this is a messy approach at best.

+ Seth is very much correct in that the easiest method is to just link the tables in a front end and move the desired form, as well as any static tables and queries, from DB4 to the front-end.

+ The next method, and one I do for temporary table backends is to either create a form or standard module with public variables declared for each of the desired databases. For the form I have these databases opened in the on_load event for the form. I also have code in the on_close event of the form that releases all of the public variables - just incase the user manages to close the database without using one of my exit buttons.
Public BackEnd1 As DAO.Database

This form is opened in "hidden" view and left open while the session is open the databases thus defined are referred to in other code in various methods, one method would be:

Sub poc()
    Dim zglobal As DAO.Database
    Set zglobal = Forms!f_dataentry.backend1
    Debug.Print zglobal.Name
    Set zglobal = Nothing
End Sub

You can also set these as global variables in a standard module; however, I prefer the form method as it maintains a link to the backends and there is a built in method to close the backends on exiting the application in the on_close event of the form.

+ Another method I have used for infrequent connections to a backend is to add the database to the workspace. I will do this for certain transaction based updates to data that requires data integrity between the backends that I do not have a link to the tables. Because you cannot easily establish the relationships between tables in different databases without linking them within the frontend this method is not advisable for the novice and can lead to data orphans - you are warned!

For temporary table files, if I do not use the hidden form, I will add the tables as linked tables and then using vba set the link location to a none existent database updating them as needed for the new temp-table file. So long as the table isn't opened Access doesn't mind. HOWEVER, once again, if the related fields are for active tables then Access will complain unless the proper paths are set. Again, this method is not advisable for the not proficient and can lead to data orphans - you are warned!

+ Perhaps if you will tell us what it is you are doing we can provide better direction.

[Pachamuthu]

Thanks for the guidance. I will write the code and post.