Runtime Error 3349

[Leesha]

Hi,
I've been importing information downloaded from the internet using the code below:
DoCmd.TransferSpreadsheet TableName:="tblImportYLCSVTemp", FileName:=DLookup("ImportPathLink", "tblAdministrativeInformation") & "\Import.xlsx", HasFieldNames:=True

The data is sequential in that it is month by month. Oftentimes the information is exactly the same as the previous month import, except that there may be a few new names. I randomly get the attached error. I've searched each column to try to find where the error is coming from so that I can give directions to others as to what to look for. I can't find anything that seems wrong. Is there a way around this error? I'm wondering if there are characters that I don't see that are greater than the 255 allowed.

Thanks,
Leesha

[HansV]

You could do the following:
- Split the data in the Excel workbook into two sheets.
- For example, if the sheet has 249 rows originally, cut and paste 124 rows to another sheet (also copy/paste the header row if there is one).
- Import one sheet first, then the other.
- If only one of them produces the error, you know in which half it occurs.
- Apply the same procedure to that half.
- Eventually, you'll know which record causes the error.

[Leesha]

Hi Hans,
I will gladly try this, however there can be thousands of records in the table and the end users may not appreciate having to do this. I will do this to see if I can find the record and "see" what the issue is so I can advise others as to where to look.
Thanks!
Leesha

[Leesha]

Hi Hans,
Just for the heck of it I imported the file manually and created a different table with it. I then created an append query that appended the info into the table the code is trying to put it into and it went in without any errors. I'm wondering if a workaround to having the users have to split their data and try to find the record with the error, if it even exists is to do the do the same thing as I just tried? I would need to be able to have the code import the file and create a new table but to not warn the user that they are about to replace an existing table, as I am sure there are some that would freak out no matter how many times I train them. Is this possible to do with code and if so what would it look like? Thanks!

[HansV]

If you add

DoCmd.SetWarnings False

at the beginning of the code, and

DoCmd.SetWarnings True

at the end, users should not get prompted.

[Leesha]

Thanks! And what do I need to do the this code to make it create a new table vs import into an existing one?
DoCmd.TransferSpreadsheet TableName:="tblImportYLCSVTemp", FileName:=DLookup("ImportPathLink", "tblAdministrativeInformation") & "\Import.xlsx", HasFieldNames:=True

[HansV]

If the TableName argument specifies a non-existent table, Access will create it:

DoCmd.TransferSpreadsheet TableName:="NewTable", FileName:=DLookup("ImportPathLink", "tblAdministrativeInformation") & "\Import.xlsx", HasFieldNames:=True

[Leesha]

Question, Does this actually overwrite the table "NewTable" or import into it? My goal is to overwrite the "NewTable" each time the code is run, so that I don't end up getting the same runtime error on "NewTable".

[HansV]

Why don't you clear the original table before importing data into it?

Code: Select all

    CurrentDb.Execute "DELETE * FROM NewTable", dbFailOnError

[Leesha]

I do delete the data from the table that the new data is being imported to but I'm still getting that random error, however if I manually create a new table and then do the append query it' appends without issue. I'm just trying to find a way get past the random runtime error without having to have the user search around for a file that may not even exist as an issue.

I've been deleting the data using a delete query. I'm assuming the code above has the same effect?

Is is possible to delete table "NewTable" completely prior to import and then the a new instance of "NewTable" is created on import?

[HansV]

Sure.

Code: Select all

    On Error Resume Next
    DoCmd.SetWarnings False
    DoCmd.DeleteObject acTable, "NewTable"
    DoCmd.SetWarnings True
    On Error GoTo 0

[Leesha]

I gave this a try and so far so good! Keeping my fingers crossed.
Thanks!
Leesha

[Alice_22]

Runtime Code 3349 happens when Microsoft Access fails or crashes whilst it's running, hence its name. It doesn't necessarily mean that the code was corrupt in some way, but just that it did not work during its run-time.
Runtime errors happen without warning. The error message can come up the screen anytime Microsoft Access is run. In fact, the error message or some other dialogue box can come up again and again if not addressed early on. Here are ways to remedy the error.

Method 1: Back-up and restore the Database:

Method 2:Inbuilt Compact and Repair Utility:

Method 3:Use any third party Access Database Repair Tool:

Best Regards,
Suyash

[SpeakEasy]

Suyash, your advice seems to be a copy and pasted extract from ErrorVault.com's useless, generic run-time error pages

[SpeakEasy]

>a new instance of "NewTable" is created on import?
>I gave this a try and so far so good!

Here's the thing - when creating a new table Access 'guesses' the datatype of each column being imported based on the first 8 rows. As long as the data in all subsequent rows in each column complies with that guessed format,. all is well. If not, then you will get import errors, just as you have experienced with appending to an existing table (which will of course already have defined the datatypes for each column)

One way around this guessing is to create your own ImportExportSpecification. The easiest way of doing this (unless you are feeling particularly gung-ho and want to directly write the underlying XML from scratch) is to use the Import wizard:

External Data> New Data Source> From File> Excel

then run through the wizard,, selecting exactly how you want the import to work. After you click the Finish button you will be offered the opportunity to Save import steps. Do so. You can then rerun that import simply by using:

CurrentProject.ImportExportSpecifications("Import-Test").Execute

where "Import-Test" should be replaced by the name of your own ImportExportSpecification, which you can find in External data> Saved Imports if you forget it (or by enumerating the ImportExportSpecifications collection). If necessary you can subsequently modify the specification on the fly by editing the XML

CurrentProject.ImportExportSpecifications("Import-Test").XML

[Leesha]

Thanks Speakeasy! I'll give this a try!