How to convert Relationships into the new Access 2010 format
Bob Alston
integrity and cascading deletes. Can someone tell me how to convert
these into the format required for Access 2010 to be Sharepoint 2010
compatible?
bob
Albert D. Kallal
news:n9JNm.34550$6c2....@newsfe03.iad...
It can be hard to get people to answer questions for regular access, let
alone a beta, and worse yet the web stuff....
I not played with importing lots of data. But, to import two related tables,
and setup for web tables?
Just import the two tables (un-check the relationships option in the option
dialog else the import will not be permitted).
I shall assume you have something like
main table:
tblcustomers main parent table (pk = id)
child Table:
tblCustInvoices child table (FK = customers_id)
You will always setup the child relationship from the child table (makes
sense).
So, open up the child table. Add a new column (choose the field type..don't
add the column and THEN try to change the type. when you add a new column
choose lookup/relationships column.
the wizard will launch, choose the table (tblCustomers, and for the fields,
well, I just suggest you choose one column (id, the pk).
You also see the option to enforce referential integraity...choose the
cascade delete.
You now have the required relationship + column.
At this point, just copy your FK value to the new column. ctrl-g, and type:
currentdb.Execute "update tblCustInvoices set cust_id = customer_id"
Now, back to table view...if the columns look good then I would delete the
old column customer_id (right click that column...choose delete). And, then
I suppose you could re-name cust_id to customer_id if you stick to some type
of naming convention. So, just right click on this column and choose re-name
field. I think you can also double click on the column heading..but I don't
have 2010 in front of me right now.
At this point, you have your relationship. if you close this table, and open
up tblcustomers, you see the "+" sign that appears..and if you click on
them, you see the child data sheets show..just like they would in a non-web
based application...
There might be some other approaches you could use, but we are all new at
this. And, the above steps takes FAR less time then it took me to write this
post out...just a new collum, and the quite ctrl-g and update command....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOO...@msn.com
Bob Alston
1)How do I "un-check the relationships option in the option
> dialog else the import will not be permitted"? I don't see that
option in the Access 2010 File Share - Publish to access services?
2) Would I be correct to infur that if I were not going to run a web
app, only store the data in sharepoint but have users use the
traditional access client to update data, I need not establish relations
in sharepoint as they will still exist in the Access client and operate
as usual?
Thanks again for all your postings on Access 2010!!!
Bob
Albert D. Kallal
>
> 1)How do I "un-check the relationships option in the option
> > dialog else the import will not be permitted"? I don't see that
> option in the Access 2010 File Share - Publish to access services?
I don't understand why you are taking about publish for?
You were asking about how to IMPORT two tables with a relationships? (were
you not? PLEASE do correct if I am assuming wrong here, else I just wasting
your time!!!).
I am (well, we are..) assuming that you created a blank web databae. You
where then asking about how you import two tables with a relation. I thus
stated:
>Just import the two tables (un-check the relationships option in the option
dialog else the import will not be permitted).
I mean, you not asking how to import a table in access are you???? This is
not changed, for what many many years?
Did you try the above suggestion?
We were simply talking about importing two tables here (at least that is
what I thought, but again PLEASE do correct me I am wrong). The way you
import tables is done very much the same way it always been done. The dialog
may have changed a bit for 2007/10, but the process is much the same.
When you are using the import dialog, and during the import after you
selected the mdb, or accDB file (non web), you can select the two tables you
wanted to import. You will note that there is a "options" button on the
import form.
To get to the above, again, assuming we are in that blank web database.
Ok, so to import our two tables (with that assumed relationships), you go:
click on the external data tab in the ribbon.
Then, in the import group, click on the access (there are others such as
Excel, text files, but since we are going to import some access tables, then
it should be clear that we choose access at this point).
Ok, now the wizard panel comes up. You simply browse to the mdb, or accDB
file that has our two tables.
Below the above, you see two options:
[] Import tables, quires, forms, reports, macros, and modules into the
current database.
[] Link to the data source by creating a table.
Now, since we want to import our two tables, then we will choose the 1st
option in above (import).
Ok, now click on the ok button. You be at the tables tab. select your two
tables (the two tables with the relationships between them).
At this point, if you try to import, you will find that access refuse the
import. You will see an error in the UsysApplicationLog.
SO BEFORE you hit ok, you want to click on the "options >>>" button to
expand the dialog. When you do that, you will see the option to import
relationships checked. YOU MUST UNCHECK that option. So, in my original
post, I simply said that during the import you need to un-check the
relations option during the import, else access will refuse to import.
So, what I am simply telling you that you have to un-check the import
relationships during the import or access will refuse to import the tables.
So, un-check the relationships option. Hence, now you can import the two
tables. Once you done that then simply follow the steps in my post outlined
to setup a new relationship using the lookup column/relationship column
wizard. And, as mentioned can simply use a update query to copy the FK
values in that child table over to the new column. The whole process is
going to take well under 1 minute of your time.
> 2) Would I be correct to infur that if I were not going to run a web app,
> only store the data in sharepoint but have users use the traditional
> access client to update data, I need not establish relations in sharepoint
> as they will still exist in the Access client and operate as usual?
Well, if the linked tables data is on Oracle, you use oracle system to
modify the tables. If the data on sql server, you sql server system to
modify those tables. If the data in SharePoint, you use SharePoint system.
However, ms-access DOES allow you to modify tables and add/remove columns
etc in the client. And, access can be used to setup relationships in
SharePoint...even AFTER the tables been moved up. So, this works the same
for any linked database, but for SharePoint you can modify the tables client
side (you have to use layout view always to do this).
Keep in mind that a web database allows VBA (client forms) and web forms.
So, the quickest way to get the tables up would be to create a web database.
That way you be forced to setup the relationships use the "new" approach.
I not tried to up-sized tables with relationships in a non web database to
SharePoint. The upsizing in a2007 improved a WHOLE LOT over that of 2003
(and it would move up child tables). I guessing, but it like does attempted
to setup relationships now (but, I not sure if access is smart enough to
maintain the relationships when you up-size tables from a client only
application). You just have to try this.
I would likely create a web app, and then build VBA forms (client forms) in
that web app If I was going to have the tables reside on SharePoint.
However, you can up-size tables from a client only application. And, it very
much like up-sizing to sql server. You just wind up with linked tables.
However, once you up-sized, you not be using the relationships window since
the data is now on SharePoint. However, the new approach to setting up
relationships does work for web apps, my guess that these table design
features should work for linked tables (but, I not tested this).
Bob Alston
2010 database with various tables like I stated, with relationships. So
I really want to import the entire Access database.
Bob
Albert D. Kallal
> I am sorry for my confusion. I was asking about importing an access 2010
> database with various tables like I stated, with relationships. So I
> really want to import the entire Access database.
>
Sure, the above instructions apply equally well here to an standard (non
web) access 2010. I don't believe the web database will allow the import of
the two tables unless you un-check the import relationships option outlined
during the import (just try it!). So, I don't see a problem here. If the
tables are already web, and have used the new lookup/relationships windows,
then when you import them, the relationships will likely move along for the
ride.
I don't see any reason why would importing from access 2007, 2003, or even
2010 tables for that mattes changes any of my instructions.
It possible if you have some non published web tables in an application, the
import wizard says you can't import **and** upchucking the "import
relationships" box fails, but that would be a bug, and it would thus be a
simple matter to remove the relationships BEFORE you import in this case (if
access says you can't do this - I would just make a copy of the data file,
and then just remove the relationship using the lookup wizard *IF* access
says you can't import in this case).
So, the fact of importing tables from access 2000, 2003, 2007, or 2010
should not change the above issues and instructions one bit...
Bob Alston
explaining. I am trying to share the entire Access 2010 MDB file to
Sharepoint, using Access services. I Select FILE then SHARE then
PUBLISH TO ACCESS SERVICES. Next I run the compatibility checker. Next
I fill in the data fields for server name and site name and click the
button for PUBLISH TO ACCESS SERVICES.
I get a failure do to the existence of the table relationships.
what am I missing?
bob
David W. Fenton
news:96EOm.58362$rE5....@newsfe08.iad:
> So, what I am simply telling you that you have to un-check the
> import relationships during the import or access will refuse to
> import the tables.
Albert, I thought I was following the discussion, but I've lost the
thread here.
What are you importing, to what and from what?
1. to an Access web app?
2. to Sharepoint?
3. from an Access client app?
4. from Sharepoint?
I really did think I was following along, but by that point in your
post, I realized I'd completely lost it!
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
David W. Fenton
news:96EOm.58362$rE5....@newsfe08.iad:
> Keep in mind that a web database allows VBA (client forms) and web
> forms.
Er, didn't you mistype that? doesn't a web database *disallow* VBA,
i.e., client forms? Or are you saying you can mix the two in one
database? If you do that you won't be able to publish it, right
(that's what I thought I read on the Access blog yesterday)? Or can
you publish just the web parts?
Salad
> "Albert D. Kallal" <PleaseNOOO...@msn.com> wrote in
> news:96EOm.58362$rE5....@newsfe08.iad:
>
>
>>Keep in mind that a web database allows VBA (client forms) and web
>>forms.
>
>
> Er, didn't you mistype that? doesn't a web database *disallow* VBA,
> i.e., client forms? Or are you saying you can mix the two in one
> database? If you do that you won't be able to publish it, right
> (that's what I thought I read on the Access blog yesterday)? Or can
> you publish just the web parts?
>
There's this I found on the web.
http://blogs.msdn.com/access/archive/2009/11/17/access-2010-create-ribbon.aspx
Albert D. Kallal
> I don't think I am trying to do the upload the same way you are
> explaining. I am trying to share the entire Access 2010 MDB file
Ah, ok. By the way, do you do mean an accDB file, not an mdb file, right?
(for normal discussions, and for products in this NG tiny details don't
matter, but when working with beta, new product, new terms, new
features, then tiny details become HUGE details...just HUGE.
So, I assume you mean in the above accDB, as an mdb means a GRAND canyon of
difference here.
So, you can't publish unless you created a blank web database, and that MUST
be therefore a accDB file, it can't be a mdb file. So, was the above a slip,
or are you taking about a mdb file (if yes, then you can't publish that).
>
> I get a failure do to the existence of the table relationships.
>
> what am I missing?
Well, as I noted, you are UN-ABLE to import tables with relationships into a
blank web database. So, you should not have any local tables with old style
relationships. In fact, it should not be possible to have those older
relationships in the application). However, in pre beta (the CTP we had),
you COULD import those tables, but since the relationships window is
disabled in web database, then you can't delete the relationships.
So either you did not create a blank web database, and are running the
checking and publish option on a non web database This could be a beta bug,
but regardless, you cannot publish your application unless you created web
database. You can't use existing mdb, or accDB files.
You MUST create a web database, and then if need be import the tables into
that application.
So, somehow you have managed to by-pass the check that prevents you from
importing tables with relationships, or you are not in a web database. You
must done one of either of these issues.
Bob Alston
Where can I find out how to build the right kind of web-compatible
relationships?
bob
David W. Fenton
news:QIudnc2DdaiPEpHW...@earthlink.com:
Somehow, that leaves me just as confused as before. One part seems
to say that you *can* create client objects in a web database, but
another part seems to me to contradict that.
Albert?
Albert D. Kallal
> OK. I broke the beta <grin>
>
> Where can I find out how to build the right kind of web-compatible
> relationships?
>
My instructions quite much outlined how to do this.
you will:
create a blank new web database.
Import the two tables (lets just start with two tables to keep this simple).
As noted, if you attempt to import two related tables, access 2010 will
complain that you can't do this. thus, you have to follow my instructions in
the other post in which you DURING the import have to un-check the
relationships check box during that import.
Once you have the two tables imported (and, as mentioned, you will not be
able to import those tables if there is a relationships (so, how you managed
to do this is some type of bug).
Ok, now that the tables are imported, simply follow the instructions as I
mentioned in the other post. You add a new column of lookup type in the
child table (see my other post).
Albert D. Kallal
news:Xns9CCDD273023C5f9...@74.209.136.89...
> "Albert D. Kallal" <PleaseNOOO...@msn.com> wrote in
> news:96EOm.58362$rE5....@newsfe08.iad:
>
>> Keep in mind that a web database allows VBA (client forms) and web
>> forms.
>
> Er, didn't you mistype that? doesn't a web database *disallow* VBA,
> i.e., client forms? Or are you saying you can mix the two in one
> database?
Yes. 100% correct, you can and likely will mix VBA client forms, and also
have web only forms in he SAME application.
> If you do that you won't be able to publish it, right
> (that's what I thought I read on the Access blog yesterday)? Or can
> you publish just the web parts?
You just whack publish...it will check the web only objects..and they
publish up to the server, and the client objects are ignored. So, yes, to be
clear, you can (and likely will) mix both client forms and web forms in the
same application.
Even more cool is when you write macros, and the dropdown pops up for the
openForm action, in a client form, the list of web forms is ALSO included in
the drop down (since it legal for VBA and client forms to launch web only
forms). However, if you writing a macro in a web only form, and when typing
in the openForm action, the drop down will show ONLY web forms, since web
forms can't launch client forms....
so, yes..this is most fun..and it bit fuzzy confusing at first...but, it
very cool!
after you published the application, development of client forms, or web
forms still occurs in the access client. If you make any changes to web
forms, then you just hit the syc button (the publish button will be hidden).
So, sync will push up any changes you made to the web side. so, if you add
more web reports, or modify a web form, only the parts you change has to go
up to the server (not all of the web forms...so a change to one web form
will publish quite fast..since that all that has to be sent to the web
(SharePoint) server...
Albert D. Kallal
>
> Somehow, that leaves me just as confused as before. One part seems
> to say that you *can* create client objects in a web database, but
> another part seems to me to contradict that.
>
> Albert?
>
See my other responses. but to answer this question of mixing web and VBA
client forms in the same application, the answer is yes that is not only
possible, but it my guess this type of mix will be standard fair for these
web applications.
This also explains why some asked how come they don't see much difference
when I choose web databae, or standard blank database. (answer: you can
still create VBA forms in the web database, and if you don't publish it, you
be hard pressed to figure out what the web stuff is all about).
So:
You can create BOTH web and client (VBA) forms in web applications
You can NOT create web forms in a non web application.
David W. Fenton
> As noted, if you attempt to import two related tables, access 2010
> will complain that you can't do this. thus, you have to follow my
> instructions in the other post in which you DURING the import have
> to un-check the relationships check box during that import.
Uh, isn't that something that Access should do for you, i.e., if it
knows you can't import relationships, shouldn't it uncheck it for
you?
Albert D. Kallal
> "Albert D. Kallal" <PleaseNOOO...@msn.com> wrote in
> news:oUvPm.800$y%5....@newsfe03.iad:
>
>> As noted, if you attempt to import two related tables, access 2010
>> will complain that you can't do this. thus, you have to follow my
>> instructions in the other post in which you DURING the import have
>> to un-check the relationships check box during that import.
>
> Uh, isn't that something that Access should do for you, i.e., if it
> knows you can't import relationships, shouldn't it uncheck it for
> you?
>
Perhaps?? I not sure what is best here. It is likely best to inform the user
that while the relationships imported just fine for the last 17 years, this
is a change. Otherwise, the user will not really know they have a problem to
deal with here. This is a beta build, and this could change. Right now I do
think it best one has to un-check the relationships box.
You see in this thread, Bob would have imported the tables, and then not
ever known that the relations where gone and would have worked merrily along
until some cascade delete did not work, or worse published to the web
without ever knowing the relationships were deleted. I not sure if it best
that the relationships would be just ignored here. Forcing the user to deal
with this certainly means it is CLEAR and not hidden that the relationships
are coming over during the import.
In this thread I suspect that Bob would have just imported the tables, and
never gave the relationships issue an 2nd thought. At least this way they
are informed and told the relationships are not importable...
I suspect at ht end of the day, the importing system should actually CREATE
the new correct relationships for the user. I suspect this functionally will
not make it into the final build (everyone on the access team is VERY busy
and I don't see time left over for new things, let alone dealing with what
is likely the largest release in access history).
Bob Alston
understanding that the relations aren't there. In fact, I cannot
currently upload the entire app until I remove the relations.
bob
Albert D. Kallal
> Just to clarify I would never have imported the tables without
> understanding that the relations aren't there. In fact, I cannot
> currently upload the entire app until I remove the relations.
>
> bob
Right, but are talking about a web database, or a regular one?
Albert D. Kallal
Right, but you not answered my question?
Did you create a blank web database, or just create a regular database?
If you can still open the relationship window, then you have NOT created a
web database.
You can most certainly publish a regular database to SharePoint. Your forms
and reports etc. WILL NOT make the trip up to SharePoint. So, attempting to
publish a non web database WILL ONLY effect your tables.
And, if your tables still have old style relationships, then you have to
remove them (and, since you have the relationship window, then you can easy
remove them). You can THEN STILL follow my instructions in the other post
(ie: add a
new column of type lookup/relationship type and use the wizard to setup the
relationship. And, then use an update query to move the data over to that
new collum). These instructions WILL WORK for your current NON WEB database.
However, I thought the whole idea here was to attempt this with a web
database,
NOT A REGULAR database? (or did I just much miss every single thing you been
talking about here all along?).
If you can see/view the relationships window, then you HAVE NOT created a
web database.
So, do you have a web database, or did you create a regular database?
(it would REALLY help us here if you could answer this simple question).
Here is a screen shot with the two create database options:
So, if you can see the relationship window, you have NOT created a web
database.
so, what kind of database did you create here, web or client (regular
database)?
Bob Alston
run any web forms or web reports. but I want the data on sharepoint.
Bob
Albert D. Kallal
> I have a regular client database. I intend to run it on the client, not
> run any web forms or web reports. but I want the data on SharePoint.
>
Ah, excellent, then the my WHOLE speal about not being able to import tables
with relationships DOES NOT apply to you! (so, that why this question was
important here to determine. So, now, a BIG chunk of my instructions do not
apply to you.
So, in your case, just remove the relationships, and then add the lookup
column as I outlined, and then use the update query to fill that new column.
That lookup column + the enforced relationship on that lookup column will
move up to SharePoint (share 2010, since previous versions don't support
Referential integrity at this time, only SharePoint 2010 does).
David W. Fenton
> "David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
> news:Xns9CCFA0EA62828f9...@74.209.136.89...
>> "Albert D. Kallal" <PleaseNOOO...@msn.com> wrote in
>> news:oUvPm.800$y%5....@newsfe03.iad:
>>
>>> As noted, if you attempt to import two related tables, access
>>> 2010 will complain that you can't do this. thus, you have to
>>> follow my instructions in the other post in which you DURING the
>>> import have to un-check the relationships check box during that
>>> import.
>>
>> Uh, isn't that something that Access should do for you, i.e., if
>> it knows you can't import relationships, shouldn't it uncheck it
>> for you?
>
> Perhaps?? I not sure what is best here. It is likely best to
> inform the user that while the relationships imported just fine
> for the last 17 years, this is a change. Otherwise, the user will
> not really know they have a problem to deal with here.
Well, it seems obvious to me, from years of using computer programs
and from designing user-friendly interfaces that:
1. you definitely want to be informed of the problem.
2. you want to be able to cancel and not go through with it.
3. if you have to do something to fix it, and it's something that is
cut-and-dried i(.e., there's only one way to do it), then Access
should offer to do it for you.
That makes for more complicated UI, but all that's needed is a
troubleshooting wizard, e.g.,:
Message at top of wizard: your tables have relationships and those
can't be imported. What would you like to do?
[option group:]
o cancel the import.
o import the tables without relationships.
I can't see how this is hard. You either want to import the tables
or not. If you want to, then you have to import them without the
relationships. Rather than tediously having to uncheck the option
yourself, why can't Access do it for you?
> This is a beta build, and this could change. Right now I do
> think it best one has to un-check the relationships box.
I don't think it's the best UI, though. It's the kind of thing that
annoys me.
> You see in this thread, Bob would have imported the tables, and
> then not ever known that the relations where gone and would have
> worked merrily along until some cascade delete did not work, or
> worse published to the web without ever knowing the relationships
> were deleted.
I didn't mean to suggest that it should do it for you *silently*!
Absolutely not! It should just ask you if you want to skip the
relationships and import without them.
> I not sure if it best
> that the relationships would be just ignored here. Forcing the
> user to deal with this certainly means it is CLEAR and not hidden
> that the relationships are coming over during the import.
Again, I didn't intend that.
> In this thread I suspect that Bob would have just imported the
> tables, and never gave the relationships issue an 2nd thought. At
> least this way they are informed and told the relationships are
> not importable...
But once you're informed of the issue, instead of being told "Click
the OK in this dialog and then uncheck the IMPORT RELATIONSHIPS
checkbox and try the whole thing again", why not just deal with the
issue right there, asking you "do you want to skip importing the
relationships? If not, you won't be able to import" or something
like that.
> I suspect at ht end of the day, the importing system should
> actually CREATE the new correct relationships for the user. I
> suspect this functionally will not make it into the final build
> (everyone on the access team is VERY busy and I don't see time
> left over for new things, let alone dealing with what is likely
> the largest release in access history).
I would have assumed that relationships that can be replicated in
the Sharepoint relationships would be imported. It's a little tricky
knowing exactly how to replicated your Jet relationships with the
tools available in Sharepoint, no? It's a combination of the RI
available and table-level data macros (for handling updates if you
need CASCADE UPDATE), but there's nothing all that mysterious about
how to map one to the other.
David W. Fenton
> if your tables still have old style relationships, then you have
> to remove them
Perhaps I understood what you described the process to be. I thought
you just had to uncheck IMPORT RELATIONSHIPS, rather than deleting
them entirely? That is, your original would remain unchanged, with
its relationships, while the imported tables would have no
relationships defined.