Connecting Databases (1 Viewer)

[LadyDi]

I have created several databases for use in my department. I was wondering if there is any way to link all the databases together without recreating the wheel. The only thought I could come up with was to create one more database, but this database would link to all other databases. In other words,I would like the associates to be able to open one database and access all other databases from there. Do you have any suggestions as to how to make this happen?

I can find where to link tables in different databases, but not where to link forms or queries. A lot of work as been put into these forms, and I don't want to start from scratch.

Any advice you can provide would be greatly appreciated.

 

[Trevor G]

Are you trying to switch from one database to another through a form control? If so you would add some code to do this

Sub openDataBAses()
Dim accapp As Access.Application
Set accapp = New Access.Application
accapp.OpenCurrentDatabase ("c:\which location\name of database.mdb")
accapp.Visible = True
End Sub

You could also look at using a hyperlink and a shell command

 

[LadyDi]

Yes, I do just want on database that can be used to toggle between various databases. The code you provided worked perfectly. However, now I have a couple more questions.

1. Is there a way to make the database open in full screen mode?
2. On many of the databases we use, there is a sign in screen. Is there a way for the name on the sign in forms to be populated from a name field in the new database?

Thank you very much for your assistance.

 

[Trevor G]

Add this to the code to maximise the window

accapp.Application.RunCommand acCmdAppMaximize

How is your sign in screen being populate are you using

Envrio("UserName")

 

[LadyDi]

Actually, the user selects their name from a drop down list on the sign in screen.

I've never heard of the Enviro("UserName") - what does that do? Would it be better than having the user select his or her name from a drop down?

 

[Trevor G]

The Envrio("UserName") will get the computer name that the user uses to log in, it saves having to ask them to use passwords as you can build the relevant table to check who is opening the database and if they have permission they can enter the database without using a password.

If you already have a method of getting them to select from a drop down then use that same method as it is already there, and if you change anything in the new database it will effect the other databases.

 

[LadyDi]

Thank you for the information. I have two more questions.

1. Is there a way to populate the name field in the sign in form from a name field in the new database?

2. Someone just asked me if there is a way to open one database within another? Is there an ActiveX control that will open a database? What I would really like to do is click a button and have a database open on a separate tab within the new database. Is that possible?

 

[Trevor G]

Thank you for the information. I have two more questions.

1. Is there a way to populate the name field in the sign in form from a name field in the new database?

2. Someone just asked me if there is a way to open one database within another? Is there an ActiveX control that will open a database? What I would really like to do is click a button and have a database open on a separate tab within the new database. Is that possible?

I am not aware of any facility that will allow you to have one database within another.

As to the first question, you would need to be able to copy the value then once the other database is open you would need to make sure that the value is pasted, you would also have see if you need to refer to a column number.

As I have no idea what table names, field names, form names, database names etc that you are using I can't supply any sample.

If you are looking to get one database to automatically place in a value from another database does that mean you then want to by pass the password and go to another form?

 

[LadyDi]

We actually do not have a password on the front end of any of these databases. The people I work with have passwords for so many other programs, I didn't want to add another one. All they do is enter their name on a sign in form. Depending on the name that is entered, depends on the part of the database they are taken to. They put their name in the "PSSName" field and press enter. That is all there is to signing into the databases. So, copying and pasting from one field to another would work fine. How would you go about doing that?

 

[Trevor G]

The code would need to first of all go to the correct control and then copy the value

DoCmd.GoToControl "PSSName"
DoCmd.RunCommand acCmdCopy

Once you open the other database you do the same but paste in the value

DoCmd.GoToControl "PSSName"
DoCmd.RunCommand acCmdPaste

 

[boblarson]

I've never heard of the Enviro("UserName") -

That would be because Trevor has shortened it from its TRUE syntax:

Environ("username")

Need the n in there.

 

[boblarson]

A couple of things.

1. For opening up the other database and using it, Trevor's code is ALMOST there. You should also add one thing to it to release the variable so you don't end up with potential memory leaks. That is to add the part in red below:

Sub openDataBAses()
Dim accapp As Access.Application
   Set accapp = New Access.Application
     accapp.OpenCurrentDatabase ("c:\which location\name of database.mdb")
     accapp.Visible = True
     [B][COLOR=red]accapp.UserControl = True[/COLOR][/B]
[B][COLOR=#ff0000]      Set accapp = Nothing[/COLOR][/B]
End Sub

And to set your form which pops up from that instance you don't have to use the DoCmd.GoToControl, you just have to use what you have there:

Dim accapp As Access.Application
   Set accapp = New Access.Application
     accapp.OpenCurrentDatabase ("c:\which location\name of database.mdb")
     accapp.Visible = True
[B][COLOR=red]     accapp.AllForms("YourFormNameHere").Controls("YourControlNameHere").Value = Environ("username")[/COLOR][/B]
     [B][COLOR=blue]accapp.UserControl = True[/COLOR][/B]
[B][COLOR=blue]      Set accapp = Nothing[/COLOR][/B]
End Sub

 

[LadyDi]

That works perfectly. Thank you very much for your help.

Now, I don't mean to be a pain, but I have one more question.

Is there a way to get the database to press Enter once it goes to the new database?

 

[boblarson]

Is there a way to get the database to press Enter after it pastes the name?

You might have to set the command button in the other database to be PUBLIC instead of PRIVATE but you should be able to call the event:

Dim accapp As Access.Application
 
   Set accapp = New Access.Application
 
     accapp.OpenCurrentDatabase ("c:\which location\name of database.mdb")
 
     accapp.Visible = True
 
[COLOR=black]    accapp.Forms("YourFormNameHere").Controls[/COLOR][COLOR=black]("YourControlNameHere").Value = Environ("username")[/COLOR]
 
[COLOR=#ff0000][B]Call accapp.Forms("YourFormNameHere").ButtonNameHere_Click[/B][/COLOR]
 
   [COLOR=black] accapp.UserControl = True[/COLOR]
 
[COLOR=black]    Set accapp = Nothing[/COLOR]
End Sub

 

[boblarson]

Oops, got one piece wrong again. You want:

accapp.DoCmd.RunCommand acCmdAppMaximize

to maximize the app instead of what I wrote:

accapp.DoCmd.Maximize.

 

[LadyDi]

That works perfectly. Thank you very much.

 

[cclark9589]