Importing into existing table error...

Raysoc

Board Regular
Joined
Feb 10, 2009
Messages
227
I am currently experiencing an error I can not determine.

I have weekly files of data, each file has the exact same headings, these files are excel files.
If I import file 1 into table 1, then import file 2 into table 1 I get an error, the error simply says an error occurred the file was not imported. So my assumption would be the headings are not the same. However they are, 100%. If I import both files to separate tables and then use a union query then its not a problem. However, at the moment I am trying to analyze a few months data, while I could probably import them all, that's a huge union query to work on, when having one table would be simpler.

I am racking my brain trying to think of a reason they wont import, maybe when importing a field is a number, but if the first cell is blank then maybe it thinks I'm trying to import a text into a number field? SO then the only solution is to make the first table all text fields import all the tables and then change them back, but then next week when I want to add another weeks data I would have to do the same, that's assuming that's the error.

Any ideas?
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

gauntletxg

Well-known Member
Joined
Jul 15, 2008
Messages
636
Impoty everything into an empty dummy table where all the fields are text, append the data to your real table, and then delete the records from the dummy one.
 

Raysoc

Board Regular
Joined
Feb 10, 2009
Messages
227
That does not even work :(

I imported the first table, changed all fields to text and tried importing the second file, just said could not import..

Now im having to import every table then append every table... very big PITA.

Any easy VBA code to import all .xls files from a directory and then append to a master table? lol
 

gauntletxg

Well-known Member
Joined
Jul 15, 2008
Messages
636
No, create 2 tables. One is the real one with all of your data types, the other is exactly the same only text. Import everything into the second, then append to the first, and delete from the second. You should not be changing data types after importing. If you can't import to a table with all text fields, then you might have some remnant data or formatting in your Excel file that Access is inadvertently recognizing. Try deleting the columns after your last field, and the rows after your last record and try the import again.

Importing many files could be done like

Code:
Dim strPath As String, strFile As String
strPath = "C:\*.xls"
strFile = Dir(strPath)

On Error GoTo EarlyExit
DoCmd.SetWarnings False
Do While strFile <> ""
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblDummy", strFile
    DoCmd.OpenQuery "qryAppend" ' append from tblDummy to your real table
    DoCmd.OpenQuery "qryDelete" ' Delete data from tblDummy
    strFile = Dir()
Loop
DoCmd.SetWarnings True
Exit Sub
EarlyExit:
Debug.Print Err.Number & " - " & Err.Description
Resume Next

Or if you follow a unique, standard naming convention for your imports you can put them all in separate tables and create your union query on the fly

Code:
Dim db As Database, sql As String, tdf As TableDef
Set db = CodeDb

For Each tdf In db.TableDefs
    If tdf.Name Like "tblUpload*" Then
        sql = "SELECT " & tdf.Name & ".* " & _
        "FROM " & tdf.Name & ";" & _
        vbCrLf & "UNION" & vbCrLf & sql
    End If
Next

sql = Replace(Replace(sql, Chr(12), ""), Chr(15), "")
sql = Trim(Left(sql, Len(sql) - 7))
db.QueryDefs("qryUnion").sql = sql
Set db = Nothing
DoCmd.OpenQuery "qryUnion"
 

Raysoc

Board Regular
Joined
Feb 10, 2009
Messages
227

ADVERTISEMENT

Can't even import into the text only table... i opened the file deleted all rows and columns after the data and still no. Very temper mental.. I am stuck importing a single table and appending each separately which is taking forever...

thx for the code snippets I am going to test them out!

How does the code handle multiple sheets in a workbook tho?
 

Raysoc

Board Regular
Joined
Feb 10, 2009
Messages
227
SOLVED:

In excel some of the heading names have periods "a.c" so when I import table one into access it strips them out, but im importing excel sheets where its present, so because Access does not allow periods in the field names the two no longer align and I can not import into!

Found this error by moving my data to a laptop with Access 2007 to see if Access07 could do it, it happens to have better error handling!

yay
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,841
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top