Access 97: Backend DB Name 
Author Message
 Access 97: Backend DB Name

I know you can use the CurrentDB object to find the name of the
currently opened DB (CurrentDB.Name), but how do I find out the name of
it's Backend db?

Is there something like CurrentDB to find the backend filename & path?

Chad Waldman



Thu, 03 May 2001 03:00:00 GMT  
 Access 97: Backend DB Name
It's a common (and useful) technique to keep all the Access data in a
separate mdb file, but nothing in Access requires this.  In fact, one
Access "front end" database can attach to tables in a number of other mdb's
as well as other data sources.  To find the location of the attached table,
examine its connect property.

HTH
        - Turtle



Quote:
> I know you can use the CurrentDB object to find the name of the
> currently opened DB (CurrentDB.Name), but how do I find out the name of
> it's Backend db?

> Is there something like CurrentDB to find the backend filename & path?

> Chad Waldman




Thu, 03 May 2001 03:00:00 GMT  
 Access 97: Backend DB Name
Thank you for your help.

Chad

Quote:

>It's a common (and useful) technique to keep all the Access data in a
>separate mdb file, but nothing in Access requires this.  In fact, one
>Access "front end" database can attach to tables in a number of other
mdb's
>as well as other data sources.  To find the location of the attached
table,
>examine its connect property.

>HTH
> - Turtle



>> I know you can use the CurrentDB object to find the name of the
>> currently opened DB (CurrentDB.Name), but how do I find out the name
of
>> it's Backend db?

>> Is there something like CurrentDB to find the backend filename &
path?

>> Chad Waldman




Thu, 03 May 2001 03:00:00 GMT  
 Access 97: Backend DB Name
Just happened to make this this morning.  Hope it helps.

Public Function TableSourceDB(ByVal TblName As String) As String
   Dim DB   As Database
   Dim Tdf  As TableDef
   Dim Chr1 As Integer
   Dim Chr2 As Integer
   Dim Cnct As String

   On Error Resume Next
   TableSourceDB = ""

   Set DB = CurrentDb()
   Set Tdf = DB.TableDefs(TblName)
   If Err.Number <> 0 Then
      Exit Function  'Invalid table, most likely.
   End If

   Cnct = UCase(Tdf.Connect)
   With Tdf
      If Len(Cnct) < 1 Then
         Exit Function  'It's a local Access table.
      End If

      '-- Find the Database name.
      Chr1 = InStr(Cnct, "DATABASE=") + 9
      Chr2 = InStr(Chr1, Cnct, ";")
      If Chr2 < 1 Then
         Chr2 = Len(Cnct) + 1
      End If

      If Left$(Cnct, 7) = "PARADOX" Then
         TableSourceDB = Mid$(Tdf.Connect, Chr1, Chr2 - Chr1) & "\" &
Tdf.SourceTableName) & " (Paradox)"
      Else
         TableSourceDB = Mid$(Tdf.Connect, Chr1, Chr2 - Chr1)
      End If
   End With

End Function

Quote:

>I know you can use the CurrentDB object to find the name of the
>currently opened DB (CurrentDB.Name), but how do I find out the name of
>it's Backend db?

>Is there something like CurrentDB to find the backend filename & path?

>Chad Waldman




Fri, 04 May 2001 03:00:00 GMT  
 Access 97: Backend DB Name
Hi Chad,

Directly from Access Help file on the topic,

        Connect Property:   Sets or returns a value that provides
information about the source of an open connection, an open database, a
database used in a pass-through query, or a linked table. For Database
objects, new Connection objects, linked tables, and TableDef objects not yet
appended to a collection, this property setting is read/write. For QueryDef
objects and base tables, this property is read-only.

        As a test, run the sListPath sub in your database.  Note that for
local tables, a blank line, and for linked tables, the path to their parent
database will be printed out in the Debug window.

        For general usage,  pass individual table names to fGetLinkPath
function.

'*************** Code Start **************
Function fGetLinkPath(strTable As String) As String
Dim dbs As Database, stPath As String

    Set dbs = CurrentDb()
    On Error Resume Next
    stPath = dbs.TableDefs(strTable).Connect
    If stPath = "" Then
        fGetLinkPath = vbNullString
        'can change this to currentdb.name
    Else
        fGetLinkPath = right(stPath, Len(stPath) _
                        - (InStr(1, stPath, "DATABASE=") + 8))
    End If
    Set dbs = Nothing
End Function

Sub sListPath()
    Dim loTd As TableDef
    CurrentDb.TableDefs.Refresh
    For Each loTd In CurrentDb.TableDefs
        Debug.Print fGetLinkPath(loTd.Name)
    Next loTd
    Set loTd = Nothing
End Sub

'*************** Code End **************

HTH
--
Dev Ashish (Just my $.001)
---------------
The Access Web ( http://home.att.net/~dashish )
---------------

Quote:

>I know you can use the CurrentDB object to find the name of the
>currently opened DB (CurrentDB.Name), but how do I find out the name of
>it's Backend db?

>Is there something like CurrentDB to find the backend filename & path?

>Chad Waldman




Fri, 04 May 2001 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Front end in Access 2000 / backend in access 97

2. Convert Access 97 db to Access 2000 db in VB6

3. Converting Access 2.0 DB to Access 97 DB in VB code

4. implementing transactions on an access 97 backend using asp

5. Access 97 secure backend password

6. Problem With VB6 App With Access 97 Backend Running On Windows 2000 Pro

7. which backend to choose Access 97 or SQL?

8. Problem With VB6 App With Access 97 Backend Running On Windows 2000 Pro

9. How to get number of users on MS Access 97 backend

10. Help, using an Access 97 DB without MS Access Application

11. Error accessing Access 97 DB

12. Help, using an Access 97 DB without MS Access Application

 

 
Powered by phpBB® Forum Software