Results 1 to 8 of 8

Thread: change path

  1. #1
    Join Date
    Mar 2003
    Posts
    225

    Unanswered: change path

    The database i currently use is on a network server on the following path:

    \\Tech02\OeeData\oeedata.mdb

    and in one of the forms the following reference is used:

    Set dbs = DBEngine.Workspaces(0).OpenDatabase("\\Tech02\OeeD ata\oeedata.mdb")

    i have a front end which is linked to the backend

    Frontend is called OEE
    Backend is called oeedata

    both of which are in the same folder.

    my question is i want to copy this database(completly) to my laptop and use it when not connected to the network, however it keeps looking to the above path, is there a way to change the above path so that it looks to the local pc for the file??

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Since no one else is chewing on this... sure.... If your database (on laptop) is located in C:\mydatabaseFolder\ then simply change:

    Set dbs = DBEngine.Workspaces(0).OpenDatabase("\\Tech02\OeeD ata\oeedata.mdb")

    To

    Set dbs = DBEngine.Workspaces(0).OpenDatabase("C:\mydatabaseFolder\oeedata.mdb")

    Or you could add the following function to a database code module and declare a public string variable to hold the location of your database:
    Code:
    'In the declarations section...
    Public DatabasePath As String
    
    Public Function GetDB_Path() As String
       Dim DB As Database
       Dim i As Integer, a$
       Set DB = CurrentDb()
       a$ = Trim(DB.Name)
       For i = Len(a$) To 1 Step -1
           If Mid$(a$, i, 1) = "\" Then Exit For
       Next i
       GetDB_Path = Left$(a$, i)
    End Function
    Then in the On Open event of your startup form enter:

    DatabasePath = GetDB_Path

    Then, where ever you have something like...:

    Set dbs = DBEngine.Workspaces(0).OpenDatabase("\\Tech02\OeeD ata\oeedata.mdb")

    you can put...:

    Set dbs = DBEngine.Workspaces(0).OpenDatabase(DatabasePath & "oeedata.mdb")


  3. #3
    Join Date
    Mar 2003
    Posts
    225
    ok thanks for that.....i am a little lost as how i should go about doing what you suggested though.....


    Or you could add the following function to a database code module and declare a public string variable to hold the location of your database:
    Code:
    'In the declarations section...
    Public DatabasePath As String

    Public Function GetDB_Path() As String
    Dim DB As Database
    Dim i As Integer, a$
    Set DB = CurrentDb()
    a$ = Trim(DB.Name)
    For i = Len(a$) To 1 Step -1
    If Mid$(a$, i, 1) = "\" Then Exit For
    Next i
    GetDB_Path = Left$(a$, i)
    End Function
    does this mean i copy the above code into a blank module and save it as "DatabasePath" ??

    someone else suggested that i store the Path in a table...would this be easier??

    can anyone please help me with :

    1) how i should set up the Table
    2) how i would then reference this table instead of using the
    Set dbs = DBEngine.Workspaces(0).OpenDatabase("\\Tech02\OeeD ata\oeedata.mdb")

    i am assuming that if i need to change the path in future if i use the table method, i would only need to change the 1 item in the Table....


    at the end of the day ideally i want to be able to have the option move the database freely and for it to run from whereever
    Help!!!!

    Andy

  4. #4
    Join Date
    Mar 2003
    Posts
    225
    Anyone Please!! i really do need some quick help here please

    (sorry don't mean to be Pushy)

    Cheers

    Andy

  5. #5
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    if you are going to change the path often, then it is probably easier to use a table

    Code:
    dim rsName as dao.recordset
    dim strPath as string
    
       set rsName=CurrentDB.OpenRecordset("Table_Name")
       strpath=rsName![Path]
       rsName.close
    
    Set dbs = DBEngine.Workspaces(0).OpenDatabase(strPath)

  6. #6
    Join Date
    Mar 2003
    Posts
    225
    so am i right in thinking i would put this in each form that has the

    Set dbs = DBEngine.Workspaces(0).OpenDatabase("\\Tech02\OeeD ata\oeedata.mdb")

    and also my table setup....should it be as follows..

    name ----Txt field
    Path -----txt field


    Andy

  7. #7
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    what you could do, is declare a public function. and then include:
    Code:
    Public Function GetPath() as String
    dim rsName as dao.recordset
    dim strPath as string
    
       set rsName=CurrentDB.OpenRecordset("Table_Name")
       strpath=rsName![Path]
       rsName.close
    End Function
    Then in your forms,etc:
    Code:
    strPath=getpath
    Set dbs = DBEngine.Workspaces(0).OpenDatabase(strPath)
    Your table :
    Path ---Text
    This is all you need, as there is only one path.

  8. #8
    Join Date
    Mar 2003
    Posts
    225
    thanks, i will give it a go.....

    just one last question as my code aint to clever...

    do i need to put a public function in every form that i use, that refers to the path ?? or in just one form

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •