Access to SQL
-
I am migrating a Microsoft Access database to SQL Server. I am new to SQL, and I would like to transfer my lookup tables from Access into SQL. The tables themselves transfer fine using the upsizing wizard, but it seems that I lost the lookup tables as the valid domain for the corresponding columns. For example, In my Access database, I have a table called "Projects." Each Project has a corresponding "Council District" where the project is being conducted. There are 12 possible Council Districts from which to choose from. In Access, the Council District ID column of my Projects table is a lookup into my Council District Table. When insterting data into that column, I get a drop down that includes the values in my lookup table (Council Districts). Both of my tables were moved over to the SQL Server, but it seems that I no longer have the "Council Districts" table as a lookup of valid records for the Council District column in my "Projects" table. Is it possible to carry this information over? Will I need to use another method to set up a lookup table as a list of valid records in SQL? Thanks.
-
Sql server does not allow lookups in the table structure as does access. You can replicate the functionality however by creating a combo box on the data entry form and set it's rowsource to the appropriate table. I would normally create a view in Sql server and use this as the rowsource of the combo box.
Don't forget to make sure all your sql server tables have primary keys set as sometimes the conversion will skip these.
Cole
-
Take a look at this reference. It been extremely helpful for me.
Microsoft Access Developer's Guide to SQL Server by Mary Chipman and Andy Baron.
Good luck.
Phillip L. Hagan -
Jackie,
All the lookups should be done from the GUI side of the application. No one should be accessing the tables directly. Especially with a SQL Server database. Something else that you should do is do a diagram in SQL Server and set up all the relationships between the tables. This is similar to the relationship window in Access, except that you can define diagrams for different subject areas within your database. For example, you could have a customer diagram, a product diagram, or an order diagram.
Defining the lookup at the table level is not a good thing even in Access.
Robert
-
Robert,
I have setup the relationships in the diagram designer. Are you saying after the relationships are setup the appropiate domains are setup for the columns in the SQL tables? For example, in the attached picture, Manager ID in the Project Managers table is the Primary key and Manager ID in the Projects Table is the foreign key. In Access, when I enter data into the Manager ID field of the Projects Table, I get a pulldown menu that lists the records from the Manager ID field of the Project Managers table. Now that I am in SQL, when I enter data into the Projects table in the manager id column, I don't get the pulldown, is there a way of setting this up in SQL? Or are you seeing that the realtionships in the diagrams are the only way to establish lookups.
-
No, I am saying the same thing I did before.
Drop downs are done at the user graphical interface. You cannot do it at the table level like Access and it is bad design to do it in Access that way also. You NEVER give a user an interface directly to the table. You create forms for them to do data entry in. You create the dropdowns on the forms.
The diagrams are how you set up your referential integrity like in Access and visually see how things are related to each other.
-
I think I understand what you are saying. That fact that my actual doesn't have dropdowns in not important, because that is not where the updates should occur anyway. The editing should occuring within the forms, which is where I should create my dropdowns/lookups.
Do you think is what Cole means by setting up combo boxes?
-
Yes, that is what Cole was talking about also.
If you need help with this, let me know. I often mentor beginning developers. I teach/lead and Access Users group in Houston and have been doing it for about 12 years. I also co-authored a book on Access, so I am pretty good at it. 🙂
-
I see you are very good at it. I will definitely be needing some assistance as i get more into developing in SQL. I will try my hand at some forms in SQL. Actually, it seems as if forms are a type of "view" in SQL. I appreciate your help.
-
Actually, there is no such thing as forms in SQL Server. SQL Server is not the GUI that you would give to a user. It is only the database behind the GUI. Well, not just that, it is also view of the data. Which is like queries in Access. Diagrams showing the relationships between tables similar to tools like ERWin and ER Studio. Stored procedures which are like modules in VB/VBA which contain business logic programming. And functions which are also like modules in VB/VBA which perform a specific thing, likle returning an age when the birthdate is passed to it.
You will need to use Access, VB, .Net, or something similar to create the forms for the user interface.
-
That's interesting. I have a partner who is pretty good with .net, or we may continue to use Access for the forms.
-
.Net is very good and a good choice for SQL Server. You can also use the Access Data Project(ADP). It is a direct connection into the SQL Server. The only think about it is that you do not have the query capability that you do with an Access MDB because the queries are actually views on the server and not queries stored on the Access side like the MDB. This can be a plus or a minus, depending on the skill set in your shop.
I have done a couple of web sites with ASP.net and VB.net. They work well and were not too bad in creating. I think the next version (2005) will be even better.
-
It sounds as if .net will be the way to go. I'll start off with an Acess front end or adp until we can figure out how to make things work with .net.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply