SQL Server Problem

[LadyDi]

I have recently "upsized" my database to an SQL Server because it was reaching the 2 GB limit. I was hoping that the move would speed up the database and prevent it from crashing because of it's size. The "upsizing" went fine, and I was able to run my queries afterwards. However, when I try to run a delete query or an update query to modify the data in one of the tables on the server, it runs forever. I tried to run a delete query yesterday to clear a temp table and prepare it for updated data. Before I "upsized" my database, that query would run in a matter of minutes. Yesterday, it ran all day long and still did not delete all records (I'm not exaggerating either - I started the delete query around lunch and at 7:00 pm, it still wasn't complete). Can I not add and delete data in a table housed on an SQL Server? What do you recommend?

Any assistance you can provide would be greatly appreciated.

 

[billmeye]

Are you running the query through Access? If so, you should change it to run as a pass through query which does all the work on the server side instead of within Access.

 

[LadyDi]

That worked great for the delete queries. Thank you very much for the advice.

I'm having trouble getting it to work for the Append queries. When I try to run this query, I get a message that the ODBC connection failed because it didn't recognize the '*'. How do I need to re-write this query so that it will work?

INSERT INTO tblEquipmentData
SELECT ptqryEquipmentTableDB2.*
FROM ptqryEquipmentTableDB2;

I want to take the results of a query and append them to the tblEquipmentData table. The fields in the query match the table exactly.

 

[LadyDi]

I changed my append query, so that it listed out all the fields instead of using the *, and I still get an error message. Now it tells me that it can't find my query: "ODBC--call failed. [Microsoft][SQL Server Native Clinet 10.3][SQL Server]Invalid object name 'ptqryEquipmentTableDB2'.(#208)". The "ptqryEquipmentTableDB2" query is housed in my Access database, not on the SQL Server. Is that my problem? Also, this is another pass through query. I'm pulling information from one server and trying to move it to another server. Is that a problem?