?
Solved

Access automated and manual Import error.

Posted on 2015-02-17
17
Medium Priority
?
281 Views
Last Modified: 2015-02-17
Import fails with the error "Unexpected error from external database driver (1),"  Other posted solutions don't seem to apply.  Excel file location and import range is correct;  file and import table structure and formatting is consistent.  Here is the code:
Public Sub ImportSourceData()
    Dim sPath As String
    sPath = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\"
    Src1 = sPath & "MB25.XLSX"
    Src2 = sPath & "MB52Inventory.XLSX"
    Src3 = sPath & "PKMC.XLSX"
    WrhsTrns = sPath & "WrhsTrns.xls"
    Debug.Print xlFile
'Import and delete the data files
    DoCmd.SetWarnings False
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "MB25T", Src1, True, "Sheet1!A1:Q5000"

Open in new window

Note: this import is the first of three into three separate tables.  The import data is exported from SAP in .xlsx format.

There is other code below that is not yet edited, but I'm not there yet. The Excel file and database are attached.
MB25.XLSX
Pick.accdb
0
Comment
Question by:Jay Williams
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 4
  • +1
17 Comments
 
LVL 35

Expert Comment

by:ste5an
ID: 40614117
acSpreadsheetTypeExcel9 is imho the Excel 2000 format, while you're using an .xlsx file which is a newer format.
0
 

Author Comment

by:Jay Williams
ID: 40614129
Thanks for that.  What other arguments might be correct?
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40614146
replace this
acSpreadsheetTypeExcel9

by this
acSpreadsheetTypeExcel12
or
acSpreadsheetTypeExcel14

depending on Excel version in your system

gowflow
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Jay Williams
ID: 40614170
with 14 I got the same error; with 12 I got:
3170.JPG
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40614203
use this

DoCmd.TransferSpreadsheet acImport,10, "MB25T", Src1, True, "Sheet1!A1:Q5000"
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40614222
then its
acSpreadsheetTypeExcel10

gowflow
0
 

Author Comment

by:Jay Williams
ID: 40614230
Got the same thing, Rey.  I did notice something else weird; when I try to open the MB25.xlsx file, I either get a blank screen (no sheet) or an error saying excel has stopped working.  Earlier this week I had a "cannot read '~$Book.xltm" error that I had to fix.  If I open Excel with a new blank sheet, MB25.xlsx opens just fine.  Think I have something else going on?
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40614237
Just to understand where is your macro sitting in an Excel or Access ?
gowflow
0
 

Author Comment

by:Jay Williams
ID: 40614243
This is in Access.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40614251
@goflow
there is no "acSpreadsheetTypeExcel10"
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40614253
@Jay Williams
can you create a new db and try the codes.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40614254
tried to open the DB you attached but no tables there nothing !
"MB25T" is supposed to be a table in the DB is it ?

gowflow
0
 

Author Comment

by:Jay Williams
ID: 40614269
Yes, I just have the navigation pane shut off F11 will show you everything.
0
 

Author Comment

by:Jay Williams
ID: 40614274
I'll give that a whirl, Rey.  FYI, I did also try your trick of importing it into a brand new created target table, and that failed too, with the same error.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40614285
<FYI, I did also try your trick of importing it into a brand new created target table, and that failed too, with the same error. >
then, the problem is with your excel file.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 40614313
your excel file was saved in a different format.

run this codes

Sub saveasxlx()
Dim sPath As String, Src1 As String
    sPath = CurrentProject.Path & "\"
    Src1 = sPath & "MB25.XLSX"

Dim xlObj As Object
Set xlObj = CreateObject("excel.application")
    xlObj.workbooks.Open Src1
    xlObj.activeworkbook.saveas Src1, FileFormat:=51
    xlObj.Quit
End Sub

then do the import with

DoCmd.TransferSpreadsheet acImport, 10, "NewMB25T", Src1, True, "Sheet1!A1:Q5000"
0
 

Author Closing Comment

by:Jay Williams
ID: 40614378
Once again, you nailed it.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question