A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How to retrive data from an external database?



 
 
Thread Tools Display Modes
  #1  
Old September 21st, 2004, 01:18 PM
Alp
external usenet poster
 
Posts: n/a
Default How to retrive data from an external database?

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


Ads
  #2  
Old September 21st, 2004, 05:19 PM
Tim Ferguson
external usenet poster
 
Posts: n/a
Default

"Alp" wrote in :

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

  #3  
Old September 22nd, 2004, 09:21 AM
Alp
external usenet poster
 
Posts: n/a
Default

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;

"Tim Ferguson" wrote in message
...
"Alp" wrote in :

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



  #4  
Old September 22nd, 2004, 10:24 AM
Alp
external usenet poster
 
Posts: n/a
Default

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


"Tim Ferguson" wrote in message
...
"Alp" wrote in :

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



  #5  
Old September 23rd, 2004, 10:07 AM
Tim Ferguson
external usenet poster
 
Posts: n/a
Default

"Alp" wrote in :

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

  #6  
Old September 23rd, 2004, 10:10 AM
Tim Ferguson
external usenet poster
 
Posts: n/a
Default

"Alp" wrote in :

. 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

  #7  
Old September 24th, 2004, 08:39 AM
Alp
external usenet poster
 
Posts: n/a
Default

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" wrote in message
...
"Alp" wrote in :

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



  #8  
Old September 24th, 2004, 05:50 PM
Tim Ferguson
external usenet poster
 
Posts: n/a
Default

"Alp" wrote in :

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


  #9  
Old September 24th, 2004, 06:23 PM
Alp
external usenet poster
 
Posts: n/a
Default

Looks like mine is elyptical... :-)

Alp

"Tim Ferguson" wrote in message
...
"Alp" wrote in :

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




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
What is MDE Charlie General Discussion 4 August 24th, 2004 04:15 PM
Excel - Import External Data from Access VLeonard Setting up and Configuration 1 November 7th, 2003 06:59 PM


All times are GMT +1. The time now is 02:47 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 OfficeFrustration.
The comments are property of their posters.