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 |
|
Lin100
Yak Posting Veteran
70 Posts |
 Posted - 2006-09-12 : 17:03:41
|
| Access 2000 and Access 2003 and SQL 2000 ServerI have a database names Books.MDB extension and I wanted to create a Books_SQL_ADP.ADP (project file) so that Access is a front-end and the back-end willbe on the SQL 2000 Server.Tools --> Database Utilities --> Upsizing Wizard.''''''''''''''''''''''''''''''''''''''''''''''''''''''''What server would you like to use for this database?: WIN-2000-SERVERLogin ID: saPassword: password (It was set is Enterprise Manager)Named: Books_SQL_ADP''''''''''''''''''''''''''''''''''''''''''''''''''''''''1) On a PC that runs Windows 2000 Pro (Service Pack 4), and Access 2000 (9.0.2720), it gives me an error - ERROR: OVERFLOW.''''''''''''''''''''''''''''''''''''''''''''''''''''''''2) On a PC that runs Windows 2000 Server (Service Pack 4), and Access 2002 (10.2627.3501) SP-1,during creation of Books_SQL_ADP.ADP, it crashes while converting to Triggers, even though I did not chose this option. |
|
|
Lin100
Yak Posting Veteran
70 Posts |
Posted - 2006-09-12 : 17:08:57
|
| Oops. A typos. I said "Access 2000 and Access 2003 and SQL 2000 Server"It should be "Access 2000 and Access 2002 and SQL 2000 Server" |
 |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-09-12 : 17:50:17
|
| The old adage applies here - if you want something done properly, do it yourself....The Upsizing wizard can be a pain in the arse (or ass). It's more work, but you will get better results by manually creating the tables yourself in SQL Server then creating an ADP file from it. You can then re-import all your forms etc afterwards. |
|
|
|
Lin100
Yak Posting Veteran
70 Posts |
Posted - 2006-09-12 : 23:29:52
|
| Hi Timmy. 1) I just created an ADP file manually, all of the tables in the SQL 2000 Server are seenin the Access database window. However, when I opened some of the table in the Access side and try to edit the fields, it said "This recordset is not updatable". Other table can be updated, while other cannot.2) Also, when I am in this same ADP Access file I did an importFile --> Get External Data --> Import I saw all of the TAB, except that the queries TAB is not there, so I could not import the queries.Any idea how to solve these two problems ? |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-09-13 : 01:03:46
|
| 1. You'll need to define either a primary key or a unique index on your tables before Access will let you update the data via the GUI (i.e. the app needs to know how it will create the UPDATE statements behind-the-scenes).2. That's a catch with ADP's. The queries won't import unless you run the upsizing wizard. But this is a blessing in disguise - SQL can't understand some of the functions available in traditional Access queries. The wizard will try and convert them, but if they are complex it will give up. It's a good opportunity to review your queries and re-write them either as views or stored procedures (or UDF's for that matter).There is a fair amount of work involved to properly upsize an MDB to an ADP, but it is worthwhile....HTH,Tim |
|
|
|
|
|
|
|
|
Cannot create an .ADP (Project file) Access file