UPdate SQL Table Access 2007

J

JohnH

I'm trying to update a field in a SQL Table from a field in a linked Excel
file. I'm linking the tables on ID and the select query shows a one-to-one
relationship when I view it. I try to change it to an update query, updating
Name.CATEGORY with NewCat.CATEGORY but I get the following error.

connection is busy with results for another hstmt

This is what the sql looks like. I created the update using the design view

UPDATE dbo_Name INNER JOIN newcat ON dbo_Name.ID = newcat.ID SET
dbo_Name.CATEGORY = [newcat]![CATEGORY];
This is what the Access SQL looks like. I don't think it's doing what I want?

UPDATE dbo_Name INNER JOIN newcat ON dbo_Name.ID = newcat.ID SET
dbo_Name.CATEGORY = [newcat]![CATEGORY];

Any help is much appreciated.
Thanks
John
 
K

Klatuu

You cannot update a linked Excel file. That feature was disabled about 5
years or so ago because of a lawsuit Microsoft lost. Excel files linked as
tables are read only. There are only two ways to modify an Excel file.
Import the file into a table, modify the table, and export the table back to
an excel file.

or

Use automation to manipulate the data directly.
 
J

JohnH

Thanks Klatuu,
I'm trying to update the SQL file not the Excel file. What I did was make a
table fron the excel file so now I have an Access table.
I'm linking the acceess tabel newcat to the sql table Name by id.
Now I want to update the Name.Category field with the content of the
NewCat.Category field.

Hope that makes more sense?

Thanks
John

Klatuu said:
You cannot update a linked Excel file. That feature was disabled about 5
years or so ago because of a lawsuit Microsoft lost. Excel files linked as
tables are read only. There are only two ways to modify an Excel file.
Import the file into a table, modify the table, and export the table back to
an excel file.

or

Use automation to manipulate the data directly.
--
Dave Hargis, Microsoft Access MVP


JohnH said:
I'm trying to update a field in a SQL Table from a field in a linked Excel
file. I'm linking the tables on ID and the select query shows a one-to-one
relationship when I view it. I try to change it to an update query, updating
Name.CATEGORY with NewCat.CATEGORY but I get the following error.

connection is busy with results for another hstmt

This is what the sql looks like. I created the update using the design view

UPDATE dbo_Name INNER JOIN newcat ON dbo_Name.ID = newcat.ID SET
dbo_Name.CATEGORY = [newcat]![CATEGORY];
This is what the Access SQL looks like. I don't think it's doing what I want?

UPDATE dbo_Name INNER JOIN newcat ON dbo_Name.ID = newcat.ID SET
dbo_Name.CATEGORY = [newcat]![CATEGORY];

Any help is much appreciated.
Thanks
John
Click to expand...
Click to expand...
 
K

Klatuu

It looks okay. Is it not doing the update? Are you getting an error?
--
Dave Hargis, Microsoft Access MVP


JohnH said:
Thanks Klatuu,
I'm trying to update the SQL file not the Excel file. What I did was make a
table fron the excel file so now I have an Access table.
I'm linking the acceess tabel newcat to the sql table Name by id.
Now I want to update the Name.Category field with the content of the
NewCat.Category field.

Hope that makes more sense?

Thanks
John

Klatuu said:
You cannot update a linked Excel file. That feature was disabled about 5
years or so ago because of a lawsuit Microsoft lost. Excel files linked as
tables are read only. There are only two ways to modify an Excel file.
Import the file into a table, modify the table, and export the table back to
an excel file.

or

Use automation to manipulate the data directly.
--
Dave Hargis, Microsoft Access MVP


JohnH said:
I'm trying to update a field in a SQL Table from a field in a linked Excel
file. I'm linking the tables on ID and the select query shows a one-to-one
relationship when I view it. I try to change it to an update query, updating
Name.CATEGORY with NewCat.CATEGORY but I get the following error.

connection is busy with results for another hstmt

This is what the sql looks like. I created the update using the design view

UPDATE dbo_Name INNER JOIN newcat ON dbo_Name.ID = newcat.ID SET
dbo_Name.CATEGORY = [newcat]![CATEGORY];
This is what the Access SQL looks like. I don't think it's doing what I want?

UPDATE dbo_Name INNER JOIN newcat ON dbo_Name.ID = newcat.ID SET
dbo_Name.CATEGORY = [newcat]![CATEGORY];

Any help is much appreciated.
Thanks
John
Click to expand...
Click to expand...
Click to expand...
 
J

JohnH

Does not do the update.
I get the following error

ODBC Call failed

[Microsoft][ODBC SQL Server Driver] connection is busy with results for
another hstmt (#0)



Klatuu said:
It looks okay. Is it not doing the update? Are you getting an error?
--
Dave Hargis, Microsoft Access MVP


JohnH said:
Thanks Klatuu,
I'm trying to update the SQL file not the Excel file. What I did was make a
table fron the excel file so now I have an Access table.
I'm linking the acceess tabel newcat to the sql table Name by id.
Now I want to update the Name.Category field with the content of the
NewCat.Category field.

Hope that makes more sense?

Thanks
John

Klatuu said:
You cannot update a linked Excel file. That feature was disabled about 5
years or so ago because of a lawsuit Microsoft lost. Excel files linked as
tables are read only. There are only two ways to modify an Excel file.
Import the file into a table, modify the table, and export the table back to
an excel file.

or

Use automation to manipulate the data directly.
--
Dave Hargis, Microsoft Access MVP


:

I'm trying to update a field in a SQL Table from a field in a linked Excel
file. I'm linking the tables on ID and the select query shows a one-to-one
relationship when I view it. I try to change it to an update query, updating
Name.CATEGORY with NewCat.CATEGORY but I get the following error.

connection is busy with results for another hstmt

This is what the sql looks like. I created the update using the design view

UPDATE dbo_Name INNER JOIN newcat ON dbo_Name.ID = newcat.ID SET
dbo_Name.CATEGORY = [newcat]![CATEGORY];
This is what the Access SQL looks like. I don't think it's doing what I want?

UPDATE dbo_Name INNER JOIN newcat ON dbo_Name.ID = newcat.ID SET
dbo_Name.CATEGORY = [newcat]![CATEGORY];

Any help is much appreciated.
Thanks
John
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

That is not a problem with the query. It is an ODBC connection problem.
I just read about an issue with 2007 and ODBC connections. Go to the Forms
Coding group and read the post "setting connection"

Other than that, I don't know what it is.
--
Dave Hargis, Microsoft Access MVP


JohnH said:
Does not do the update.
I get the following error

ODBC Call failed

[Microsoft][ODBC SQL Server Driver] connection is busy with results for
another hstmt (#0)



Klatuu said:
It looks okay. Is it not doing the update? Are you getting an error?
--
Dave Hargis, Microsoft Access MVP


JohnH said:
Thanks Klatuu,
I'm trying to update the SQL file not the Excel file. What I did was make a
table fron the excel file so now I have an Access table.
I'm linking the acceess tabel newcat to the sql table Name by id.
Now I want to update the Name.Category field with the content of the
NewCat.Category field.

Hope that makes more sense?

Thanks
John

:

You cannot update a linked Excel file. That feature was disabled about 5
years or so ago because of a lawsuit Microsoft lost. Excel files linked as
tables are read only. There are only two ways to modify an Excel file.
Import the file into a table, modify the table, and export the table back to
an excel file.

or

Use automation to manipulate the data directly.
--
Dave Hargis, Microsoft Access MVP


:

I'm trying to update a field in a SQL Table from a field in a linked Excel
file. I'm linking the tables on ID and the select query shows a one-to-one
relationship when I view it. I try to change it to an update query, updating
Name.CATEGORY with NewCat.CATEGORY but I get the following error.

connection is busy with results for another hstmt

This is what the sql looks like. I created the update using the design view

UPDATE dbo_Name INNER JOIN newcat ON dbo_Name.ID = newcat.ID SET
dbo_Name.CATEGORY = [newcat]![CATEGORY];
This is what the Access SQL looks like. I don't think it's doing what I want?

UPDATE dbo_Name INNER JOIN newcat ON dbo_Name.ID = newcat.ID SET
dbo_Name.CATEGORY = [newcat]![CATEGORY];

Any help is much appreciated.
Thanks
John
Click to expand...
Click to expand...
Click to expand...
 
J

JohnH

Thanks Dave I will look at it.

Klatuu said:
That is not a problem with the query. It is an ODBC connection problem.
I just read about an issue with 2007 and ODBC connections. Go to the Forms
Coding group and read the post "setting connection"

Other than that, I don't know what it is.
--
Dave Hargis, Microsoft Access MVP


JohnH said:
Does not do the update.
I get the following error

ODBC Call failed

[Microsoft][ODBC SQL Server Driver] connection is busy with results for
another hstmt (#0)



Klatuu said:
It looks okay. Is it not doing the update? Are you getting an error?
--
Dave Hargis, Microsoft Access MVP


:

Thanks Klatuu,
I'm trying to update the SQL file not the Excel file. What I did was make a
table fron the excel file so now I have an Access table.
I'm linking the acceess tabel newcat to the sql table Name by id.
Now I want to update the Name.Category field with the content of the
NewCat.Category field.

Hope that makes more sense?

Thanks
John

:

You cannot update a linked Excel file. That feature was disabled about 5
years or so ago because of a lawsuit Microsoft lost. Excel files linked as
tables are read only. There are only two ways to modify an Excel file.
Import the file into a table, modify the table, and export the table back to
an excel file.

or

Use automation to manipulate the data directly.
--
Dave Hargis, Microsoft Access MVP


:

I'm trying to update a field in a SQL Table from a field in a linked Excel
file. I'm linking the tables on ID and the select query shows a one-to-one
relationship when I view it. I try to change it to an update query, updating
Name.CATEGORY with NewCat.CATEGORY but I get the following error.

connection is busy with results for another hstmt

This is what the sql looks like. I created the update using the design view

UPDATE dbo_Name INNER JOIN newcat ON dbo_Name.ID = newcat.ID SET
dbo_Name.CATEGORY = [newcat]![CATEGORY];
This is what the Access SQL looks like. I don't think it's doing what I want?

UPDATE dbo_Name INNER JOIN newcat ON dbo_Name.ID = newcat.ID SET
dbo_Name.CATEGORY = [newcat]![CATEGORY];

Any help is much appreciated.
Thanks
John
Click to expand...
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

Update query issue 0
Update Table Field Value with SQL Query 2
Help! Need to update linked SQL table in Acc07 3
Operation must use an updatable query 1
Update Query Type conversion 2
How does one report a bug in Access 2007 SQL? 4
Query gives me a warning when I ru it... 1
Update query wont work? 1

Top