UPdate SQL Table Access 2007

[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

 

[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.

 

[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

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

 

[Klatuu]

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

 

[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)

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

 

[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

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)

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

 

[JohnH]

Thanks Dave I will look at it.

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

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)

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