Finding the location of a database

[namliam]

connectstrg = tabledefs("alinkedtable").connect

Is no where in my code?? I think you would have to use Currentdb.Tabledefs().connect
You cannot use tabledefs without a database pre-qualifier, which is more or less what you did with DBEngine.Workspaces(0).Databases(0).... except much more complex...

A quick test on one of my linked tables is returns the path no problem, barring the fact that it is prefixed with ";DATABASE=" which you will need to remove for the path to be usefull.
Also the path contains the BE database, which then has to be removed...

The only reason Currentdb.Tabledefs("Table").Connect would return a null, is if it IS NULL. I.e. When there is no connect for a local table...

Your lack of InstrRev can also be circumvented by using Dir... Dir(FullBePathAndFileName) will return only the filename... I.e.
strFilename = dir(connectstrg)
Path = mid(connectstrg, len(connectstrg) - len(strfilename) )

To come back to the blank, I am guessing your checking a local table for a connection. Which obviously dont exist.
You can check if the table is a linked table by using...
Currentdb.Tabledefs("Table").Attributes = dbAttachedTable

Also I would advice you (from my own experience) to keep the last \ in the full path returned by your function, but that may be personal preverence.

 

[genesis]

HI,

I want to get the location of the database I am in.

I have a front end back end setup and I want to allow the user to copy them to wherever they want, and then to link the backend using code.

I have found the property currentproject.path which almost does what I want, but this returns the value for example

c:\documents\database\

what I actually need is \\sues computer\user\sue\documents\database

or similar

This is because this is a networked database and can be used from a number of PC's, each PC has a shortcut to the same front end and of course they all use the same backend.

Is there any way to get this information. I do have some code that lets me ask the user to find the location, and as long as I give them instructions to start from the machine name this should work, but it is potentially very messy.

Thanks for your help

Sue

cant you use these code (see attached database) to locate the database?

 

[gemma-the-husky]

sorry. that was my bad aircode

connectstrg = tabledefs("alinkedtable").connect

should be this
connectstrg = currentdb.tabledefs("alinkedtable").connect

as an example, just try this in a module

Sub doit()
MsgBox CurrentDb.TableDefs("anylinkedtable").Connect
End Sub

the connect is null for an unconnected table.

 

[wiklendt]

cant you use these code (see attached database) to locate the database?

sure, if you want to annoy your user every time they want to export something (or every time they open the database!)... the user shouldn't need to know where the backend is.

this code/db would be helpful in asking the user where to save an export file, BUT i don't want to do that.

it's a good little demo, though. thanks for that.

 

[wiklendt]

sorry. that was my bad aircode

connectstrg = tabledefs("alinkedtable").connect

should be this
connectstrg = currentdb.tabledefs("alinkedtable").connect

as an example, just try this in a module

Sub doit()
MsgBox CurrentDb.TableDefs("anylinkedtable").Connect
End Sub

the connect is null for an unconnected table.

i've tried prefixing with "CurrentDb." no luck. i'll try a few more things and get back to you.

 

[wiklendt]

I use this code in a standard module:

Option Compare Database
Option Explicit


Public Function fHTC_GetBEFolder(pTableName As String) As String
'
' usage example: fHTC_GetBEFolder("Suppliers")
'
    Dim strFullPath As String
    Dim I As Long
    strFullPath = Mid(DBEngine.Workspaces(0).Databases(0).TableDefs(pTableName).Connect, 11)

    For I = Len(strFullPath) To 1 Step -1
        If Mid(strFullPath, I, 1) = "\" Then
            fHTC_GetBEFolder = Left(strFullPath, I)
            Exit For
        End If
    Next


End Function

Public Function fHTC_GetBEName(pTableName As String) As String
'
' usage example: fHTC_GetBEName("Suppliers")
'

    Dim strFullPath As String
    Dim I As Long
    strFullPath = Mid(DBEngine.Workspaces(0).Databases(0).TableDefs(pTableName).Connect, 11)

    For I = Len(strFullPath) To 1 Step -1
        If Mid(strFullPath, I, 1) = "\" Then
            fHTC_GetBEName = Mid(strFullPath, I + 1)
            Exit For
        End If
    Next


End Function

Public Function fHTC_GetBEFullPath(pTableName As String) As String
'
' usage example: fHTC_GetBEFullPath("Suppliers")
'
    
    fHTC_GetBEFullPath = Mid(DBEngine.Workspaces(0).Databases(0).TableDefs(pTableName).Connect, 11)

End Function

ok, i've now tried HTC's code - works fantastically thanks.