Finding the location of a database

S

suepowell

Registered User.
Local time
Today, 20:50
Joined
Mar 25, 2003
Messages
282
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
 
hi,

would this be the right track in a situation where i want to export data to an external file which would be saved on-the-fly to a sub-folder of exactly where the BE is stored - whether it's on a server or not?

i.e., if my developmental BE is on my local machine, but the deployed BE is on the server... i don't want to have to change the filepath in the FE button press event on the form everytime i upload a change - i want to gain the linked BE location programmatically from the FE?

the link you provided seems to end at the mvp.org site showing code to enumerate all the local and networked drives... how will this help in determining where the BE is stored in relation to the FE? (which is what i THINK i need?) thanks.
 
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.
Click to expand...

You are heading down a rocky slope. letting all users access a shared front end on a server is defeating the object of fe be methodology.

Each user should have a copy of the front end on their local machine which inturn is lined to the shared backend. Corruption is going to occur at some point in time, and don't forget if you want to deploy a new version to the shared location you would need to have everyone logged out so that you would not encounter a share violation when attempting the copy and paste.

Another big disadvantage is that the network resources will be stretched as not only does Access have to bring the data accross the network it also has the front end application to bring accross with it.

David
 
Search on my name I have posted (cba to search myself) code to do just what you want...
 
wiklendt said:
i.e., if my developmental BE is on my local machine, but the deployed BE is on the server... i don't want to have to change the filepath in the FE button press event on the form everytime i upload a change - i want to gain the linked BE location programmatically from the FE?
Click to expand...

to clarify: i often update database stuff on my personal laptop off-site, and do not have access to the work server.
 
Last edited:
The myFolder is a function that uses the "CurrentProject.Path" equivalent
Code: 
Function myFolder()
    myFolder = Left([B][U]CurrentDb.Name[/U][/B], InStrRev(CurrentDb.Name, "\"))
End Function

The difference is I need a Backend name (Your_BE.MDB) as you will not be linking to this same database.

Also I choose to stick the backend in a subfolder of my "current project" to 'lighten' the burdon on my users to "pick" the right database should they browse to the folder.

Currentproject or CurrentDB will return the path for the currentdb, not your "current be" which may be located totaly somewhere else.
 
namliam said:
The myFolder is a function that uses the "CurrentProject.Path" equivalent
Code: 
Function myFolder()
    myFolder = Left([B][U]CurrentDb.Name[/U][/B], InStrRev(CurrentDb.Name, "\"))
End Function
The difference is I need a Backend name (Your_BE.MDB) as you will not be linking to this same database.

Also I choose to stick the backend in a subfolder of my "current project" to 'lighten' the burdon on my users to "pick" the right database should they browse to the folder.

Currentproject or CurrentDB will return the path for the currentdb, not your "current be" which may be located totaly somewhere else.
Click to expand...

ooooooohhhhh.... i get it. clever :) thanks for elaborating. i've been brain farting all day today!
 
ah, but that still doesn't solve my problem - if everyone has a copy of the front end on their machine, and the back end is somewhere on the server, then the backend is NOT simply in a subfolder of the front (CurrentProject) end....? or could this be solved with creating a clone of the front end from/on the server?
 
Well alternatively ( I have done this in the past ) I have a connection build to a local database (subfolder or same local drive folder) for my development environment.
Then if the local db isnt there, then connect to a Network drive (probably stored in a local database table someplace)...

Another alternative could be to list possible locations in a table...
ID / Location
1 X:\SomeDept\SomeFolder\BE.MDB
2 F:\SomeOtherFolder\BE.MDB
3 C:\Temp\BE.MDB
4 E:\... \ BE.MDB (some USB drive? or somethign)

Then ultimately check the myFolder for said backend... You can use DIR to try and check if the BE exists on said location(s), if exist use my code to remap all the linked tables.
 
namliam said:
Well alternatively ( I have done this in the past ) I have a connection build to a local database (subfolder or same local drive folder) for my development environment.
Then if the local db isnt there, then connect to a Network drive (probably stored in a local database table someplace)...

Another alternative could be to list possible locations in a table...
ID / Location
1 X:\SomeDept\SomeFolder\BE.MDB
2 F:\SomeOtherFolder\BE.MDB
3 C:\Temp\BE.MDB
4 E:\... \ BE.MDB (some USB drive? or somethign)

Then ultimately check the myFolder for said backend... You can use DIR to try and check if the BE exists on said location(s), if exist use my code to remap all the linked tables.
Click to expand...

hm. yes, that sounds pretty cool. will have to work on it :) thanks!
 
the location of a linked backend can be found by taking any linked table

tabledefs("mylinkedtable").connect

the string will (for a jet database) will look like

;database=path

is that what you mean?
 
gemma-the-husky said:
the location of a linked backend can be found by taking any linked table

tabledefs("mylinkedtable").connect

the string will (for a jet database) will look like

;database=path

is that what you mean?
Click to expand...

that could be the one - i'll play with that and see how it goes. what i mean (to clarify) is to discover via VBA where the linked table(s) are without already knowing where they should be or having to hardcode/change them.

i've got a few options and decisions to make regarding what i do with my FE/BE, but i'll certainly give everything a go :)
 
wiklendt said:
that could be the one - i'll play with that and see how it goes. what i mean (to clarify) is to discover via VBA where the linked table(s) are without already knowing where they should be or having to hardcode/change them.

i've got a few options and decisions to make regarding what i do with my FE/BE, but i'll certainly give everything a go :)
Click to expand...

I use this code in a standard module:

Code: 
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
 
note that after A97, you can hover the cursor over any linked table, and the location will show in a popup box

in A97, you had to use code to get at it. As explained, the location for any connected database is in the tabledefs "connect" property - it just looks different for jet/odbc/foxpro tables etc etc.
 
gemma-the-husky said:
note that after A97, you can hover the cursor over any linked table, and the location will show in a popup box

in A97, you had to use code to get at it. As explained, the location for any connected database is in the tabledefs "connect" property - it just looks different for jet/odbc/foxpro tables etc etc.
Click to expand...

that is both interesting and useful, however, the purpose of my probing about the code to find it is so that when i use the commands TransferText or TransferSpreadsheet, i can save them in the server folder of the backend so that everyone with access to the database can then also access these files RATHER than saving the exported data to the user's local computer, where only they can access it.

for me to save the file in the same folder as the backend (or a subfolder of the backend) i need to be able to tell access where that is HOWEVER the clincher here was that i develop the DB on my local drive, and deploy it on a shared network drive, so i didn't want the code to error because it couldn't find the right folder path - so i needed the folder path to the backend to be dynamic - otherwise i'd have to keep changing the code each time i deployed and risking errors by forgetting to do it.

...but hovering over a linked table in the db would have it's own benefits and uses, so thanks for sharing that.
 
OK - this function will give you the backend path from any linked Jet table. (it will also give you the database filename, although it isnt returned by the function - i actually use this as a sub with 2 byref arguments to get the path and the file)

HOWEVER, this only works correctly if you already HAVE a properly connected table, and I assume you have solved the issue of reconnecting databases


Code: 
function backendpath as string

'for Jet tables

dim connectstrg as string
Dim pos As Long

'use a known connected table
connectstrg = tabledefs("alinkedtable").connect

'get rid of ;database=

connectstrg = mid(connectstrg,11)

'this was written in A97 (no instrrev)
'so find the last \ character

    pos = 1
    While InStr(pos, infile, "\") > 1
        pos = InStr(pos, infile, "\") + 1
    Wend

    backendpath = Left(infile, pos - 1)
    'backendfile = Mid(infile, pos)

End function
 
gemma-the-husky said:
OK - this function will give you the backend path from any linked Jet table.
Click to expand...

namliam's table connection code works fabulously, but this backend path code seems to result in a null string.

i have put the code into a module, and had to prevent error to it by changing this line

Code: 
connectstrg = tabledefs("alinkedtable").connect
to

Code: 
connectstrg = DBEngine.Workspaces(0).Databases(0).tabledefs("alinkedtable").connect
BUT now instead of erroring, it just doesn't pick up the folder path.

i am trying to envoke the code on a button click:

Code: 
    strExportFolder = "\ExportResults\"
    strExportPath = GetBackendPath() & strExportFolder
but then i Debug.Print strExportPath returns only the strExportFolder portion of the string.

this is apparent in my button error handler, where "full path expected" returns only the "\ExportResults\" bit...

attachment.php


the full code on the button click, in case it helps, is:

Code: 
Private Sub cmdExportStockList_Click()
On Error GoTo Err_cmdExportStockList_Click

    Dim strDate, strFileName, strExportFile, strExportPath, strExportFolder, strSpec, strDoc As String
    Dim strMsgTitle, strMsgInfo, strMsgError As String
    
    strDoc = "qryExportStocktake"
    strDate = Format(Date, "YYYYMMMDD")
    strDate = Format(strDate, ">")
    strFileName = "Stocktake template " & strDate & ".xls"
    strSpec = "eqryExportStocktake"
    
    strExportFolder = "\ExportResults\"
    ' --------------------------------------------------------------
    strExportPath = GetBackendPath() & strExportFolder
    strExportFile = GetBackendPath() & strExportFolder & strFileName
    ' --------------------------------------------------------------
    'strExportPath = CurrentProject.Path & strExportFolder
    'strExportFile = CurrentProject.Path & strExportFolder & strFileName
    
    strMsgTitle = "RLS Ordering Records"
    strMsgInfo = vbInformation + vbOKOnly
    strMsgError = vbCritical + vbOKOnly
    
    Debug.Print strExportPath
    
    'exports to Excel 2000 format
    DoCmd.TransferSpreadsheet acExport, , strDoc, strExportFile, True
    'DoCmd.TransferText acExportDelim, strSpec, strDoc, strExportFile, True
    
    MsgBox strFileName & " saved. " & Chr(13) & Chr(13) & "This file overwrites any previous export made today." & Chr(13) & Chr(13) & "Path to file: " & Chr(13) & strExportFile, strMsgInfo, strMsgTitle
    
    'Shell "notepad.exe " & strExportFile, vbNormalFocus
    
Exit_cmdExportStockList_Click:
    Exit Sub

Err_cmdExportStockList_Click:
     
    Select Case Err.Number
      Case 3044
        'destination folder does not exist, replace default warning with this:
        MsgBox "Could not write file." & Chr(13) & "Please ensure the subfolder " & strExportFolder & " exists in the database directory, then try again." & Chr(13) & Chr(13) & "Full file path expected:" & Chr(13) & strExportPath, strMsgError, strMsgTitle
      Case Else
        Msg = "Error # " & Str(Err.Number) & Chr(13) & Err.Description
        MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
    End Select
   
    Resume Exit_cmdExportStockList_Click

End Sub
and the full code of the GetBackendPath Function is:

Code: 
Function GetBackendPath() As String

'for Jet tables
Dim connectstrg As String
Dim pos As Long

'use a known connected table
    connectstrg = DBEngine.Workspaces(0).Databases(0).TableDefs("tblOrders").Connect

'get rid of ;database=
    connectstrg = Mid(connectstrg, 11)

'this was written in A97 (no instrrev)
'so find the last \ character
    pos = 1
    While InStr(pos, infile, "\") > 1
        pos = InStr(pos, infile, "\") + 1
    Wend
    backendpath = Left(infile, pos - 1)
    'backendfile = Mid(infile, pos)

End Function

any leads? thanks.
 

Attachments

  • GetBackendPathError.jpg
    GetBackendPathError.jpg
    16.6 KB · Views: 997
You must log in or register to reply here.

Similar threads

MajP
Access Item Genie demo for Organizing / Tracking your Items
2 3 4
Replies
74
Views
3,009
ClaraBarton
C
M
Solved Avoiding opening the database from OneDrive
2
Replies
23
Views
967
GPGeorge
GPGeorge
S
Solved Folder not open
Replies
3
Views
224
sergio vieira
S
H
Solved Handling of address property in hyperlink fields
Replies
11
Views
839
Harry Paraskeva
H
jwcolby54
Database Properties - Did you know
2 3
Replies
43
Views
1,139
xavier.batlle
X

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom