Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Link Table via transferdatabase (Access 2000)

    Misleading subject (transferspreadsheet instead of transferdatabase) edited by HansV

    Hi everybody:

    I've written a front-end reporting application that allows users to create custom tables containing the records they want to run their reports on. These tables are created and saved to the back-end database via the transferspreadsheet action from button click code on a user form. A second call to transferspreadsheet creates the link in the front-end to the new table.

    Code:

    Set db = CurrentDb
    '1. Set the make table querydef's SQL to the argument string
    Set qTmpUDef = db.QueryDefs("qTmpMakeTable")
    qTmpUDef.Sql = strSQLMT
    On Error Resume Next 'NOTE: Execute method will return error if table already exists.
    DoCmd.DeleteObject acTable, strTable 'in case desired table doesn't exist or already deleted
    On Error GoTo err_fnMakeTable
    '2. Run the make table querydef
    db.Execute "qTmpMakeTable"
    '3. Test if querydef changed anything
    If db.RecordsAffected > 0 Then 'Make table succeeded
    fnMakeTable = True
    Else 'No records for new table
    fnMakeTable = False
    End If

    If blRemote = 0 Then
    Else 'Send table to back-end and create link
    DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acTable, strTable, strTable, 0, -1
    DoCmd.DeleteObject acTable, strTable 'delete local copy
    DoCmd.TransferDatabase acLink, "Microsoft Access", strPath, acTable, strTable, strTable, 0, -1
    End If

    Everything works fine when I test it (naturally!) But when a user tried it, although the custom table was created, when she re-ran the report, she got an Access error message "could not find the table or query . . . " and the link did not appear. Can anyone tell me what would cause this? Is it a permissions issue, and if so, do I have to give my users administration rights to either or both of the back-end or front-end files? Or is it just a matter of the new back-end table being hidden? This doesn't seem to affect any of the other linked tables.

    Thank you, in advance, for any light you can shed on this. I need to fix it asap.

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

    Re: Link Table via transferdatabase (Access 2000)

    All users need to have Modify permissions in the folder containing the front end and in the folder containing the back end. And if you have applied user-level security in the database, they need to have sufficient permissions to create and delete tables.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link Table via transferdatabase (Access 2000)

    Hi Hans:

    Thank you. I checked, and the users do have full permissions on the network folder containing the back-end. The front-end is being copied via a vbs file to each user's temp folder and run from there. The code to get that folder is: sTempDirectory = fso.getspecialfolder(2) where fso = a filesystemobject. I can't be certain about the full path to the temp folder it returns: [ Cocume~1KRISS~1.LYSLocals~1Temp ], but it looks like Cocuments and SettingsUSERNAME.CompanyNameLocal SettingsTemp. I have full permissions on all my own temp folders. The user has full permissions on all her temp folders except Cocments and SettingsDefault UserLocal SettingsTemp, but she does have full permission on Cocuments and SettingsUSERNAME.CompanyNameLocal SettingsTemp, so I think there must be another issue.

    Am I missing something?

    Thanks!

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

    Re: Link Table via transferdatabase (Access 2000)

    If the code is not able to create the link, I'd expect an error message, unless you don't display them in your error handling section. Would it be possible for you to go to a user's PC and single-step through the code, to see what happens?

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link Table via transferdatabase (Access 2000)

    It's an mde file. But I'll try to set up some kind of test (if I can find a user both logged on and absent from cube!)

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

    Re: Link Table via transferdatabase (Access 2000)

    Try to test with (a copy of) the .mdb the .mde was created from.

  7. #7
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link Table via transferdatabase (Access 2000)

    Okay, silly me! I figured out EXACTLY what I did wrong! My code allowed the user to create the table in the linked mdb and create the link to the current mde, which is a COPY of the production version on the network. So, the code runs fine until the user logs off and then logs back on again, . . . when my code overwrites her COPY with the production version, which doesn't have the table link!

    What I need to do is either create the link in the PRODUCTION copy of the mde, or overwrite it with the user's copy, which is, fortunately or unfortunately, open.

    Does anybody know how to either 1) create a link from one external mdb to another external file (the mde that is NOT the current mde where the code is running), or 2) copy the current mde file to an external file while the current mde is open and code is running? Unless you can think of a better way to accomplish this????

    (I'm so smart I outsmart myself sometimes . . . . )

    Thanks!

    Kathryn

  8. #8
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link Table via transferdatabase (Access 2000)

    On second thought, perhaps the simplest way to handle this is to retrieve the data from the table in the mdb using a remote query instead of creating a table link on subsequent calls to the report code.

    YES!!!! Works like a charm, and I only had to change 1 line of code . . .FYI, just add full path to your back-end file at the end of your sql, in single quotes, i.e.:

    SELECT * FROM tblWhatever IN 'C:msoffice97OfficeSamplesNorthwind.mdb'

    It's a little slow, but look, Ma! No links!

Posting Permissions

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