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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
"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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
"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
|
|||
|
|||
"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
|
|||
|
|||
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
|
|||
|
|||
"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
|
|||
|
|||
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 | |
|
|
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 05:59 PM |