Execution of this application has stopped due to a run-time error.

[ramirezx@ddmfg.com]

Hi,


I have an Access accdb file that I'm running on a computer that has a run-time installed. I've tried everything to figure it out, I need it to do a delete query and append query.

I've narrowed it down to the append query, and it's appending a linked Excel file to a table in the database. It has to do with the link, I've imported the Excel file in the database and both append and delete queries.

It just gives me the error when I'm trying to append from a linked Excel file any idea what could be causing this? The computer that has run time installed has no other program installed, could it be that because Excel is not on the computer is what is causing the problem? It works on my computer that has Office 365 with Access 2016, the other computer only has Access Run Time?

Anyone that can help I would greatly appreciate it!

Thanks,
Xavier

-----------------------------------
THIS DELETES DATA FROM THE TABLE:

DoCmd.SetWarnings False

DoCmd.OpenQuery "qry_05a_tbl_XCR_qry_01a_PartNumList_1b_V2_Del ete"


DoCmd.SetWarnings True

---------
THIS APPENDS DATA FROM THE LINKED EXCEL FILE:

DoCmd.SetWarnings False

DoCmd.OpenQuery "qry_05b_tbl_XCR_qry_01a_PartNumList_1b_V2_App end"


DoCmd.SetWarnings True

-----------------------------------

[ranman256]

you cannot delete records using a link.
you must build it as IN (subquery), similar to a FIND DUPLICATES query.

use the query wizard and make a FIND DUPLICATES query, then look at the design on how they use the: IN (subquery)
do the same for Delete query.

[isladogs]

I think Ranman was referring to the fact that you can't edit a linked Excel file.
However that's not what you are trying to do here.

I suspect you have code errors causing it to fail on the run time version.
In the VBE options, check that Require Variable Declaration is ticked.
This will add the line Option Explicit to all new code modules and prevent code running with undefined variables.
Next add the line Option Explicit as the second line in all existing code modules
Now check the database compiles and, if not, run repeatedly and fix any compile errors it finds.

Next check for missing references. That would also cause issues.
Finally if you still have issues, you could decompile. I'll explain later if necessary

One more thing
You really ought to simplify your naming convention.
Having all those underscores makes coding more difficult and errors more likely.
Also why do you have qry twice in each name?

Suggest something more like qryAppendPartNumList

[ramirezx@ddmfg.com]

Thanks guys,

I wasn't able to figure out Require Variable Declaration and Option Explicit. I would love if you have any links that gives a basic example where I can better at that part of Access that I'm not good at yet. Been working with Access from the beginning and want to get better with VBA and modules.

Thanks for the suggestion on consolidating my queries names, I will start doing that!

I did figure out the issue is with the linked Excel file and append query, I ended up basically importing the Excel file in the db as a table then I could do the append or delete.

-----------------------------
Dim strXls As String
strXls = CurrentProject.Path & Chr(92) & "PartNumList.xls"
DoCmd.TransferSpreadsheet acImport, , "tblPartNumList ", _
strXls, True, "PartNumList!"
-----------------------------

Again thank you guys for your help!

[isladogs]

Have a look at tips 1 & 2 in this link: http://www.fmsinc.com/microsoftacces...ons/index.html

[pbaldy]

I wasn't able to figure out Require Variable Declaration and Option Explicit. I would love if you have any links that gives a basic example where I can better at that part of Access that I'm not good at yet. Been working with Access from the beginning and want to get better with VBA and modules.

Here's a visual:

http://www.baldyweb.com/OptionExplicit.htm