Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-02-19 : 09:26:14
|
| Jim Hill writes "I have a Access 2000 db that is at 20meg. It has to be repaired each day. I am trying to find the easiest way to port the data into SQL 2000. I used the EXPORT feature in ACCESS and got the data into SQL, but it lost all of my realationships and it will not let me rebuild them in ACCESS. Then, I tried IMPORTING the data from the SQL side and still had problems. I checked out the Internet and it sounds like there was/is a tool to upsize an Access DB to SQL 7.0, but I found nothing about SQL 2000.Anyone have an idea what is the best way to accomplish this task?I have no problem with using ACCESS 2000 as the front end, I just do not want it to be my Database because of its' size limitations.Thanks,Jim Hill" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-19 : 09:37:06
|
| In Access 2000, under the Tools:Database Utilities menu, is the Upsizing Wizard/Upsize to SQL Server option. This will preserve all your relationships/foreign keys. I've used this successfully to upsize to both SQL 7.0 and 2000. |
 |
|
|
jh080157
Starting Member
2 Posts |
Posted - 2002-02-19 : 10:25:44
|
| Since I wrote this, I found the upsizing tool and tried it, but it still did not save the relationships. Any ideas?? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-19 : 11:37:38
|
| If you upsized ALL of the tables at the same time, and the relationships are defined in Access, then it will include them in the upsize. These have to be relationships that Enfore Integrity between tables (foreign keys), otherwise there's nothing for SQL Server to transfer...simply linking tables together doesn't create a foreign key.BTW, database diagrams don't automatically generate either when you upsize. I don't know if you were checking for them or not, but just in case. |
|
|
|
jh080157
Starting Member
2 Posts |
Posted - 2002-02-19 : 15:14:56
|
| Did exactly what you said, but the relationships still do not seem to come across. Should I expect to see them on the Access side like they were, or on the SQL side?They definitely no longer appear on the Access side. My SQL guy said he checked and that they do not show up on the SQL side either.We originally had done them one at a time, but the last time we did it, we figured that we probably had to do them all at once. We dropped/deleted all of the tables on the SQL side and then did it over.NO LUCK.The data is all there and that is it. The box is checked that says keep relationships..Jim |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-19 : 17:59:28
|
| DROP all the SQL Server tables and try the upsize once more. After the tables are upsized, either generate scripts for them and check the script for a FOREIGN KEY declaration, or check the table Properties tab under Relationships. Again, checking database diagrams won't show anything, you have to try one of the two methods above.If this still doesn't work, you can add them in by hand from the table:properties:relationships tab in Enterprise Manager. |
|
|
|
|
|
|
|
|
Migrating From Access 2000 to SQL 2000