Database informations via vba? 
Author Message
 Database informations via vba?

I intend to make a database to administrate
my other databases.

So I should know where i can get more
informations about "reading out" informations
from other databases (e.g. does it have linked
tables; where is the Icon stored and such stuff).

BTW, how do I get the database path and
the database name "separate"?
db.name gives me the name and the path
as one string.

/Raymond



Sun, 02 Nov 2003 16:04:51 GMT  
 Database informations via vba?
You can programatically OpenDatabase on a fully qualified file name
to get at the objects it contains. If its tables are linked, they
will have something in the Connect string of the TableDef.

Function ShowTables(strFullFileName As String)
    Dim db As DAO.Database
    Dim tdx As DAO.TableDefs
    Dim tdf As DAO.TableDef

    Set db = OpenDatabase(strFullFileName)
    Set tdx = db.TableDefs

    For Each tdf In tdx
        Debug.Print tdf.Name, tdf.Connect
    Next

    Set tdf = Nothing
    Set tdx = Nothing
    db.Close
    Set db = Nothing
End Function

Here's a simple way to parse the file name from the path. It relies
on the fact that the Dir() function returns just the file name part
without the path.

Function FilePath(strFullFileName As String) As String
    'Returns the path of the file, including the trailing slash.
    Dim strFile As String

    strFile = Dir(strFullFileName)
    If strFile <> vbNullString Then
        FilePath = Left(strFullFileName, _
            Len(strFullFileName) - Len(strFile))
    End If
End Function

Quote:

> I intend to make a database to administrate
> my other databases.

> So I should know where i can get more
> informations about "reading out" informations
> from other databases (e.g. does it have linked
> tables; where is the Icon stored and such stuff).

> BTW, how do I get the database path and
> the database name "separate"?
> db.name gives me the name and the path
> as one string.

> /Raymond

--
Perth, Western Australia
Tips for MS Access users at:
        http://odyssey.apana.org.au/~abrowne


Sun, 02 Nov 2003 16:26:59 GMT  
 Database informations via vba?
Thanks a lot; exactly what I was looking for. I'm still impressed
how much new thing you can learn day by day with this
newsgroup.

What I still don't see 100% is when to use "DAO" and
when not. In general I simply use Dim db as database and
I never had problems with it. But maybe I'll find something
on this in the help section.

The workaround for the path/name is also very nice. Sometimes
it's strange that such functions are not built in into Access.

/Raymond



Quote:
> You can programatically OpenDatabase on a fully qualified file name
> to get at the objects it contains. If its tables are linked, they
> will have something in the Connect string of the TableDef.

> Function ShowTables(strFullFileName As String)
>     Dim db As DAO.Database
>     Dim tdx As DAO.TableDefs
>     Dim tdf As DAO.TableDef

>     Set db = OpenDatabase(strFullFileName)
>     Set tdx = db.TableDefs

>     For Each tdf In tdx
>         Debug.Print tdf.Name, tdf.Connect
>     Next

>     Set tdf = Nothing
>     Set tdx = Nothing
>     db.Close
>     Set db = Nothing
> End Function

> Here's a simple way to parse the file name from the path. It relies
> on the fact that the Dir() function returns just the file name part
> without the path.

> Function FilePath(strFullFileName As String) As String
>     'Returns the path of the file, including the trailing slash.
>     Dim strFile As String

>     strFile = Dir(strFullFileName)
>     If strFile <> vbNullString Then
>         FilePath = Left(strFullFileName, _
>             Len(strFullFileName) - Len(strFile))
>     End If
> End Function


> > I intend to make a database to administrate
> > my other databases.

> > So I should know where i can get more
> > informations about "reading out" informations
> > from other databases (e.g. does it have linked
> > tables; where is the Icon stored and such stuff).

> > BTW, how do I get the database path and
> > the database name "separate"?
> > db.name gives me the name and the path
> > as one string.

> > /Raymond

> --
> Perth, Western Australia
> Tips for MS Access users at:
> http://odyssey.apana.org.au/~abrowne



Sun, 02 Nov 2003 16:55:28 GMT  
 Database informations via vba?
The statement:
     Dim db as Database
works fine except in Access 2000 where there is no reference
to the DAO library (the default for new A2k mdb's), or if
another library has priority over the DAO library and it also
has a Database object in it (as the ADO library does).

So your code will work fine in all older versions of Access,
and in an Access 2k mdb that does not reference ADO before
DAO. Because this problem has cropped up in A2k, I've started
disambiguating in all Access projects--regardless of version
or the libraries references--so the code cannot be broken so
easily.

Quote:

> Thanks a lot; exactly what I was looking for. I'm still impressed
> how much new thing you can learn day by day with this
> newsgroup.

> What I still don't see 100% is when to use "DAO" and
> when not. In general I simply use Dim db as database and
> I never had problems with it. But maybe I'll find something
> on this in the help section.

> The workaround for the path/name is also very nice. Sometimes
> it's strange that such functions are not built in into Access.

> /Raymond



> > You can programatically OpenDatabase on a fully qualified file name
> > to get at the objects it contains. If its tables are linked, they
> > will have something in the Connect string of the TableDef.

> > Function ShowTables(strFullFileName As String)
> >     Dim db As DAO.Database
> >     Dim tdx As DAO.TableDefs
> >     Dim tdf As DAO.TableDef

> >     Set db = OpenDatabase(strFullFileName)
> >     Set tdx = db.TableDefs

> >     For Each tdf In tdx
> >         Debug.Print tdf.Name, tdf.Connect
> >     Next

> >     Set tdf = Nothing
> >     Set tdx = Nothing
> >     db.Close
> >     Set db = Nothing
> > End Function

> > Here's a simple way to parse the file name from the path. It relies
> > on the fact that the Dir() function returns just the file name part
> > without the path.

> > Function FilePath(strFullFileName As String) As String
> >     'Returns the path of the file, including the trailing slash.
> >     Dim strFile As String

> >     strFile = Dir(strFullFileName)
> >     If strFile <> vbNullString Then
> >         FilePath = Left(strFullFileName, _
> >             Len(strFullFileName) - Len(strFile))
> >     End If
> > End Function


> > > I intend to make a database to administrate
> > > my other databases.

> > > So I should know where i can get more
> > > informations about "reading out" informations
> > > from other databases (e.g. does it have linked
> > > tables; where is the Icon stored and such stuff).

> > > BTW, how do I get the database path and
> > > the database name "separate"?
> > > db.name gives me the name and the path
> > > as one string.

--
Perth, Western Australia
Tips for MS Access users at:
        http://odyssey.apana.org.au/~abrowne


Sun, 02 Nov 2003 17:12:19 GMT  
 Database informations via vba?
On Wed, 16 May 2001 16:26:59 +0800, Allen Browne

[snip]

Quote:
>It relies
>on the fact that the Dir() function returns just the file name part
>without the path.

[snip]

And on the hope that the file exists.

--
Mike Sherrill
Information Management Systems



Sun, 02 Nov 2003 22:38:05 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Read MP3 Tag informations via VB.NET?

2. Create Database via VBA

3. Saving attachments into database via VBA Macro

4. Saving attachments into database via VBA Macro

5. Database access via activeX document to remotre database

6. Select informations from Extras/Optionen/Benutzer-Info/Addresse (engl.: user-informations?)

7. Accessing tables via VBA

8. spreadsheet export via VBA

9. Setting Excel cell formula via Access VBA

10. Need help updating a field via VBA

11. Set Field Properties via VBA ?

12. How to change column locations/widths via VBA

 

 
Powered by phpBB® Forum Software