Altering linked tables

Greetings,
I am using an Access mdb with all linked tables in MSDE. I know that you
can't change linked tables from the MDB, so I created an ADP that imports
all the tables, so that I can alter them in the ADP. The ADP let's me add a
column to a table, but when I open the mdb up again that column isn't there.
What do I do?
Thanks in advance.
Hi, Yair.
Drop the link, then recreate the link to the table. An external table's
structure and connection properties are only recorded at the time of
linking, so any later changes that you make to a linked table's structure
(i.e., add/change/delete/rename fields) or connection properties (i.e.,
add/change/delete database password) are unknown. Dropping and recreating
the link will re-establish the correct properties needed to access the data
in the external table.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
"Yair Sageev" <geekyheeb-news@.yahoo.com> wrote in message
news:excWwvHhEHA.3024@.TK2MSFTNGP10.phx.gbl...
> Greetings,
> I am using an Access mdb with all linked tables in MSDE. I know that you
> can't change linked tables from the MDB, so I created an ADP that imports
> all the tables, so that I can alter them in the ADP. The ADP let's me add
a
> column to a table, but when I open the mdb up again that column isn't
there.
> What do I do?
> Thanks in advance.
>
|||Thanks Gunny.
How do I drop the link and relink?
Will it affect all my forms and reports?
I'll check the help too but if it's an easy answer...
"'69 Camaro" <Black_hole.To.69Camaro@.Spameater.org> wrote in message
news:%23dk$36HhEHA.2052@.tk2msftngp13.phx.gbl...
> Hi, Yair.
> Drop the link, then recreate the link to the table. An external table's
> structure and connection properties are only recorded at the time of
> linking, so any later changes that you make to a linked table's structure
> (i.e., add/change/delete/rename fields) or connection properties (i.e.,
> add/change/delete database password) are unknown. Dropping and recreating
> the link will re-establish the correct properties needed to access the
data[vbcol=seagreen]
> in the external table.
> HTH.
> Gunny
> See http://www.QBuilt.com for all your database needs.
> See http://www.Access.QBuilt.com for Microsoft Access tips.
>
> "Yair Sageev" <geekyheeb-news@.yahoo.com> wrote in message
> news:excWwvHhEHA.3024@.TK2MSFTNGP10.phx.gbl...
you[vbcol=seagreen]
imports[vbcol=seagreen]
add
> a
> there.
>
|||Thanks. I used the linked table manager to refresh the tables an it worked
perfectly.
"'69 Camaro" <Black_hole.To.69Camaro@.Spameater.org> wrote in message
news:%23dk$36HhEHA.2052@.tk2msftngp13.phx.gbl...
> Hi, Yair.
> Drop the link, then recreate the link to the table. An external table's
> structure and connection properties are only recorded at the time of
> linking, so any later changes that you make to a linked table's structure
> (i.e., add/change/delete/rename fields) or connection properties (i.e.,
> add/change/delete database password) are unknown. Dropping and recreating
> the link will re-establish the correct properties needed to access the
data[vbcol=seagreen]
> in the external table.
> HTH.
> Gunny
> See http://www.QBuilt.com for all your database needs.
> See http://www.Access.QBuilt.com for Microsoft Access tips.
>
> "Yair Sageev" <geekyheeb-news@.yahoo.com> wrote in message
> news:excWwvHhEHA.3024@.TK2MSFTNGP10.phx.gbl...
you[vbcol=seagreen]
imports[vbcol=seagreen]
add
> a
> there.
>
|||Check out http://www.mvps.org/access/tables/tbl0010.htm at "The Access Web"
for one approach to relinking ODBC tables, or see
http://members.rogers.com/douglas.j...LessLinks.html for how to do
it without requiring a DSN.
Assuming you do it when you first start up the application, it won't affect
your forms or reports unless table changes have occurred, and your forms or
reports reference fields or tables that are no longer present.
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)
"Yair Sageev" <geekyheeb-news@.yahoo.com> wrote in message
news:ep3Ss#HhEHA.3476@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Thanks Gunny.
> How do I drop the link and relink?
> Will it affect all my forms and reports?
> I'll check the help too but if it's an easy answer...
>
> "'69 Camaro" <Black_hole.To.69Camaro@.Spameater.org> wrote in message
> news:%23dk$36HhEHA.2052@.tk2msftngp13.phx.gbl...
structure[vbcol=seagreen]
recreating
> data
> you
> imports
> add
>
|||Hi, Yair.

> How do I drop the link and relink?
Select the linked table in the database window with your mouse and hit the
<DELETE> key. Then use the menu "File -> Get External Data -> Link Tables"
and browse for the file that contains the table that you want to link to,
then follow the prompts in the dialog window just like you did when you
originally linked the table.

> Will it affect all my forms and reports?
Sort of. It will allow you to add this new field to all of the forms and
reports bound to this table, and any queries and Recordsets that utilize the
table, but won't automatically make these changes for you.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
"Yair Sageev" <geekyheeb-news@.yahoo.com> wrote in message
news:ep3Ss%23HhEHA.3476@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Thanks Gunny.
> How do I drop the link and relink?
> Will it affect all my forms and reports?
> I'll check the help too but if it's an easy answer...
>
> "'69 Camaro" <Black_hole.To.69Camaro@.Spameater.org> wrote in message
> news:%23dk$36HhEHA.2052@.tk2msftngp13.phx.gbl...
structure[vbcol=seagreen]
recreating
> data
> you
> imports
> add
>

Alter table problem

Greetings !
I can not believe that no one had this problem.
I have tested on Windows 2000 Advanced Server, and Windows 2003 Server
(Standard Edition)
If you install Hotfix KB 810185, you will not be able to change design of
your tables on your databases.
After reinstalation of SQL Server EE and applying Service Pack 3a, and
without applying Hotfix this problem disappear.
Regards Gj.What exactly is not working after applying that hotfix? Can you post the
commands that are failing? Any error messages? Or are you trying to alter
tables from Enterprise Manager and getting errors?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Dariviko" <dariviko@.hotmail.com> wrote in message
news:c0ttp0$506$1@.sunce.iskon.hr...
Greetings !
I can not believe that no one had this problem.
I have tested on Windows 2000 Advanced Server, and Windows 2003 Server
(Standard Edition)
If you install Hotfix KB 810185, you will not be able to change design of
your tables on your databases.
After reinstalation of SQL Server EE and applying Service Pack 3a, and
without applying Hotfix this problem disappear.
Regards Gj.|||I can not find that KB article on MS website

Alter table problem

Greetings !
I can not believe that no one had this problem.
I have tested on Windows 2000 Advanced Server, and Windows 2003 Server
(Standard Edition)
If you install Hotfix KB 810185, you will not be able to change design of
your tables on your databases.
After reinstalation of SQL Server EE and applying Service Pack 3a, and
without applying Hotfix this problem disappear.
Regards Gj.What exactly is not working after applying that hotfix? Can you post the
commands that are failing? Any error messages? Or are you trying to alter
tables from Enterprise Manager and getting errors?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Dariviko" <dariviko@.hotmail.com> wrote in message
news:c0ttp0$506$1@.sunce.iskon.hr...
Greetings !
I can not believe that no one had this problem.
I have tested on Windows 2000 Advanced Server, and Windows 2003 Server
(Standard Edition)
If you install Hotfix KB 810185, you will not be able to change design of
your tables on your databases.
After reinstalation of SQL Server EE and applying Service Pack 3a, and
without applying Hotfix this problem disappear.
Regards Gj.|||I can not find that KB article on MS website

Alter table does not work on SQL Server 2000 with SP3a and Hotfix KB810185

Greetings !
I am having problem with ALTER TABLE on any table when I install Hotfix
(KB810185-8.00.0859-ENU) required for SQL Server Reporting services.
I have installed SQL Server 2000 Enterprise Edition with SP3a on Windows
2003 Server (Standard Edition)
Hardware characteristics: Intel Pentum IV 2.40 GHz, 2 GB RAM, 5 disks etc.
We have tested alter table on two different computers and problem is the
same.
We have tried in Enterprise Manager and Query Analyser.
In Query Analyser sometimes is possible, but not all the time.
Without Hotfix (KB810185) everthing works fine.
Please help.
Is it possible to uninstall this hotfix?
It is not visible in ADD/REMOVE Programs ?
Regards Gjuro.What is the exact ALTER TABLE statement that is failing, and how do you
know it fails? Do you get an error, does it simply not change the
table, or something else?
SK
Dariviko wrote:
>Greetings !
>I am having problem with ALTER TABLE on any table when I install Hotfix
>(KB810185-8.00.0859-ENU) required for SQL Server Reporting services.
>I have installed SQL Server 2000 Enterprise Edition with SP3a on Windows
>2003 Server (Standard Edition)
>Hardware characteristics: Intel Pentum IV 2.40 GHz, 2 GB RAM, 5 disks etc.
>We have tested alter table on two different computers and problem is the
>same.
>We have tried in Enterprise Manager and Query Analyser.
>In Query Analyser sometimes is possible, but not all the time.
>Without Hotfix (KB810185) everthing works fine.
>
>Please help.
>Is it possible to uninstall this hotfix?
> It is not visible in ADD/REMOVE Programs ?
>Regards Gjuro.
>
>|||---
Test table creation: (Script from Enterprise Manager - New table)
----
/*
17. veljaca 2004 16:49:35
User:
Server: MSSERVER
Database: MFIN_TEST
Application: MS SQLEM - Data Tools
*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Table_TEST
(
id int NOT NULL,
col1 char(10) NULL,
col2 char(10) NULL
) ON [PRIMARY]
GO
COMMIT
--
Table is successfuly created.
When I move ALLOW NULLS from col2 in Enterprise Manager, I receive error in
Enterprise Manager.
/*
17. veljaca 2004 16:51:40
User:
Server: MSSERVER
Database: MFIN_TEST
Application: MS SQLEM - Data Tools
*/
---
ALTER TABLE in Enterprise Manager
---
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_TABLE_TEST
(
id int NOT NULL,
col1 char(10) NULL,
col2 char(10) NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.TABLE_TEST)
EXEC('INSERT INTO dbo.Tmp_TABLE_TEST (id, col1, col2)
SELECT id, col1, col2 FROM dbo.TABLE_TEST TABLOCKX')
GO
DROP TABLE dbo.TABLE_TEST
GO
EXECUTE sp_rename N'dbo.Tmp_TABLE_TEST', N'TABLE_TEST', 'OBJECT'
GO
COMMIT
--
This is an error I receive:
--
/*
17. veljaca 2004 16:52:07
User:
Server: MSSERVER
Database: MFIN_TEST
Application: MS SQLEM - Data Tools
*/
'TABLE_TEST' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid cursor state
Table TABLE_TEST is empty. New table
Windows 2003 Server has all updates.
I tried to remove hotfix KB810185 as it is described on Microsoft site, but
it does not work.
Regards
Gjuro MCDBA
"Steve Kass" <skass@.drew.edu> wrote in message
news:uS5Yl8V9DHA.2472@.TK2MSFTNGP10.phx.gbl...
> What is the exact ALTER TABLE statement that is failing, and how do you
> know it fails? Do you get an error, does it simply not change the
> table, or something else?
> SK
> Dariviko wrote:
> >Greetings !
> >
> >I am having problem with ALTER TABLE on any table when I install Hotfix
> >(KB810185-8.00.0859-ENU) required for SQL Server Reporting services.
> >
> >I have installed SQL Server 2000 Enterprise Edition with SP3a on Windows
> >2003 Server (Standard Edition)
> >Hardware characteristics: Intel Pentum IV 2.40 GHz, 2 GB RAM, 5 disks
etc.
> >
> >We have tested alter table on two different computers and problem is the
> >same.
> >We have tried in Enterprise Manager and Query Analyser.
> >In Query Analyser sometimes is possible, but not all the time.
> >Without Hotfix (KB810185) everthing works fine.
> >
> >
> >Please help.
> >
> >Is it possible to uninstall this hotfix?
> > It is not visible in ADD/REMOVE Programs ?
> >
> >Regards Gjuro.
> >
> >
> >
> >
>|||I am having the same exact problem except when using Enterprise Manager.
I am trying to modify an existing table, and when I click the Save icon, I
get a message about Invalid Cursor State. This is only AFTER I applied
Hotfix 821334 in order to use SQL Reporting Services. As in Dariviko's case,
I am also running Server 2003 with SQL Server 2000 and SP3a. I installed
Reporting Services yesterday and the 821334 hotfix as a part of that
installation.
Does anybody else get this error?
Thanks,
Michael Carr
"Dariviko" <dariviko@.hotmail.com> wrote in message
news:c0te4m$pq4$1@.sunce.iskon.hr...
> --
> This is an error I receive:
> --
> /*
> 17. veljaca 2004 16:52:07
> User:
> Server: MSSERVER
> Database: MFIN_TEST
> Application: MS SQLEM - Data Tools
> */
> 'TABLE_TEST' table
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid cursor state
>
> Table TABLE_TEST is empty. New table
> Windows 2003 Server has all updates.
> I tried to remove hotfix KB810185 as it is described on Microsoft site,
but
> it does not work.
> Regards
> Gjuro MCDBA
>
>
>
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:uS5Yl8V9DHA.2472@.TK2MSFTNGP10.phx.gbl...
> > What is the exact ALTER TABLE statement that is failing, and how do you
> > know it fails? Do you get an error, does it simply not change the
> > table, or something else?
> >
> > SK
> >
> > Dariviko wrote:
> >
> > >Greetings !
> > >
> > >I am having problem with ALTER TABLE on any table when I install Hotfix
> > >(KB810185-8.00.0859-ENU) required for SQL Server Reporting services.
> > >
> > >I have installed SQL Server 2000 Enterprise Edition with SP3a on
Windows
> > >2003 Server (Standard Edition)
> > >Hardware characteristics: Intel Pentum IV 2.40 GHz, 2 GB RAM, 5 disks
> etc.
> > >
> > >We have tested alter table on two different computers and problem is
the
> > >same.
> > >We have tried in Enterprise Manager and Query Analyser.
> > >In Query Analyser sometimes is possible, but not all the time.
> > >Without Hotfix (KB810185) everthing works fine.
> > >
> > >
> > >Please help.
> > >
> > >Is it possible to uninstall this hotfix?
> > > It is not visible in ADD/REMOVE Programs ?
> > >
> > >Regards Gjuro.
> > >
> > >
> > >
> > >
> >
>|||I have them same problem. See http://www.aspfaq.com/show.asp?id=2515. Now we just need a Microsoft tech to tell us where we can download hotfix 876 or greater.|||You call Microsoft Product Support Services, just like the KB articles say.
(You can find individual hotfixes at http://www.aspfaq.com/2160)
You won't be able to find hotfixes available at tucows or download.com.
They keep them from public consumption for a reason...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"David Hubbell" <David.Hubbell@.sierras.com> wrote in message
news:D52EBDF9-A0DC-4723-A123-D1FE4B47747D@.microsoft.com...
>I have them same problem. See http://www.aspfaq.com/show.asp?id=2515. Now
>we just need a Microsoft tech to tell us where we can download hotfix 876
>or greater.

Alter table does not work on SQL Server 2000 with SP3a and Hotfix KB810185

Greetings !
I am having problem with ALTER TABLE on any table when I install Hotfix
(KB810185-8.00.0859-ENU) required for SQL Server Reporting services.
I have installed SQL Server 2000 Enterprise Edition with SP3a on Windows
2003 Server (Standard Edition)
Hardware characteristics: Intel Pentum IV 2.40 GHz, 2 GB RAM, 5 disks etc.
We have tested alter table on two different computers and problem is the
same.
We have tried in Enterprise Manager and Query Analyser.
In Query Analyser sometimes is possible, but not all the time.
Without Hotfix (KB810185) everthing works fine.
Please help.
Is it possible to uninstall this hotfix?
It is not visible in ADD/REMOVE Programs ?
Regards Gjuro.---
Test table creation: (Script from Enterprise Manager - New table)
----
/*
17. veljaca 2004 16:49:35
User:
Server: MSSERVER
Database: MFIN_TEST
Application: MS SQLEM - Data Tools
*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Table_TEST
(
id int NOT NULL,
col1 char(10) NULL,
col2 char(10) NULL
) ON [PRIMARY]
GO
COMMIT
Table is successfuly created.
When I move ALLOW NULLS from col2 in Enterprise Manager, I receive error in
Enterprise Manager.
/*
17. veljaca 2004 16:51:40
User:
Server: MSSERVER
Database: MFIN_TEST
Application: MS SQLEM - Data Tools
*/
---
ALTER TABLE in Enterprise Manager
---
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_TABLE_TEST
(
id int NOT NULL,
col1 char(10) NULL,
col2 char(10) NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.TABLE_TEST)
EXEC('INSERT INTO dbo.Tmp_TABLE_TEST (id, col1, col2)
SELECT id, col1, col2 FROM dbo.TABLE_TEST TABLOCKX')
GO
DROP TABLE dbo.TABLE_TEST
GO
EXECUTE sp_rename N'dbo.Tmp_TABLE_TEST', N'TABLE_TEST', 'OBJECT'
GO
COMMIT
--
This is an error I receive:
--
/*
17. veljaca 2004 16:52:07
User:
Server: MSSERVER
Database: MFIN_TEST
Application: MS SQLEM - Data Tools
*/
'TABLE_TEST' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid cursor state
Table TABLE_TEST is empty. New table
Windows 2003 Server has all updates.
I tried to remove hotfix KB810185 as it is described on Microsoft site, but
it does not work.
Regards
Gjuro MCDBA
"Steve Kass" <skass@.drew.edu> wrote in message
news:uS5Yl8V9DHA.2472@.TK2MSFTNGP10.phx.gbl...
> What is the exact ALTER TABLE statement that is failing, and how do you
> know it fails? Do you get an error, does it simply not change the
> table, or something else?
> SK
> Dariviko wrote:
>
etc.
>|||I am having the same exact problem except when using Enterprise Manager.
I am trying to modify an existing table, and when I click the Save icon, I
get a message about Invalid Cursor State. This is only AFTER I applied
Hotfix 821334 in order to use SQL Reporting Services. As in Dariviko's case,
I am also running Server 2003 with SQL Server 2000 and SP3a. I installed
Reporting Services yesterday and the 821334 hotfix as a part of that
installation.
Does anybody else get this error?
Thanks,
Michael Carr
"Dariviko" <dariviko@.hotmail.com> wrote in message
news:c0te4m$pq4$1@.sunce.iskon.hr...
> --
> This is an error I receive:
> --
> /*
> 17. veljaca 2004 16:52:07
> User:
> Server: MSSERVER
> Database: MFIN_TEST
> Application: MS SQLEM - Data Tools
> */
> 'TABLE_TEST' table
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid cursor stat
e
>
> Table TABLE_TEST is empty. New table
> Windows 2003 Server has all updates.
> I tried to remove hotfix KB810185 as it is described on Microsoft site,
but
> it does not work.
> Regards
> Gjuro MCDBA
>
>
>
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:uS5Yl8V9DHA.2472@.TK2MSFTNGP10.phx.gbl...
Windows
> etc.
the
>|||I have them same problem. See http://www.aspfaq.com/show.asp?id=2515. Now we
just need a Microsoft tech to tell us where we can download hotfix 876 or g
reater.|||You call Microsoft Product Support Services, just like the KB articles say.
(You can find individual hotfixes at http://www.aspfaq.com/2160)
You won't be able to find hotfixes available at tucows or download.com.
They keep them from public consumption for a reason...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"David Hubbell" <David.Hubbell@.sierras.com> wrote in message
news:D52EBDF9-A0DC-4723-A123-D1FE4B47747D@.microsoft.com...
>I have them same problem. See http://www.aspfaq.com/show.asp?id=2515. Now
>we just need a Microsoft tech to tell us where we can download hotfix 876
>or greater.sql