Solved

Run-time error '3078' The Microsoft jet database engine cannot find the input table or query

Posted on 2006-07-11
12
2,036 Views
Last Modified: 2008-01-09
Hello,

I have to help somebody who has an application which worked well for a long time.
It is an Access application which links directly into an Excell table.

Since we installed SP2 for Office 2003, we get the following error:

Run-time error '3078' The Microsoft jet database engine cannot find the input table or query 'Imported ABC (ruw)"

And the debugger leads us to the following failing command:

DoCmd.RunSQL "SELECT * INTO [Imported ABC (ruw)] FROM [Imported ABC MND3 (ruw)];"

I don't think it is the sintax or the code, because it worked for several years every week...

Thanks a lot for any suggestion

Luke

0
Comment
Question by:LuckyLuke57
12 Comments
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Can u double check the link to the Excel table? You may perhaps need to recreate the link since its an upgrade


0
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
Hi LuckyLuke57,
Have you checked that you have a table with this name?

Pete
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
[Imported ABC MND3 (ruw)]   <----- It can't find this. It's either been renamed, you don't have permissions to it anymore, or like Rocki said, the link needs re-linking.  You may also check permissions on this spreadsheet to make sure you CAN still link to it.
J
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
Is this a linked excel table?  

Part of SP2 was to disable linked Excel table manipulation because of a lawsuit.

http://www.theregister.co.uk/2006/02/07/microsoft_office_access_infringement/
0
 
LVL 10

Author Comment

by:LuckyLuke57
Comment Utility
jimpen,

It is indeed a linked Excel table.

But I can't revert to a situation before SP2, so is there a work-around?

Thanks,

Luke

0
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
Comment Utility
You can still do straight imports and export in excel format, but you can't do an append query to an excel table.

VBA Command:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MyTableName", _
        "C:\MyPath\MyFileName.xls", True
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 39

Expert Comment

by:stevbe
Comment Utility
Are you adding records from excel into access or the other way around?

if you really need to append then you can use Excel automation ... open the existing excel file, go to the last row of the excel worksheet, open a recordset of the data you want to append and then loop through the recordset adding each record to the spreadsheet.
0
 
LVL 10

Author Comment

by:LuckyLuke57
Comment Utility
I have an application in Access, with a form with a lot of buttons.
When I click on one button, it has to import data from Excel.

And then I come via the debugger in the code

DoCmd.RunSQL "SELECT * INTO [Imported ABC (ruw)] FROM [Imported ABC MND3 (ruw)];"

and with the above errormessage.

I know the direct link is forbidden, and I am willing to test the work-around from jimpen, but because I don't know a lot of Access and his SQL-commands and VBA, I don't know how to substitute the suggestion of jimpen into my problematic code.

Has it to become something like

' this replaces the direct link
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Imported GOR MND1 (ruw)", _
        "C:\MyPath\MyFileName.xls", True
' and the old command must work
DoCmd.RunSQL "SELECT * INTO [Imported ABC (ruw)] FROM [Imported ABC MND3 (ruw)];"

OR is it

' this replaces the direct link
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "[Imported GOR MND1 (ruw)]", _
        "C:\MyPath\MyFileName.xls", True
' and the old command must work
DoCmd.RunSQL "SELECT * INTO [Imported ABC (ruw)] FROM [Imported ABC MND3 (ruw)];"

Luke
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
Which of these your linked excel table? And which way are you trying to take the data -- import or export to the excel spreadsheet?

[Imported ABC (ruw)]
[Imported ABC MND3 (ruw)]
0
 
LVL 10

Author Comment

by:LuckyLuke57
Comment Utility
VERY GOOD QUESTION
Indeed, it can be an export to excel, which is giving the problem or an import from excel.
Because I am beginning to investigate this problem, I have to ask to the people who use the application.
But they went home already (I am in Europe), so I come back to this within 16 hours.

Thanks a lot for the moment...
0
 
LVL 10

Author Comment

by:LuckyLuke57
Comment Utility
Hello guys,

The guy with the problem application went on vacation, so I can't ask more info.
The work-around of jimpen helped:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ABC Export", ABC_Output_Path + ABC_OutputFile_MND3, True

Thanks,

Luke
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

771 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now