Upsing from access XP 1

[jonandyas]

I have just had my back end .mdb database upsized to SQL.

Now I find that the relationships don't seem to work as they used to (ie I don't get the little plus symbol in datasheet view allowing me to expand related records in my access front end).

Can SQL server cope with this set up, or wil I have to recode all my forms/subforms that have related records?

 

[willir]

I personally have not been in the position to upsize. But I am aware of the following article...

http://www.mvps.org/access/tencommandments.htm

http://www.mvps.org/access/lookupfields.htm

One of the "gotchas" is apparently lookup tables.

Although I have not seen documentation on it, the use of DISTINCTROW is also apparently unique to Access. Likewise, crosstab queries, parameter queries, pass-through queries, union queries.

I assume you have read the documentation from Microsoft...

http://support.microsoft.com/default.aspx?kbid=241743&product=acc2000

[blue]
Because SQL Server and MSDE include no Lookup field functionality, the Microsoft Access 2000 Upsizing Tools migrate the values stored in a Lookup field, not the corresponding value associated with the data. [/blue]

There are other considerations too...
[blue]
Modifying your queries can often resolve problems that originally caused them to be skipped by the Upsizing Wizard (or caused their migration to fail). Because you are likely to run the Upsizing Wizard several times before all of your queries have been “tuned” to your satisfaction, it generally is a good idea to choose the Only create table structure; don’t upsize any data option the first few times that you run the wizard. The majority of time that the Upsizing Wizard uses is spent upsizing your data: don’t bother migrating your data until you have all your queries the way that you want them.[/blue]

What I gleamed from relationships...
[blue]
Table Relationships
You can upsize table relationships in one of two ways:
· DRI – Use Declarative Referential Integrity (DRI) in most cases: DRI creates relationships between your tables during the process of table creation itself.
· Triggers – DRI does not support cascading updates and deletes or cross-database referential integrity. If you need to implement either, use triggers instead of DRI.
[/blue]


I am not sure if this documentation explains your issue or not.

Richard

 

[jonandyas]

Many Thanks for the pointers.

I am trying to implement a kind of recipes database (parent table contains name of recipe, child contains 1 or more ingredients). I used to be able to add/amend data using forms/subforms, but now I don't appear to be even allowed to edit the child table directly in datasheet view.

I upsized to SQ to improve transantlantic connection speeds. mdb may have been slow, but at least it worked!

 

[willir]

jonandyas

Databases such as Oracle, Informix and MS*SQL will closely follow ISO standards. (I think 1992 or 1994 is the last -- I don't keep up to date on this.) It takes a long time for these standards to change, and they will be behind in terms of current technology.

Access is geared for the home user and small shops that will not have a justification to use the larger applications. The "big" databases are expensive, and require a high level of expertize for development and support. (I feel this is one of the reasons Unix and Novell lost out to Windows environment.)

Consequently, Access is designed to be more friendly, and a little more liberal with following ISO standards vs taking advantage of newer technology.

The two have a totally different market.

For example,
- One has to manually implement security in Access, and there are some apparent holes. Whereas, security in a "big" database is a major component.
- Access has few diagnostic tools whereas the "big" applicaitons have more tools including remote monitoring.
- Access makes great use of new technology. A lot of the behind the scenes "smarts" that result in the implementation of a relational database with very little knowledge requried by the user.
- Access costs considerably less than the "big" database applications.

Per your comment...
I upsized to SQ to improve transantlantic connection speeds. mdb may have been slow, but at least it worked!

The "big" applications do work, and have proven tecnologies that are used every day by major corporations.

The real issue is the difference between Access the "big" applications -- conversion issues, different functionality, different culture.

From my read of the conversion document from Micrsoft regarding upsizing to SQL is that it is work. Any conversion between platforms will require work.

However, I would not hesitate to recommend the conversion for larger customers who have a need for a large number of transaction where the cost of a serious database is warranted.

I would not recommend such an upgrade to smaller shops where they lack the justification and the resources for support. To me, Access is much more "fun", and will do the job.

Lastly, having walked the road way too many times before, it is tough adjusting to a new product. This is silly? What happend to...? I can't... Etc But in the end, after adapting to the learning curve, and the required cultural adjustment, 90% of time, the upgrade was worth the effort.

I am not sure if you will decide to pursue MS*SQL or stick with Access. Either way, I think you will have a more informed decision. And it looks like you can "play" with MS*SQL which should be fun too.

I wish you luck on your issue.

Take care
Richard

 

[KZPools]

What I've learned of most value in this post is the difference between DRI and Triggers. Information that I should've spent more time researching BEFORE I relinked our application to the converted SQL tables. I say that because as of this morning our Access front-end links to SQL and users have been inputting data into those tables today. That's relevant now because I'm in need of having converted my database with Triggers instead of DRI, primarily to take advantage of the cascade update/delete between some parent-child tables. If I reconvert the DB backup from yesterday, structure only, using Triggers instead, what's the easiest way to move my data from the first SQL db to the new SQL db? (Thanks!)

 

[KZPools]

...continued

Or, alternatively, can I move the triggers from one SQL db to another? And how?

Thanks!