SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab
SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be V
arChar
These transformations were carried out using SQL EnterpriseManager > DesignT
able
The Access Front End worked Fine.
PROBLEM
--
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column Compa
nyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > De
sign Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a t
ext field BUT the server is still telling the access Front end that the data
type is nVarChar [NOT VarChar - as currently reported by SQL Enterprise
Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates t
hem with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they wer
e before.
Jim BuntonJim
Do you have a last Service Pack installed on the SQL Server?
Looks strange. I just did some testing and it works just fine
Try to create a table first and then run DTS to update the table.
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:KWCif.17904$8G
6.12386@.fe1.news.blueyonder.co.uk...
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab
SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be V
arChar
These transformations were carried out using SQL EnterpriseManager > DesignT
able
The Access Front End worked Fine.
PROBLEM
--
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column Compa
nyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > De
sign Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a t
ext field BUT the server is still telling the access Front end that the data
type is nVarChar [NOT VarChar - as currently reported by SQL Enterprise
Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates t
hem with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they wer
e before.
Jim Bunton|||Thanks for the reply Uri
DownLoaded Service Pack 3
Installed - works
DownLoaded Service Pack 3a (sql2kasp3.exe)
Setup.exe > begins to run - stops on ERROR 145 an error occurred in the move
data process
? Help ? !! Have done a reboot. still no joy
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eeBjvvB9FHA.1032@.TK2MS
FTNGP11.phx.gbl...
Jim
Do you have a last Service Pack installed on the SQL Server?
Looks strange. I just did some testing and it works just fine
Try to create a table first and then run DTS to update the table.
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:KWCif.17904$8G
6.12386@.fe1.news.blueyonder.co.uk...
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab
SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be V
arChar
These transformations were carried out using SQL EnterpriseManager > DesignT
able
The Access Front End worked Fine.
PROBLEM
--
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column Compa
nyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > De
sign Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a t
ext field BUT the server is still telling the access Front end that the data
type is nVarChar [NOT VarChar - as currently reported by SQL Enterprise
Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates t
hem with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they wer
e before.
Jim Bunton|||Creating table first then loading data works fine - ca now edit data
[update to SP 4 - to SP 3 Ok but
SP 3a setup.exe > will not instal - stops wth err 145 'an error occurred in
the move data process']
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eeBjvvB9FHA.1032@.TK2MS
FTNGP11.phx.gbl...
Jim
Do you have a last Service Pack installed on the SQL Server?
Looks strange. I just did some testing and it works just fine
Try to create a table first and then run DTS to update the table.
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:KWCif.17904$8G
6.12386@.fe1.news.blueyonder.co.uk...
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab
SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be V
arChar
These transformations were carried out using SQL EnterpriseManager > DesignT
able
The Access Front End worked Fine.
PROBLEM
--
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column Compa
nyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > De
sign Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a t
ext field BUT the server is still telling the access Front end that the data
type is nVarChar [NOT VarChar - as currently reported by SQL Enterprise
Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates t
hem with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they wer
e before.
Jim Bunton
