Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    copy database (Access 2000)

    I have an option group with 16 options which are databases for different cities
    for example
    option 1 : DBBerlin
    option 2 : DbParis
    option 3 DbMoscow
    etc etc
    I want to send the database to the destination "C:bestore depending on the option i have chosen
    for example if i click 1 then the following database should be copied:
    Dim strSource As String
    Dim strDest As String
    strSource = "D:Front EndsDBBerlin.mdb"
    strDest = "C:bestoreDBBerlin.mdb"
    FileCopy strSource, strDest


    If i click the option 2 then the followinf database should be copied:
    Dim strSource As String
    Dim strDest As String
    strSource = "D:Front EndsDBParis.mdb"
    strDest = "C:bestoreDBParis.mdb"
    FileCopy strSource, strDest


    How can i make the choice to copy for one and the same destination,but for diffeent databses depending on the choice in the option group in the form?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: copy database (Access 2000)

    Say that your option group is named grpCity. Put a command button cmdCopy next to the option group. The On Click event procedure of this command button could look like this:

    Private Sub cmdCopy_Click()
    Dim strCity As String
    Dim strSource As String
    Dim strDest As String
    Select Case Me.grpCity
    Case 1
    City = "Berlin"
    Case 2
    City = "Paris"
    ...
    ...
    End Select
    strSource = "D:Front EndsDB" & strCity & ".mdb"
    strDest = "C:bestoreDB" & strCity & ".mdb"
    FileCopy strSource, strDest
    End Sub

    You could also create a table tblCities with two fields: CityID (number, long integer) and City (text):

    <table border=1><td align=center>CityID</td><td align=center>City</td><td align=right>1</td><td>Berlin</td><td align=right>2</td><td>Paris</td><td align=right>3</td><td>...</td><td align=right>...</td><td>...</td></table>
    You can then replace the Select Case ... End Select block with

    strCity = DLookup("City", "tblCities", "CityID=" & Me.grpCity)

    The rest would remain the same.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy database (Access 2000)

    Dear Hans,


    Thank you for your suggestion, i am trying to implement it.First of all it solves the problem with my computer.There it is perfect !!!!
    It is nice and very interesting solution to ,y problem. However,i came accross the following problem, which is not connected with your answer.
    The problem is with the source. I use the so called Memory Stick with which we transfer
    the data.I insert it in the USB port. The problem is that with one computer the source is read as "D", while with other computers
    with "F". Quite naturaly i get then tthe message that path is not known.
    I have the following source in my function:

    strSource = "D:Front EndsFE" & strCity & ".mdb"


    Can i use some other form of copying the database, or also allow other source,in order to avoid this problem?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: copy database (Access 2000)

    You could ask the user to provide the drive letter for the memory stick:

    Dim strDrive As String
    ' Ask user for drive letter
    strDrive = InputBox("Enter drive letter of source, e.g. D", "Source", "D")
    ' Get out if no letter provided.
    If strDrive = "" Then Exit Sub
    ' Assemble path to database
    strSource = strDrive & ":Front EndsFE" & strCity & ".mdb"

    You can add other tests if you like, for example, whether the user really entered a single letter.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy database (Access 2000)

    Thank you for your perfect solution.I would like to have also a shortened version of that
    where only 2 possibilities are allowed, D or F for example:
    strSource = "D:Front EndsFE" & strCity & ".mdb" or = "F:Front EndsFE" & strCity & ".mdb"
    However what i have written os not correct. How can i do that?

    kind regards

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: copy database (Access 2000)

    The best way would be to put a combo box on a form, with the following properties:

    Row Source Type: Value List
    Row Source: D;F
    Limit to List: Yes
    Name: cboDrive

    Use it like this in the code:

    If IsNull(Me.cboDrive) Then
    MsgBox "Please select a drive.", vbExclamation
    Me.cboDrive.SetFocus
    Exit Sub
    End If
    strSource = Me.cboDrive & ":Front EndsFE" & strCity & ".mdb"

Posting Permissions

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