Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2348
  • Last Modified:

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

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
LuckyLuke57
Asked:
LuckyLuke57
1 Solution
 
rockiroadsCommented:
Can u double check the link to the Excel table? You may perhaps need to recreate the link since its an upgrade


0
 
peter57rCommented:
Hi LuckyLuke57,
Have you checked that you have a table with this name?

Pete
0
 
jefftwilleyCommented:
[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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Jim P.Commented:
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
 
LuckyLuke57Author Commented:
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
 
Jim P.Commented:
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
 
stevbeCommented:
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
 
LuckyLuke57Author Commented:
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
 
Jim P.Commented:
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
 
LuckyLuke57Author Commented:
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
 
LuckyLuke57Author Commented:
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
 
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now