Run time error with importing after splitting database (1 Viewer)

tmyers

Well-known member
Local time
Today, 19:01
Joined
Sep 8, 2020
Messages
1,090
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.
Code:
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
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 16:01
Joined
Oct 29, 2018
Messages
21,358
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

Enthusiastic Amateur
Local time
Today, 23:01
Joined
Sep 21, 2011
Messages
14,048
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

Lifelong Learner
Local time
Today, 16:01
Joined
Mar 14, 2017
Messages
8,738
@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

Well-known member
Local time
Today, 19:01
Joined
Sep 8, 2020
Messages
1,090
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

Well-known member
Local time
Today, 19:01
Joined
Sep 8, 2020
Messages
1,090
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

I’m here to help
Staff member
Local time
Today, 16:01
Joined
Oct 29, 2018
Messages
21,358
Hi @tmyers. Not sure who you were replying to above, but did you try my suggestion? Just curious...
 

tmyers

Well-known member
Local time
Today, 19:01
Joined
Sep 8, 2020
Messages
1,090
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’m here to help
Staff member
Local time
Today, 16:01
Joined
Oct 29, 2018
Messages
21,358
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

Well-known member
Local time
Today, 19:01
Joined
Sep 8, 2020
Messages
1,090
I deleted the table and remade it from scratch, and that did not resolve the problem.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:01
Joined
Oct 29, 2018
Messages
21,358
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

Well-known member
Local time
Today, 19:01
Joined
Sep 8, 2020
Messages
1,090
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

I’m here to help
Staff member
Local time
Today, 16:01
Joined
Oct 29, 2018
Messages
21,358
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

Well-known member
Local time
Today, 19:01
Joined
Sep 8, 2020
Messages
1,090
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’m here to help
Staff member
Local time
Today, 16:01
Joined
Oct 29, 2018
Messages
21,358
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

Well-known member
Local time
Today, 19:01
Joined
Sep 8, 2020
Messages
1,090
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

I’m here to help
Staff member
Local time
Today, 16:01
Joined
Oct 29, 2018
Messages
21,358
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

Well-known member
Local time
Today, 19:01
Joined
Sep 8, 2020
Messages
1,090
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

Lifelong Learner
Local time
Today, 16:01
Joined
Mar 14, 2017
Messages
8,738
Are you sure that this is the problem code? Are you calling that code from other code? Can you show the 'whole' code?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:01
Joined
Oct 29, 2018
Messages
21,358
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!
 

Users who are viewing this thread

Top Bottom