Databse crashed if SQL strin runs from CurrentDb.Execute method

E

Ed

I experience very strange problem in Access 2003.
If I try to run quite a simple query from CurrentDb.Execute command it will
crash my database. From another side it will run perfectly OK if I would
create a Query1 from database window and run it from there. I am pretty sure
this is a BUG in MS Access. I tried run this query on multiple computers with
MS Access 2003 and MS Access 2002 installed and all of them repeated same
database crash behaviour. I tried to call MS support to get technical support
but they want $390.00 just to start troubleshot this issue. Are they gone
crazy? I do not want to risk this huge amount of money and my time just to
prove them wrong. From another side it is a small chance that I do something
wrong and if so I would like to know that exactly is incorrect in this query.
I would greatly appreciate anyone help in this issue.

Next is details to recreated my mdb tables and queries. Also I can email mdb
file itself ~250Kb in size.

The query SQL string as following:

INSERT INTO [Trade Packages] ( PackageID, TradeID, Description )
SELECT [Package Pad Trade List].PackageID, [Package Pad Trade List].TradeID,
[Package Pad Trade List].Description
FROM [Package Pad Trade List] LEFT JOIN [Trade Packages] ON ([Package Pad
Trade List].PackageID = [Trade Packages].PackageID) AND ([Package Pad Trade
List].TradeID = [Trade Packages].TradeID)
WHERE ((([Trade Packages].PackageID) Is Null) AND (([Package Pad Trade
List].IndustryID)=5));


The [Package Pad Trade List] sub query has following SQL string:

SELECT 1 AS PackageID, Trades.IndustryID, Trades.GroupID, Trades.ActivityID,
Trades.TradeID, Trades.Description
FROM Trades;


The tables’ fields and Indexes as following:

Table: Trade Packages
Columns
Name Type Size
PackageID Long Integer 4
TradeID Long Integer 4
Description Text 255
Table Indexes
Name Number of Fields
PackageID 1
Fields:
PackageID Ascending

TradeID 1
Fields:
TradeID Ascending

PrimaryKey 2
Fields:
PackageID Ascending
TradeID Ascending



Table: Trades

Columns
Name Type Size
IndustryID Long Integer 4
TradeID Long Integer 4
Description Text 255
Table Indexes
Name Number of Fields
IndustryID 1
Fields:
IndustryID Ascending

TradeID 1
Fields:
TradeID Ascending

PrimaryKey 4
Fields:
IndustryID Ascending
TradeID Ascending
 
M

[MVP] S.Clark

I don't know the details for your "crash", but often times, when a database
is corrupt, it will cause odd behavior like Access quitting instantly. And,
if you copy that mdb to another machine and try it, the corruption is still
there, and the behavior appears to be the same on the other machine.

If you haven't already, compact and repair the mdb. Decompile and
recompile, and if all else fails, Import all objects to a new mdb.

Finally, ensure that all users have their own copy of the .mdb, on their
local machine, linked to a data mdb on the network.
--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Ed said:
I experience very strange problem in Access 2003.
If I try to run quite a simple query from CurrentDb.Execute command it
will
crash my database. From another side it will run perfectly OK if I would
create a Query1 from database window and run it from there. I am pretty
sure
this is a BUG in MS Access. I tried run this query on multiple computers
with
MS Access 2003 and MS Access 2002 installed and all of them repeated same
database crash behaviour. I tried to call MS support to get technical
support
but they want $390.00 just to start troubleshot this issue. Are they gone
crazy? I do not want to risk this huge amount of money and my time just to
prove them wrong. From another side it is a small chance that I do
something
wrong and if so I would like to know that exactly is incorrect in this
query.
I would greatly appreciate anyone help in this issue.

Next is details to recreated my mdb tables and queries. Also I can email
mdb
file itself ~250Kb in size.

The query SQL string as following:

INSERT INTO [Trade Packages] ( PackageID, TradeID, Description )
SELECT [Package Pad Trade List].PackageID, [Package Pad Trade
List].TradeID,
[Package Pad Trade List].Description
FROM [Package Pad Trade List] LEFT JOIN [Trade Packages] ON ([Package Pad
Trade List].PackageID = [Trade Packages].PackageID) AND ([Package Pad
Trade
List].TradeID = [Trade Packages].TradeID)
WHERE ((([Trade Packages].PackageID) Is Null) AND (([Package Pad Trade
List].IndustryID)=5));


The [Package Pad Trade List] sub query has following SQL string:

SELECT 1 AS PackageID, Trades.IndustryID, Trades.GroupID,
Trades.ActivityID,
Trades.TradeID, Trades.Description
FROM Trades;


The tables' fields and Indexes as following:

Table: Trade Packages
Columns
Name Type Size
PackageID Long Integer 4
TradeID Long Integer 4
Description Text 255
Table Indexes
Name Number of Fields
PackageID 1
Fields:
PackageID Ascending

TradeID 1
Fields:
TradeID Ascending

PrimaryKey 2
Fields:
PackageID Ascending
TradeID Ascending



Table: Trades

Columns
Name Type Size
IndustryID Long Integer 4
TradeID Long Integer 4
Description Text 255
Table Indexes
Name Number of Fields
IndustryID 1
Fields:
IndustryID Ascending

TradeID 1
Fields:
TradeID Ascending

PrimaryKey 4
Fields:
IndustryID Ascending
TradeID Ascending
Click to expand...
 
E

Ed

Thank you for you advice.
I probably should mention it in my original post that I done all this steps
you are mention in your email multiple times before I report this problem to
Microsoft Tech Support and posted it here.
None of compact, import to a new .mdb file works. As I mentioned in my post
I even import only tables and queries involved out of the main .mdb file into
a separate .mdb files (which I can email if required) and tried to run it on
multiple computers with full version and Runtime of Access 2002 and Access
2003 installed. However this problem still keeps reappearing. That why I
think it may be a bug in Microsoft Access 2003.

Also all users have their own copy of the .mdb.

--
Regards,
Ed


[MVP] S.Clark said:
I don't know the details for your "crash", but often times, when a database
is corrupt, it will cause odd behavior like Access quitting instantly. And,
if you copy that mdb to another machine and try it, the corruption is still
there, and the behavior appears to be the same on the other machine.

If you haven't already, compact and repair the mdb. Decompile and
recompile, and if all else fails, Import all objects to a new mdb.

Finally, ensure that all users have their own copy of the .mdb, on their
local machine, linked to a data mdb on the network.
--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Ed said:
I experience very strange problem in Access 2003.
If I try to run quite a simple query from CurrentDb.Execute command it
will
crash my database. From another side it will run perfectly OK if I would
create a Query1 from database window and run it from there. I am pretty
sure
this is a BUG in MS Access. I tried run this query on multiple computers
with
MS Access 2003 and MS Access 2002 installed and all of them repeated same
database crash behaviour. I tried to call MS support to get technical
support
but they want $390.00 just to start troubleshot this issue. Are they gone
crazy? I do not want to risk this huge amount of money and my time just to
prove them wrong. From another side it is a small chance that I do
something
wrong and if so I would like to know that exactly is incorrect in this
query.
I would greatly appreciate anyone help in this issue.

Next is details to recreated my mdb tables and queries. Also I can email
mdb
file itself ~250Kb in size.

The query SQL string as following:

INSERT INTO [Trade Packages] ( PackageID, TradeID, Description )
SELECT [Package Pad Trade List].PackageID, [Package Pad Trade
List].TradeID,
[Package Pad Trade List].Description
FROM [Package Pad Trade List] LEFT JOIN [Trade Packages] ON ([Package Pad
Trade List].PackageID = [Trade Packages].PackageID) AND ([Package Pad
Trade
List].TradeID = [Trade Packages].TradeID)
WHERE ((([Trade Packages].PackageID) Is Null) AND (([Package Pad Trade
List].IndustryID)=5));


The [Package Pad Trade List] sub query has following SQL string:

SELECT 1 AS PackageID, Trades.IndustryID, Trades.GroupID,
Trades.ActivityID,
Trades.TradeID, Trades.Description
FROM Trades;


The tables' fields and Indexes as following:

Table: Trade Packages
Columns
Name Type Size
PackageID Long Integer 4
TradeID Long Integer 4
Description Text 255
Table Indexes
Name Number of Fields
PackageID 1
Fields:
PackageID Ascending

TradeID 1
Fields:
TradeID Ascending

PrimaryKey 2
Fields:
PackageID Ascending
TradeID Ascending



Table: Trades

Columns
Name Type Size
IndustryID Long Integer 4
TradeID Long Integer 4
Description Text 255
Table Indexes
Name Number of Fields
IndustryID 1
Fields:
IndustryID Ascending

TradeID 1
Fields:
TradeID Ascending

PrimaryKey 4
Fields:
IndustryID Ascending
TradeID Ascending
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query Help 0
Returning Empty Field 0
Querying data from two seperate tables 1
Query loses data due to joins 3
Only one record with yes/no 1
Delete file but still in memory 5
Executing SQL DTS Package from Access 6
If then code for empty combo box column 4

Top