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.

What database ? Is this a read-only query ?

Tim

"DIwama" wrote in message
...
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.

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" wrote:

What database ? Is this a read-only query ?

Tim

"DIwama" wrote in message
...
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.

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

On Jul 1, 12:26*pm, DIwama wrote:
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" wrote:
What database ? *Is this a read-only query ?

Tim

"DIwama" wrote in message
...
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.- Hide quoted text -

- Show quoted text -

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" wrote:

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

On Jul 1, 12:26 pm, DIwama wrote:
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" wrote:
What database ? Is this a read-only query ?

Tim

"DIwama" wrote in message
...
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.- Hide quoted text -

- Show quoted text -

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


On Jul 2, 7:06*am, DIwama wrote:
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" wrote:
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

On Jul 1, 12:26 pm, DIwama wrote:
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" wrote:
What database ? *Is this a read-only query ?

Tim

"DIwama" wrote in message
...
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.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -