Run time error with importing after splitting database

[tmyers]

I have run into a peculiar issue. I have some code that does the transfer spreadsheet method to import excel files into my database. I have temporary tables setup to receive each particular excel file to be imported.

All of that works just fine with no hiccups, until I split the database. Once split, something that worked just fine, breaks. I now get Run-time error '3349': You cannot record you changes because a value you entered violates the settings defined for this table or list. Comparing the newly split tables with a backup, they are exactly the same across the board, including structure and properties.

To make trouble shooting even harder for me, when I delete that particular version of the database and split it again via the back up, the vba for a different import breaks (I have 6 buttons for different vendors since the excel being imported is structured differently for each but only difference is the destination table).

Why is this happening and how can I correct it?

EDIT:
Adding code for reference. Shout out to theDBGuy, as he was kind enough to initially write this for me.

Public Function ImportXLSpectrum() As Boolean
'thedbguy@gmail.com
'10/9/2020

Dim fd As Object
Dim strFile As String

Set fd = Application.FileDialog(3)
    
With fd
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "Excel Files", "*.xls*"
    If .Show Then
        strFile = .SelectedItems(1)
    End If
End With

If strFile = "" Then
    ImportXL = False
Else
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblTempSpectrum", strFile, True
    ImportXL = True
End If

Set fd = Nothing

End Function

 

[theDBguy]

Hi. If the temp table is now a linked table after you split the db, try deleting it and then run your import code again.

 

[Gasman]

You are assuming that the tables are at fault?
It could be the data in the Excel sheet.? If a column has been swapped and was numeric, now text, that might cause it.?

 

[Isaac]

@Gasman brings up a good point. There are always some times when what appears to be the cause is just a coincidence and a red herring. Check changes in Excel as thoroughly as you did the tables.

 

[tmyers]

I triple checked both. I tried importing the same excel files over in both instances. Before split, they went just fine. After the split, one fails. If I then split the backup and test again, a different one fails. It (to me) seems random which one decides to no longer work.

 

[tmyers]

I did also verify the tables by manually importing the excel that failed through VBA. It imports with no problems, but when I try to do the exact same file with VBA, it fails and gives that run time error.

 

[theDBguy]

Hi @tmyers. Not sure who you were replying to above, but did you try my suggestion? Just curious...

 

[tmyers]

Hi @tmyers. Not sure who you were replying to above, but did you try my suggestion? Just curious...

I had not tried that method since manually importing worked. I will try it just to cross that off the list however.

 

[theDBguy]

I had not tried that method since manually importing worked. I will try it just to cross that off the list however.

Let us know how it goes...

 

[tmyers]

I deleted the table and remade it from scratch, and that did not resolve the problem.

 

[theDBguy]

I deleted the table and remade it from scratch, and that did not resolve the problem.

Hmm, I didn't say to remake it from scratch. Not sure if the result will be any different, but what I meant was delete the table and then run your import code without recreating the table first. Can you please try that? Thanks.

PS. Also, please notice I keep saying "table" (singular). I just want you to try one table and execute the import only for that table. We're merely trying to isolate the problem at this point. We don't want to unknowingly fix one problem and see an error for another and then think the solution we tried didn't work at all, when it actually did. Hope that makes sense.

 

[tmyers]

Hmm, I didn't say to remake it from scratch. Not sure if the result will be any different, but what I meant was delete the table and then run your import code without recreating the table first. Can you please try that? Thanks.

PS. Also, please notice I keep saying "table" (singular). I just want you to try one table and execute the import only for that table. We're merely trying to isolate the problem at this point. We don't want to unknowingly fix one problem and see an error for another and then think the solution we tried didn't work at all, when it actually did. Hope that makes sense.

Fair enough. Currently after splitting it this time, only one button isn't working. I unlinked that table and tried to run it, which made an error since the destination table no longer existed. I relinked it and ran it again, but got the run time error again. I then unlinked the table and went to the back end and deleted it. I then manually imported the file to recreate the table (as I had done when I first made my import process), then relinked the table. Received the run time error again.

I then made another copy of my backup, split it then tried importing. That button now works, but another doesnt. Its very weird.

 

[theDBguy]

Fair enough. Currently after splitting it this time, only one button isn't working. I unlinked that table and tried to run it, which made an error since the destination table no longer existed. I relinked it and ran it again, but got the run time error again. I then unlinked the table and went to the back end and deleted it. I then manually imported the file to recreate the table (as I had done when I first made my import process), then relinked the table. Received the run time error again.

I then made another copy of my backup, split it then tried importing. That button now works, but another doesnt. Its very weird.

Okay, thanks for trying all that. One thing to clarify, I think the import code imports the data to a "local" table. So, if you want to put the data to a "linked" table, then you could try importing the data to a local "temp" table first, and then run and APPEND query to move the imported data from the temp table to the linked table.

 

[tmyers]

Okay, thanks for trying all that. One thing to clarify, I think the import code imports the data to a "local" table. So, if you want to put the data to a "linked" table, then you could try importing the data to a local "temp" table first, and then run and APPEND query to move the imported data from the temp table to the linked table.

I can give that a try. Out of curiosity, why is it that seemingly only one button appears to break while the others work?

 

[theDBguy]

I can give that a try. Out of curiosity, why is it that seemingly only one button appears to break while the others work?

Can't really say why without seeing it. That's why I am only concentrating on one table and one code, because that's all I can picture right now. This is a "general" approach. I'm hoping if it works to one table and import code, it might apply to other tables and code.

 

[tmyers]

Converting the temp tables back to local fixed the problem. It just must not agree with trying to import into tables onto a different computer. May be something in the security on the destination computer that causes it to fail.

 

[theDBguy]

Converting the temp tables back to local fixed the problem. It just must not agree with trying to import into tables onto a different computer. May be something in the security on the destination computer that causes it to fail.

Until you get to the bottom of it, I suppose you could just use this workaround for now.

 

[tmyers]

Until you get to the bottom of it, I suppose you could just use this workaround for now.

The back end is in a shared file on a central computer at my work, so it is subject to all of our IT firewalls and such. Something might not have been agreeing with a remote process trying to write to a file.

 

[Isaac]

Are you sure that this is the problem code? Are you calling that code from other code? Can you show the 'whole' code?

 

[theDBguy]

The back end is in a shared file on a central computer at my work, so it is subject to all of our IT firewalls and such. Something might not have been agreeing with a remote process trying to write to a file.

Appreciate your situation is unique. It's hard to help you troubleshoot without being there with you. Good luck!