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:
On Error GoTo ErrorHandler
Dim tbl As AccessObject
Dim Today As Date
Dim strSQL As String, strPath As String, strFileName As String
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"
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).
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.
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!