Converting a type into another one

Hello,

I've got two tables. An old one an a new one. Called "tbl_Filme" and "tbl_Filme2". The differents are that the data types are a little bit smaller as in the old one.

So I've got a column wich should contains a datetime. Unfortunattley the datatype is just a normal date and not "smalldatetime" (the "tbl_Filme" was created with MS Access 2003). So I get an error message:

1> INSERT INTO tbl_Filme2 (Titel, Genre, Medium, Anzahl, Qualit?t, Filml?nge)
2> SELECT Titel, Genre, Medium, Anzahl, Qualit?t, Filml?nge
3> FROM tbl_Filme
4> go
Meldung '298', Ebene '16', Status '1', Server 'PREDATOR\SQLEXPRESS', Zeile 1
'The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error.'

My goal is that I can copy the old table in the new table. If I solved that problem maybe you can help me with that problem, too:

I've programmed a programm for accessing the table in VB 2005. I've got theire a GridView where I can change the cells and a button to sync. the changed values with the SQL Database (2005). But finished with the update, the values aren't updated.

I thought this might be an error of my programming but first I tried to use Access for this (created a new Access Project *.adp). But there was an error message "Could update the RecordSet". Is this beacause of the SQL Server 2005 (maby I must wait untill an update, because Access 2003 is older then SQL 2005?)?

Thanks in advance.

OK, first problem. Rather than doing a automatic convertion between the old and the new one, I would check these incompabilities and solve them. Appearantly the datetime are two big / or too small to be reflected in a smalldatetime. So knowing that smalldatetime is defined in the scope of

from January 1, 1900, through June 6, 2079

You should use the query to identify these "old" ones and UPDATE them to a acceptable format for datetime (as above).

Then there should be no more problem with importing them. If you need the dates prior to 1900 or after 2079 you have to use the datetime data type.

Second problem: ""Could update the RecordSet"." This doens′t sound lkike a problem :-). Assuming that the error message is ""Could NOT update the RecordSet".", I would investigate the command that are passed to the Provider. Did you call the UPDATE method ? There has to be an inner exception which should explain the error message a bit more in detail. This would be more helpful to solve your problem.

HTH, jens Suessmeyer.

|||

Hello,

thank you for your answer. The reason why I wanted to choose the datetime is, that I want to write a time value. Is their any data type for only time without a date in it? But I will try your suggestion.

The second problem occured in Microsoft Access. Well, I startet a new project with a new connection to the SQL Server (with the user-ID "sa").

Then I saw all the tables, which were in the database. But I can only read, I cannot change anything. If I try to change a value the error "Couldn't update the RecordSet" occures.

|||

"Is their any data type for only time without a date in it"

-No.

" But I can only read, I cannot change anything"

Create a primary key in the access enviroment on the tables, that should help.

HTH, jens Suessmeyer.

|||

Unfortunattely their is a message (while opening the window where I can edit the data types of the columes) which sais that it is not possible to save the changes because the used SQL Server is newer than the Access version.

Edit:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=196509&SiteID=1

The problem is written their, too. No useable way to solve the problem. Maybe I will wait until the Office 2007.

I have got one question contains the "SQL Server Management Studio". Where do I get this GUI for the SQL 2005. I don't find it.

Edit:

Sorry, found it. :-)

Edit:

Something is strage. After I "played" a little bit with the SQL Server Management Studio" I tried it again with the Microsoft Office Access 2003 and now it works!