Excel and SQL question

[DIwama]

When you connect Excel to a database using Microsoft Query, what do you need
to do to the SQL code to ensure that the query does not lock the tables that
it is pulling information from?

In other words, how can you continue to allow additions/deletions to the
tables, while the query is running?

There are versions of SQL where you can put WITH (NOLOCK) after the FROM,
but this gives me an error in Microsoft Query.

Thank you for any help you can give me.

 

[Tim Williams]

What database ? Is this a read-only query ?

Tim

 

[DIwama]

Hello Tim,

The database is ODBC, and the query is only used to read information from
the database. However, there are other people accessing this same database,
and they need to be able to add data while my query is running.

What do I need to do with the SQL in Microsoft Query to ensure that my query
does not lock the tables it connects to?

Thank you,
DIwama

 

[Tim Williams]

ODBC is a database access layer, not a database.
Access, Oracle, SQLServer ? It might matter, depending on your exact
requirements.

However, in general if your query is only "select" then you shouldn't
need to take any special steps to allow others to update the same
tables.

Tim

 

[DIwama]

Thanks, Tim.

I mis-typed, ODBC is, of course, the way I access the database. It is a SQL
Server database.

In Microsoft Query, my query did not appear to cause any problems. However,
there were some data entry problems experienced by other users once I
connected the query to Excel. IT was able to identify that tables were
getting locked out during the timeframe that I was running the query from
Excel. Although they did not do a full diagnostics to show that it was
definitely my query, the problems did seem to start and stop with around the
same times.


Here is the SQL:

SELECT orders.invoice, order_item.edi_date, order_item.ship_date,
ProductList.ProductSubcat, order_item.tracking_no, order_item.mfg_id

FROM company.dbo.order_comment order_comment, company.dbo.order_item
order_item, company.dbo.orders orders, company.dbo.ProductList ProductList

WHERE order_item.orientation = ProductList.orientation AND
order_item.material_id = ProductList.material_id AND order_item.slot_size =
_ProductList.slot_size AND orders.invoice = order_item.invoice AND
order_item.invoice = order_comment.invoice AND ((order_item.ship_date=?) AND
(order_comment.comment Like '%change%'))


Thanks again, Tim!

 

[Tim Williams]

I don't have direct experience with SQL Server but it's hard to
imagine an "enterprise" DBMS in which a simple select query would lock
the source tables/records. You should follow up with your DBA on this
question.

How are you running the query from excel: as a querytable or using
ADO ?
If using ADO, make sure you use the correct cursor type/lock type.

Tim