Opening DB from othe DB

  • Thread starter Thread starter RonS
  • Start date Start date
R

RonS

Because of the limit of the number of objects allowed for
an Access DB, I want to create a "main" db that will open
other DB's. This strategy will allow me to
compartmentalize my project.

This seems like a good approach to me, but I'm certainly
willing to explore other options.

1) How do I put a command in one database that will open
another?

2) Or, how do I design a complex project that must have
more than 255 objects?

Thanks,
Ron
 
255 is the limit on the number of fields in a table, not objects in a
database. An Access database can contain tens of thousands of objects. See
'specifications' in the help file for details.
 
Hmmmm,

I've run into this problem before and I recall that I've
been advised by MS tech support about the limit in the
number of objects being 255. I've used ACCESS for years
and I've known about the limit in the number of fields.
I've always thought it curious that the number of objects
had the same limit. Further, my understanding is that the
limit includes those phantom objects ACCESS creates in the
background (I'm referring to those objects that are
cleaned up when the database is packed).

Regardless, in 6 years of using ACCESS, I've always had
corruption problems when a database contained too many
objects. Even if I'm wrong about the limit (and I don't
think I am) I want to break up the database into
components. Perhaps someone will chime and comment.

I still want to know how to open a database from within
another database. I seem to recall hearing about doing
that somewhere in the past. Thanks.
 
Public Sub ThereIsNo255ObjectLimit()

Dim lngLoop As Long

For lngLoop = 1 To 256
CreateForm
DoCmd.Close acForm, Forms(Forms.Count - 1).Name, acSaveYes
Next lngLoop

End Sub

To open another database in the UI ...

Private Sub Command0_Click()

Dim app As Access.Application
Set app = New Access.Application
app.OpenCurrentDatabase "C:\DSAPPS\db4.mdb"
app.Visible = True

End Sub

To open a form or report in a referenced MDB add a public function to the
referenced MDB (the one that contains the form or report) that returns an
instance of the required form or report, and call this function from the
referencing MDB ...

In db2.mdb ...

Public Function GetTheForm() As Form

Set GetTheForm = New Form_ExtForm

End Function

In db1.mdb ...

Private mfrmTest As Form

Private Sub Command0_Click()

Set mfrmTest = db2.GetTheForm()
mfrmTest.Visible = True

End Sub

Private Sub Form_Close()

Set mfrmTest = Nothing

End Sub

My advice is that you are trying to solve a problem that does not exist, or
at least does not have the cause that you think it has. Here are some
figures from one of our own applications, an application that has been in
daily use by multiple clients since the mid nineties ...

? currentdata.AllTables.Count
95
? currentdata.AllQueries.Count
193
? currentproject.AllForms.Count
82
? currentproject.AllModules.Count
93
? currentproject.AllReports.Count
59
? 95+193+82+93+59
522

Access 2000 had a marked tendency to corrupt *during development* when
working with large 'front-end' application MDBs (larger than approx 8MB, in
my experience). I'm referring here to corruption of forms, reports, and
code, as opposed to corruption of data, which is a separate issue. Access 97
did not have this problem, Access 2002 did not have this problem, and so
far, Access 2003 doesn't appear to have this problem. Which is not to say
that these other versions *never* corrupt, but it is rare. And even in
Access 2000, the problem was almost completely confined to development - it
is, in my experience, extremely rare for forms, reports, or code to become
corrupt in a production setting, when they are not being modified - as long
as you split the app and share only the data, not the application MDB,
across the network.

I promise you, if Access could not cope with more than 255 database objects,
we would not be having this conversation, because I would not be an Access
developer.
 
Thanks Brendon, as expected your code works fine. I'm also convinced that I'm in error about the number of objects. We're working on it. Thanks again.
 
I have the similar problem, but I want not only to break my database project to components, but to have opportunity to add new components to project without rebuilding it.
Those methods:

----- Brendan Reynolds (MVP) wrote: -----

Public Sub ThereIsNo255ObjectLimit()

Dim lngLoop As Long

For lngLoop = 1 To 256
CreateForm
DoCmd.Close acForm, Forms(Forms.Count - 1).Name, acSaveYes
Next lngLoop

End Sub

To open another database in the UI ...

Private Sub Command0_Click()

Dim app As Access.Application
Set app = New Access.Application
app.OpenCurrentDatabase "C:\DSAPPS\db4.mdb"
app.Visible = True

End Sub

To open a form or report in a referenced MDB add a public function to the
referenced MDB (the one that contains the form or report) that returns an
instance of the required form or report, and call this function from the
referencing MDB ...

In db2.mdb ...

Public Function GetTheForm() As Form

Set GetTheForm = New Form_ExtForm

End Function

In db1.mdb ...

Private mfrmTest As Form

Private Sub Command0_Click()

Set mfrmTest = db2.GetTheForm()
mfrmTest.Visible = True

End Sub

Private Sub Form_Close()

Set mfrmTest = Nothing

End Sub

------------------------------------------------------------------------------

are good, but only when i know the name of another database.
My target is to have one "Main" database and to have components, wich i could add or remove.
I tried a lot of things, but I can't create even a single object from "component" database.
I would greatly appriciate your help.
 
Back
Top