Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009

    Red face Unanswered: Import module not working with split database

    I have a database that imports text files into a table for service reps to view. I am ready to deploy this to the end users. My final step was to split the database so that multiple users could connect simultaneously, but have found that splitting the database breaks my import module. Here is the code:

    Function Import52Feed_40s()
    On Error GoTo ErrorHandler
    Dim tbl As AccessObject
    Dim Today As Date
    Dim strSQL As String, strPath As String, strFileName As String
        DoCmd.SetWarnings True
        Today = Date
        If Weekday(Date) = 2 Then
            strFileName = "\\lm\central\Permkt\Dover-Financial\Billing Customer Service\BCS-Shared Folder\Daily Reports (fiche)\" & Format(Today - 3, "YYYY") & "\" & Format(Today - 3, "YYYY-MM") & "\" & Format(Today - 3, "MM-DD-YY") & " R1 j&l.txt"
            strFileName = "\\lm\central\Permkt\Dover-Financial\Billing Customer Service\BCS-Shared Folder\Daily Reports (fiche)\" & Format(Today - 1, "YYYY") & "\" & Format(Today - 1, "YYYY-MM") & "\" & Format(Today - 1, "MM-DD-YY") & " R1 j&l.txt"
        End If
        If fIsFileDIR(strFileName) Then
            DoCmd.TransferText acImportFixed, "Daily Cash Fiche Import1", "1 - RAW OP65 UNAPPLIED CASH REPORT - 40s", strFileName, True, ""
    The module goes to my error handler after trying to run this last line of code.


    -'fIsFileDIR' returns a true statement when I debug, so that is not the issue.

    -'Daily Cash Fiche Import1' is an import spec. I've confirmed that this works correctly independant of this script.

    -'1 - RAW OP65 UNAPPLIED CASH REPORT - 40s' is a linked table on my back-end. This import module correctly appended data to this table before I split the databse, but seems to be the root of the problem.

    -When I change the name of the table in this script (ie, to 1 - RAW OP65 UNAPPLIED CASH REPORT - TEST, or something similar), the import module works correctly and does not error out. However, this new table is created on the front-end, meaning it is not visible in the back-end or to other users. This obviously will not work in my production environment.

    From my newbie perspective, it appears that the linked tables in the back-end are almost protected, and when this import module tries to add data to the tables, it is getting locked out and going to my error handler. This was supposed to go live tomorrow, but I cannot figure out why this is happening.

    I have another database (for a separate process) that is built similarly, and it has worked flawlessly for over two months).

    Thanks for the help!

  2. #2
    Join Date
    Nov 2007
    Adelaide, South Australia
    Horrible table names... way too long, contains spaces, minus signs etc. That's the only thing I can think of right now with this.

    Back end/front end, it shouldn't make much of a difference unless another user locks data by editing it and the import is working with that locked table. In other words, the only difference having linked tables should make is the multi-user aspect of it. Because of this, I actually suspect the table name may be to blame.

    Guessing though :/
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jun 2009
    Thanks for the response, I appreciate the attempt. I just renamed the table to "1import40" both in my back end and in the module, with the same result. Also, my other, more functional database has the same ugly names, so I'm afraid that's not it. Thanks though.

    Oh, and nobody else knows where this db is, so nobody else is in it.

    I'm 100% perplexed on this one. I'm training two people on this today, with the idea that they'll both be able to go in and use it tomorrow. Works perfectly when not split, but craps the bed when I split it. So frustrating!

Posting Permissions

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