Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jan 2002
    Location
    Scotland, Midlothian, Scotland
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Splitting A database - code won't run (Access 97 SR2)

    Help Please.
    Am tried to take advice given elsewhere in this forum to split a shared Access application to have tables on server and other objects on user PC's. Have found nothing to suggest that code needs to be set up differently for split databases.
    Step 1. Unsplit database sitting in server and working fine - four concurrent users.
    Step 2. Copy taken of database (wouldn't dare try this on the live database unless I was sure it worked!)
    Step 3. Copy split using Access utility. Tables and objects in same directory on server.
    Step 4. Try to run database - falls over at the following point.

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("AppealsOfficers")
    With rst
    .Index = "Name" ' Falls over on this line with message "Operation is not supported for this type of object"
    .Seek "=", MyUser
    'Other code
    End With

    Have tried moving objects to local PC and running from there
    Have tried creating a new database and linking tables, copy form that activates code.
    I have also checked that the references are the same for both the backend tables and the frount end objects.

    Both give same result.

    Any suggestions as to how I go about getting my code to work?
    Does this have anything to do with how I reference linked tables in code?

  2. #2
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting A database - code won't run (Access 97 SR2)

    John,

    My first thought is that the Table links are pointing to the wrong directory.

    Select Tools|Add-Ins| Linked Table Manager.

    Seelct all the linked tables, check Always Prompt for New Location and then Click OK

    This will bring up the FileOpen Dialog, where you need to go to the directory that the BE (tables) DB is. Select the BE and then Click Open.

    This will re-link all the tables.

    One thing to keep in mind is that you are better off going through My Network (Network Neighbourhood in Win 9X IIRC) and navigate to the server that way instead of using a mapped drive letter. You would be linking to the UNC path instead. Drive letters can, and do change, and will break the DB again. But servers and server names change less often.

    I am attaching a screenshot of the A2K Linked Table Manager so you can see what it should look like.
    Attached Images Attached Images
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

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

    Re: Splitting A database - code won't run (Access 97 SR2)

    According to DAO help:
    <hr>You can't use the Seek method on a linked table because you can't open linked tables as table-type Recordset objects.<hr>
    See if you can replace Seek with FindFirst; this method will work in linked tables. The code could look like:<pre>Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("AppealsOfficers", dbOpenDynaset)
    With rst
    .FindFirst "Name = '" & MyUser & "'"
    If .NoMatch = False Then
    'Other code
    End If
    End With</pre>

    Hope this helps.

  4. #4
    Lounger
    Join Date
    Jan 2002
    Location
    Scotland, Midlothian, Scotland
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting A database - code won't run (Access 97 SR2)

    Thanks Bryan
    I think the links are OK becuase I tried it by specifically setting up a new database and creating links and got the same problem. I see there is another post pointing out that the .seek method I was using does work with a linked Db - at present this looks a more promising line to pursue.

    Thank you for your - very quick - input

  5. #5
    Lounger
    Join Date
    Jan 2002
    Location
    Scotland, Midlothian, Scotland
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting A database - code won't run (Access 97 SR2)

    Hans
    Thank you for that - you are clearly better at using the help than I am.
    I will give this a try - may be a day or two before I get time now. Your help is greately appreciated.

  6. #6
    Lounger
    Join Date
    Jan 2002
    Location
    Scotland, Midlothian, Scotland
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting A database - code won't run (Access 97 SR2)

    Hans
    Got a chance to try that this morning. It works fine. Just need to change all the other instances where I use this code now. Thanks for your help

Posting Permissions

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