| Author |
 Topic  |
|
|
intranetwise
Starting Member
USA
9 Posts |
 Posted - 07 November 2006 : 18:01:54
|
I am starting to host an old forum that I did not create and need to use MS Access with it. The problem is that the forum had been converted to SQL Server 2000. They sent me a backup file which I recreated the SQL Server database with and then imported the tables into Access. The problem I have, you guessed it, are with the Autonumbers--they are imported as a Number field because there is already data in that field. How do I get around this or is there a better way to convert it back to Access?
THANKS! |
|
|
Podge
Support Moderator
Ireland
3775 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 08 November 2006 : 07:08:11
|
| You should make sure that the data is transferred maintaing the identity of the existing records in SQL Server. You will need to have enable identity insert while transferring the data from SQL Server to Access. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
 |
|
|
intranetwise
Starting Member
USA
9 Posts |
Posted - 10 November 2006 : 13:29:35
|
First, thanks to both of you for the advice and giving it so quickly.
I should have mentioned that I restored the database from the SQL Server 2000 backup using SQL Server 2005 Express which does not have DTS. I am running Windows XP Pro and that was the only version I had installed.
Following your advice, I installed SQL Server 2000 Personal Edition (having Enterprise but not a server running an OS for it anymore) and restored the original backup. I can export it enabling the identity insert now BUT when I imported the backup with Personal it did not retain the autonumbers like Express did--UGH :).
Any ideas on why this is or another way to handle this short of setting up another server with Windows 2000 Server and install Enterprise just to export this small database?
Thanks again! |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 10 November 2006 : 14:02:20
|
I think I lost you sowhere along the way... Where were the autonumbers lost?
Did you do as Podge recommended above, start with a fresh Snitz db, then ran setup.asp to create the tables, and only then do the import? Alsom when using DTS, did you configure it to delete existing table data, instead of dropping and recreating the tables? The latter would lead you to no autonumbers in Access... |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
intranetwise
Starting Member
USA
9 Posts |
Posted - 10 November 2006 : 14:58:33
|
LOL, yes, I am lost too. I have tried this 10 different ways at least. This should be incredibly easy! :) Here are three ways I tried to do it:
First, I restored the backup using 2005 Express, which retained the autonumbers. But, when I linked the tables (in Access) and exported them to a new Access database, they were converted to numbers.
Second, I restored the backup using 2000 Personal, which did not retain the autonumbers from the backup at all. So I could not use DTS.
Third, in despiration, I took the Access database I created that did not contain the autonumbers and attempted to copy the data to the original Snitz Forum Access database (with the autonumbers) using DTS (deleting the existing data and enabling identity insert). This copied all of the tables but 4 and errored out on them (because they were linked it wouldn't just copy the data).
The last one was the closest I got but I do not know how to get the other 4 tables to copy over. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
intranetwise
Starting Member
USA
9 Posts |
Posted - 10 November 2006 : 15:03:36
|
| Forgot to mention, I did try what Podge recommended but I am guessing this version of the forums is so old it does not create the tables automatically??? I also downloaded the new version but the database is different so I am not sure if I could just copy over the existing database and the forum would work? |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
intranetwise
Starting Member
USA
9 Posts |
Posted - 10 November 2006 : 15:20:13
|
| It says Snitz Forums 2000 Version 3.3.05 in the database. There are several new tables in the new version. Do you think it would copy over easily (and work, of course) :)? |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
intranetwise
Starting Member
USA
9 Posts |
Posted - 10 November 2006 : 21:20:57
|
When I try to recreate the tables I get an asp error that says it cannot find input table 'FORUM_CONFIG_NEW'.
With a little rigging, I was able to import the data into the database that comes with the new version of the forums. The forums display and you can drill down into the posts but you cannot login. Is there a script somewhere that would let me copy the data in the old database into the new one and everything work? |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 11 November 2006 : 05:53:04
|
The way the password is handled in 3.4.x is different from 3.3.x, since the 3.4.x has password encryption. You would need an upgrade to get the passwords properly converted...
There is a way to force that, but I can't be sure about it now. I would still think that using a 3.3.x database would be the better option. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
intranetwise
Starting Member
USA
9 Posts |
Posted - 12 November 2006 : 13:46:12
|
Okay, I think I got it figured out. First, I downloaded the new forum and copied it into the old forum with the old database (Access database I created without the autonumbers). Running setup.asp, I converted the database to the new database format. Finally, I ran DTS to copy the newly updated Access database (without the autonumbers) into the blank database that came with the new forum. No errors and it seems to be working. Testing to follow :)
Thanks again for all your help!!! |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
| |
Topic |
|
Topic Locked
.