Back End Database Path

[DubboPete]

In simple terms...

I've done the newsgroup thing, even visited
mvps.org\backslash\backslash\0007.html amd seen Dev's example. But yet I
cannot get it to work. Let's see if lateral thinking newsgroup can figure
out how to pull this back-end location?

In most cases, the back end resides at:

g:\squirt\dtsquirt.mdb

In some instances, the back-end resides at:

c:\program files\squirt database\dtsquirt.mdb

All I want is a field on my form [switchboard] (let's call it [text2]) to
display the back-end database location:

g:\squirt\dtsquirt.mdb

or

c:\program files\squirt database\dtsquirt.mdb

How hard is that? Very hard, apparently, cos I can't find the quick fix
anywhere lol

SuperUsers... step up please?

DubboPete
the 17th century Goat Herder

 

[Allen Browne]

Set the ControlSource of the text box on your form to:
=Mid([dbEngine].[Workspaces](0).[Databases](0).[TableDefs]("Table1").[Connect],11)

Substitute the name of an attached table name for Table1.

 

[Klatuu]

That is fine if you assume all linked tables are in the same mdb; otherwise,
you would need to loop through all the tabledefs to see if there is any
veriation in the connect string.

Set the ControlSource of the text box on your form to:
=Mid([dbEngine].[Workspaces](0).[Databases](0).[TableDefs]("Table1").[Connect],11)

Substitute the name of an attached table name for Table1.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

In simple terms...

I've done the newsgroup thing, even visited
mvps.org\backslash\backslash\0007.html amd seen Dev's example. But yet I
cannot get it to work. Let's see if lateral thinking newsgroup can
figure out how to pull this back-end location?

In most cases, the back end resides at:

g:\squirt\dtsquirt.mdb

In some instances, the back-end resides at:

c:\program files\squirt database\dtsquirt.mdb

All I want is a field on my form [switchboard] (let's call it [text2]) to
display the back-end database location:

g:\squirt\dtsquirt.mdb

or

c:\program files\squirt database\dtsquirt.mdb

How hard is that? Very hard, apparently, cos I can't find the quick fix
anywhere lol

SuperUsers... step up please?

DubboPete
the 17th century Goat Herder

 

[John Nurick]

AFAIK this works too (e.g. as the RowSource of a listbox):

SELECT DISTINCT Database FROM MSysObjects
WHERE (Type = 6) AND (Connect IS NULL)
ORDER BY Database;

Omit the "Connect" condition to get locations of linked tables that
aren't in mdb files.

That is fine if you assume all linked tables are in the same mdb; otherwise,
you would need to loop through all the tabledefs to see if there is any
veriation in the connect string.

Set the ControlSource of the text box on your form to:
=Mid([dbEngine].[Workspaces](0).[Databases](0).[TableDefs]("Table1").[Connect],11)

Substitute the name of an attached table name for Table1.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

In simple terms...

I've done the newsgroup thing, even visited
mvps.org\backslash\backslash\0007.html amd seen Dev's example. But yet I
cannot get it to work. Let's see if lateral thinking newsgroup can
figure out how to pull this back-end location?

In most cases, the back end resides at:

g:\squirt\dtsquirt.mdb

In some instances, the back-end resides at:

c:\program files\squirt database\dtsquirt.mdb

All I want is a field on my form [switchboard] (let's call it [text2]) to
display the back-end database location:

g:\squirt\dtsquirt.mdb

or

c:\program files\squirt database\dtsquirt.mdb

How hard is that? Very hard, apparently, cos I can't find the quick fix
anywhere lol

SuperUsers... step up please?

DubboPete
the 17th century Goat Herder

 

[Allen Browne]

John, that's great!

It never occurred to me to use:
=DLookup("Database", "MSysObjects", "Name='Table1'")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

AFAIK this works too (e.g. as the RowSource of a listbox):

SELECT DISTINCT Database FROM MSysObjects
WHERE (Type = 6) AND (Connect IS NULL)
ORDER BY Database;

Omit the "Connect" condition to get locations of linked tables that
aren't in mdb files.

That is fine if you assume all linked tables are in the same mdb;
otherwise,
you would need to loop through all the tabledefs to see if there is any
veriation in the connect string.

Set the ControlSource of the text box on your form to:
=Mid([dbEngine].[Workspaces](0).[Databases](0).[TableDefs]("Table1").[Connect],11)

Substitute the name of an attached table name for Table1.

In simple terms...

I've done the newsgroup thing, even visited
mvps.org\backslash\backslash\0007.html amd seen Dev's example. But
yet I
cannot get it to work. Let's see if lateral thinking newsgroup can
figure out how to pull this back-end location?

In most cases, the back end resides at:

g:\squirt\dtsquirt.mdb

In some instances, the back-end resides at:

c:\program files\squirt database\dtsquirt.mdb

All I want is a field on my form [switchboard] (let's call it [text2])
to
display the back-end database location:

g:\squirt\dtsquirt.mdb

or

c:\program files\squirt database\dtsquirt.mdb

How hard is that? Very hard, apparently, cos I can't find the quick
fix
anywhere lol

 

[DubboPete]

Hi Klatuu and Allen

Katuu, all the linked tables are in one back-end database, the two locations
I quoted were for the 'corporate' back end and the 'remote' back-end -
remote users have the back end on their pc... make sense?

Allen,
The control source you quoted works brilliantly, however I am going to look
at that DLookup bit too!!

thanks one and all

DubboPete

That is fine if you assume all linked tables are in the same mdb;
otherwise,
you would need to loop through all the tabledefs to see if there is any
veriation in the connect string.

Set the ControlSource of the text box on your form to:
=Mid([dbEngine].[Workspaces](0).[Databases](0).[TableDefs]("Table1").[Connect],11)

Substitute the name of an attached table name for Table1.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

In simple terms...

I've done the newsgroup thing, even visited
mvps.org\backslash\backslash\0007.html amd seen Dev's example. But
yet I
cannot get it to work. Let's see if lateral thinking newsgroup can
figure out how to pull this back-end location?

In most cases, the back end resides at:

g:\squirt\dtsquirt.mdb

In some instances, the back-end resides at:

c:\program files\squirt database\dtsquirt.mdb

All I want is a field on my form [switchboard] (let's call it [text2])
to
display the back-end database location:

g:\squirt\dtsquirt.mdb

or

c:\program files\squirt database\dtsquirt.mdb

How hard is that? Very hard, apparently, cos I can't find the quick
fix
anywhere lol

SuperUsers... step up please?

DubboPete
the 17th century Goat Herder