Help! Final Year Uni Project Pain!

[frank_spencer]

Can some please help with the following enquiry that is causing me a lot of pain in my final year project.

I am using Microsoft Access with MSDE as the back end. When I am creating my tables, I want to be able to set my foriegn keys as a look up value to find the primary key in another table.

I can create a look up field when I use Acesss .mdb. I.e. Create table in design view, select the relevant field, go to field options, select look up tab and create the control.

However, now I am using MSDE I am not creating a .mdb database, I create an access project (.adp) and when I go to design the table, the look up tab is not available as all I can see is the general tab.

I have tried every option and spent hours trawling the net to work out how to create a field look up in an adp database but I cant seem to resolve this.

Can someone help please as my final year project rests on this - doh!

 

[boblarson]

Can some please help with the following enquiry that is causing me a lot of pain in my final year project.

I am using Microsoft Access with MSDE as the back end. When I am creating my tables, I want to be able to set my foriegn keys as a look up value to find the primary key in another table.

I can create a look up field when I use Acesss .mdb. I.e. Create table in design view, select the relevant field, go to field options, select look up tab and create the control.

However, now I am using MSDE I am not creating a .mdb database, I create an access project (.adp) and when I go to design the table, the look up tab is not available as all I can see is the general tab.

I have tried every option and spent hours trawling the net to work out how to create a field look up in an adp database but I cant seem to resolve this.

Can someone help please as my final year project rests on this - doh!

Thankfully MSDE and full SQL Server doesn't have that feature and those of experienced Access folk wish Access didn't either. They are a pain-in-the-butt item that can cause you no end of grief when it comes to forms and reports. So, my suggestion is USE FORMS! Do NOT use the tables for direct entry; you should use forms. With forms you can have combo boxes and list boxes that work the same as the table lookups. They store the id value and not the text (look up normalization on that one) and they display the text for you on the form. When you build reports, you use queries that pull your tables back together so you can show the text and not the ID's.

 

[pbaldy]

I agree 100% with Bob, and here's further info:

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

 

[frank_spencer]

Thank you - Forms and ASP.NET

Thank you both for your prompt and extremely helpful responses.

The reason I was trying to automate the look up field was because I was using asp.net to communicate with the tables via a website. However, I am a novice and still learning so your advice is gratefully received.

I will create an access form and try to link it to my aspx page. I.e. The users enter data via aspx, which then goes into the form then into the tables.

Or,alternatively I will create a web form to update the 2 different tables and then create a query to link the two tables together, then generate the query response on my aspx page.

I have a way forward now and hopefully get my uni project back on track!

Excellent, thanks again!