How to retrive data from an external database?

[Alp]

Hi Experts,

Here again as usual. I'd like to get opinions and/or pinters to achive
the following:

I would like to query an mdb's/mde's MSysObjects from another database. Sole
purpose of this is to get the object names, creation & update dates and what
they are (types).

I plan to use a utility db which I put together in order to perform several
things such as read/write/update/delete db properties, etc. File selection
will be via GetOpenFileName. What would be a (better) way to do the above?

Thanks in advance,

Alp

 

[Tim Ferguson]

I would like to query an mdb's/mde's MSysObjects from another
database. Sole purpose of this is to get the object names, creation &
update dates and what they are (types).

set db = ws.OpenDatabase(strPath, true, true)
Set rs = db.OpenRecordset( _
"SELECT Something, SomethingElse " & vbcrlf & _
"FROM MSysObject" & vbCrLf & _
"WHERE ObjectType = 409" & vbCrLf & _
"ORDER BY SomethingElse", _
dbOpenSnapshot, dbForwardOnly

With rs
.DoSomethingUseful

End With


I strongly suspect you will run into permission problems, but you can
always have a go. There probably is something in the square-bracket
syntax you could use:

SELECT Something, SomethingElse
FROM MySysObjects IN [Database=f:\someonesdata\someonesdb.mdb]
WHERE etc, etc

but I don't use that very often. Bear in mind that the structure of the
system tables is not documented and not guaranteed to remain the same in
subsequent versions of Access.

HTH


Tim F

 

[Alp]

Hi Tim,

Thanks for you reply. Most probably its me (or the ref's) but I can't seem
to get anywhere. I would like to actually see the result of this query. I
did try quite a few stuff but still couldn't manage to get it displayed.
Once I am done with that, then I'll try and input that to a table.
Any further suggestions?

Alp
P.S.: If run within the db in question this does what I need (in basics that
is):
SELECT MSysObjects.Name, MSysObjects.DateCreate, MSysObjects.DateUpdate,
MSysObjects.Type, MSysObjects.Flags
FROM MSysObjects;

I would like to query an mdb's/mde's MSysObjects from another
database. Sole purpose of this is to get the object names, creation &
update dates and what they are (types).

set db = ws.OpenDatabase(strPath, true, true)
Set rs = db.OpenRecordset( _
"SELECT Something, SomethingElse " & vbcrlf & _
"FROM MSysObject" & vbCrLf & _
"WHERE ObjectType = 409" & vbCrLf & _
"ORDER BY SomethingElse", _
dbOpenSnapshot, dbForwardOnly

With rs
.DoSomethingUseful

End With


I strongly suspect you will run into permission problems, but you can
always have a go. There probably is something in the square-bracket
syntax you could use:

SELECT Something, SomethingElse
FROM MySysObjects IN [Database=f:\someonesdata\someonesdb.mdb]
WHERE etc, etc

but I don't use that very often. Bear in mind that the structure of the
system tables is not documented and not guaranteed to remain the same in
subsequent versions of Access.

HTH


Tim F

 

[Alp]

Hi Tim,

A further development; I did a TransferDatabase to get that table into the
db with a name using a property of the ext db. Now I do have another
struggle:
I need to reach the db.containers for documentation as txt files. I have the
necessary code that works within the db but I need to get it to work from an
external db.

Should I post this as a new thread?

Thanks,

Alp

I would like to query an mdb's/mde's MSysObjects from another
database. Sole purpose of this is to get the object names, creation &
update dates and what they are (types).

set db = ws.OpenDatabase(strPath, true, true)
Set rs = db.OpenRecordset( _
"SELECT Something, SomethingElse " & vbcrlf & _
"FROM MSysObject" & vbCrLf & _
"WHERE ObjectType = 409" & vbCrLf & _
"ORDER BY SomethingElse", _
dbOpenSnapshot, dbForwardOnly

With rs
.DoSomethingUseful

End With


I strongly suspect you will run into permission problems, but you can
always have a go. There probably is something in the square-bracket
syntax you could use:

SELECT Something, SomethingElse
FROM MySysObjects IN [Database=f:\someonesdata\someonesdb.mdb]
WHERE etc, etc

but I don't use that very often. Bear in mind that the structure of the
system tables is not documented and not guaranteed to remain the same in
subsequent versions of Access.

HTH


Tim F

 

[Tim Ferguson]

I need to reach the db.containers for documentation as txt files. I
have the necessary code that works within the db but I need to get it
to work from an external db.

I'm not sure what the problem is. If you have a reference set to DAO 3.6,
and you create a dao.database variable and Set it by OpenDatabase, then you
can do anything with it that you could do with one set to CurrentDB.

I don't really understand the question, so it might help to restart with an
explanation of what doesn't work, any error messages, and the relevant bit
of code that you have tried.

B Wishes


Tim F

 

[Tim Ferguson]

. I would like to actually see the result of this query. I
did try quite a few stuff but still couldn't manage to get it displayed.

Use a form in continuous-forms mode or a report -- those are the mechanisms
provided for displaying a recordset. In that case you will probably need to
use the IN [] syntax, or else create a linked tabledef and point it at the
required tables.

Seems like a lot of work just to repeat what desktop Access does anyway...

HTH


Tim F

 

[Alp]

Hi Tim and sorry for a delayed response. My intention was to be able to
document the collection members (forms, modules, etc) as text files
utilizing Application.SaveAsText which I am finding may not be possible.
I am able to retrive the document properties but not their contents as text.
I was/am trying to make use of Arvin's DocDatabase function which works if
you use it within the database itself.
Any possibilities?

Alp

 

[Tim Ferguson]

document the collection members (forms, modules, etc) as text files
utilizing Application.SaveAsText which I am finding may not be
possible. I am able to retrive the document properties but not their
contents as text. I was/am trying to make use of Arvin's DocDatabase
function which works if you use it within the database itself.

I don't recognise Arvin's DocDatabase. If I really wanted to document a
database, I'd use the built-in Database Documenter; if I really, really had
to program one from scratch, I'd use the DAO objects and probably format
the output using Print #. But only if I really, really, really had to.
Problem with re-inventing wheels is that they rarely come out round...

All the best


Tim F

 

[Alp]

Looks like mine is elyptical...

Alp

I don't recognise Arvin's DocDatabase. If I really wanted to document a
database, I'd use the built-in Database Documenter; if I really, really had
to program one from scratch, I'd use the DAO objects and probably format
the output using Print #. But only if I really, really, really had to.
Problem with re-inventing wheels is that they rarely come out round...

All the best


Tim F