Solved

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

Posted on 2006-07-11
12
2,088 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
ID: 17081135
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
ID: 17081140
Hi LuckyLuke57,
Have you checked that you have a table with this name?

Pete
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17081252
[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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 38

Expert Comment

by:Jim P.
ID: 17081272
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
ID: 17081623
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
ID: 17081684
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
 
LVL 39

Expert Comment

by:stevbe
ID: 17081833
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
ID: 17082101
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.
ID: 17082280
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
ID: 17082419
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
ID: 17089037
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.
ID: 17089670
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Object Variable or Block Variable Not Set - Adobe Acrobat 31 67
On Key Down Access 2010 6 41
Insert Into syntax error 10 31
Access VBA to Count Worksheets in a Workbook 3 22
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

829 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