• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

Access automated and manual Import error.

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
Jay Williams
Asked:
Jay Williams
  • 7
  • 5
  • 4
  • +1
1 Solution
 
ste5anSenior DeveloperCommented:
acSpreadsheetTypeExcel9 is imho the Excel 2000 format, while you're using an .xlsx file which is a newer format.
0
 
Jay WilliamsOwnerAuthor Commented:
Thanks for that.  What other arguments might be correct?
0
 
gowflowCommented:
replace this
acSpreadsheetTypeExcel9

by this
acSpreadsheetTypeExcel12
or
acSpreadsheetTypeExcel14

depending on Excel version in your system

gowflow
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Jay WilliamsOwnerAuthor Commented:
with 14 I got the same error; with 12 I got:
3170.JPG
0
 
Rey Obrero (Capricorn1)Commented:
use this

DoCmd.TransferSpreadsheet acImport,10, "MB25T", Src1, True, "Sheet1!A1:Q5000"
0
 
gowflowCommented:
then its
acSpreadsheetTypeExcel10

gowflow
0
 
Jay WilliamsOwnerAuthor Commented:
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
 
gowflowCommented:
Just to understand where is your macro sitting in an Excel or Access ?
gowflow
0
 
Jay WilliamsOwnerAuthor Commented:
This is in Access.
0
 
Rey Obrero (Capricorn1)Commented:
@goflow
there is no "acSpreadsheetTypeExcel10"
0
 
Rey Obrero (Capricorn1)Commented:
@Jay Williams
can you create a new db and try the codes.
0
 
gowflowCommented:
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
 
Jay WilliamsOwnerAuthor Commented:
Yes, I just have the navigation pane shut off F11 will show you everything.
0
 
Jay WilliamsOwnerAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
<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
 
Rey Obrero (Capricorn1)Commented:
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
 
Jay WilliamsOwnerAuthor Commented:
Once again, you nailed it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 7
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now