Access Closes Itself During SQL Execution

[peridian]

I've done some searches on this, but can't seem to hit the right combination of words to find any useful articles.

I've got a database (Access 2000/WinXPPro) with a few tables. I try to perform a SELECT INTO statement, and it starts to run, but then Access appears to close itself with no warning or error.

In fact the MSACCESS process is still running, and the ldb file is still actively open, so the database is then effectively locked until I end the process and delete the lock file (the mdb can be opened before this, but none of the tables or queries will appear in the front end).

This occurs every time I try this, so I decided to build a new database and import all the items. When it gets to running the Import query on the table this query is for, the same thing happens.

What would cause a table to create this kind of behaviour in SQL/Access?

(I'm going to try building the whole database all over again, which thankfully I did all in code so I just need to let it run. Will post again if a rebuild solves the issue.)

Regards,
Rob.

[missinglinq]

To say that SQL is not my forte is a gross understatement, but perhaps the problem is caused by the statement Select Into.

There is a Select statement, and an Insert Into statement, but I don't believe that there is a Select Into statement!

As Randy Newman said “I could be wrong now, but I don’t think so!”

Linq ;0)>

[barry07]

Linq is correct - the syntax for inserting selected fields into another table is

INSERT INTO desttable(field1,field2...) SELECT oldfield1, oldfield2... from sourcetable [WHERE ...]

The WHERE clause is optional. Note that the field names don't have to be the same in the source and destination tables but you must select them in the same sequence.


Hope this helps

Barry

[Rabbit]

There is a SELECT Field List INTO TableName FROM Source.
As for what's crashing your program, I have no idea. Are you using any user-defined functions? Variables?

Maybe Access itself is corrupted. Try it on a different computer.

[peridian]

Sorry, wasn't clear, when I said SELECT INTO, I did mean a SELECT fields INTO table type query.

I have already tried this on two other computers and get the same result.

The query which runs first does have the VBA DateAdd function in, and then the subsequent SELECT INTO that works off the table created by the first query is the one with the problem.

The interesting thing was that the first query when run produced zero records in the newly created table.

I have now done a rebuild of the database in a new file, and the queries run fine without incident. I can only assume the database file got corrupted somehow, and either caused the problem directly, or caused the first query to fail to produce records which in turn caused the second query to fail completely.

Thanks for the suggestions though. I think I'll chalk this one up to the marvel that is MS Access.

Regards,
Rob.