May 14, 2013 at 1:31 pm
I have a SQL server express that I'm using to learn how SQL server works. Up till this point I've done all my DB work in Access. I'm now beginning to develop vb.net applications for web and want to learn to use a DB designed for web.
Here's what I don't yet know/can't figure out:
My company has several access databases that use linked tables to share data. I used the import wizard to import each .mdb file in as a seperate database in my SQL server. I now want to regenerate the linked tables in SQL server such that DB1 points to a table in DB2.
I tried using the SQL server management utility, but couldn't figure this out. Can someone help?
Mike
May 14, 2013 at 7:06 pm
for one thing, Database Design does not change depending on the application, to a point.
You use a fully qualified name
Database One has one Table named Person with one Column named FirstName
Database Two has one Table named Person with one Column named LastName
To use the LastName Column in your query, you would fully qualify the name
USE One
SELECT LastName
FROM Two.dbo.Person
WHERE LastName = ?????
There is no such thing as Linked Tables in SQL Server. You may Join Multiple tables using Referential Integrity to Select data that is needed. There are Linked Servers, that allow you to Link Servers together to do the same thing as Fully Qualified Names
But it really depends on what is in each database. You may want to put all your data into one database. SQL Server does not work anything like Access. You cannot copy the same database design as Access. I would assume that you have multiple Access Databases is due to a size restriction in Access. Especially if different databases are storing the same data.
Andrew SQLDBA
May 14, 2013 at 7:07 pm
for one thing, Database Design does not change depending on the application, to a point.
You use a fully qualified name
Database One has one Table named Person with one Column named FirstName
Database Two has one Table named Person with one Column named LastName
To use the LastName Column in your query, you would fully qualify the name
USE One
SELECT LastName
FROM Two.dbo.Person
WHERE LastName = ?????
There is no such thing as Linked Tables in SQL Server. You may Join Multiple tables using Referential Integrity to Select data that is needed. There are Linked Servers, that allow you to Link Servers together to do the same thing as Fully Qualified Names
But it really depends on what is in each database. You may want to put all your data into one database. SQL Server does not work anything like Access. You cannot copy the same database design as Access. I would assume that you have multiple Access Databases is due to a size restriction in Access. Especially if different databases are storing the same data.
Andrew SQLDBA
May 15, 2013 at 6:51 am
The reason for the seperate access databases is that each is a stand alone application with tables, queries, forms and reports.
We are a Roman Catholic Diocese and have several DB applications. One of the DBs contains information regarding the 129 parishes. In that DB there is a table that holds parish info such as name, location, etc with a PK of par_num. That table is shared with the other access database apps such that there is one central place for that info.
One of the other databases that shares the parish info is being given a web front end and the intent is to move from access to SQL server for the web. I understand that the access front end will simply need to have the tables point to the SQL server to get the data.
Since the database containing the parish info is sharred, I was going to move it into SQL Server as well and share that data from theresuch that the SQL server has 2 databases in it. This way there is one central repository for the data and access in now just a front end for the SQL server backend. This will eleviate the need to rebuild the access applications in the web.
Since I came from an Access background, I was trying to recreate the linked tables that I had in Access. I did not know about using fully qualified addressing to be able to reference tables in seperate databases. Now that I think about it, I don't see why I need to have seperate databases in SQL server other than to keep things resembling the original access databases.
What I would now like to know is if there exists for SQL server a visual tool to help me build my queries like there is with the access application. I have the SQL management studio but have not yet had that much time to become familiar with it.
Michael
May 15, 2013 at 7:38 am
Yes, SSMS and you use stored procedures for your queries.
There is also something that you may want to use. Schemas. Create a different schema name for each distinct item. Perhaps name one Schema [Parish] in that schema you could have tables that relate all the parish. Schema security can be controlled, so only certain logins can access certain Schema. I think that would solve your issue of all those separate databases. I shutter at the thought of trying to maintain that mess.
Andrew SQLDBA
May 15, 2013 at 7:43 am
I never realized how much I relied on Access's query builder to write SQL for me. I used SQL server Management studio to try and build a query. Since I'm a bit rusty, I linked the tables I was using in access and built the query there so I could see the SQL. Once I converted access's naming convention to the fully qualified names, I was able to execute the query and feel like I had learned something. 🙂
I must say that learning something new each day is a great thing.
May 15, 2013 at 7:49 am
Wow. Something new to learn. I like this. Maybe someday I'll be able to say I know it all. Though at that point, they'll probably develop something new and I'll have to start over.
May 15, 2013 at 7:53 am
I have been using SQL Server for close to 25 years, I am still learning. It is every changing for the good.
I am certain that if you think about a new design for your database, and design it in SQL Server, you will have a much better database. Use the correct data types, use the modern SQL functions and coding standards, correct naming conventions and no more "linked tables", even maintaining the database will be all automated and simple
Andrew SQLDBA
May 15, 2013 at 8:10 am
The one cavait to creating a better structured DB in SQL server is that the Access databases contain many years worth of historical data which I do not want to loose. That said I will talk to the DBA here at the diocese (she's in the cubical next to me) and see what, if anything, we might want to modify going forward.
On the side of keeping the Access applications working after modifying the tables to point to SQL server, I renamed the tables in my test access DB to drop the dbo_ prefix that was added when I linked the tables. This is so that we won't need to rework the queries to use new table names. I then went to build a query and found that one of the tables generates the annoying message "ODBC - Connection to 'SQL Server' failed" when I tried try to query it. I don't see why renaming the table in Access should cause this. I deleted the table and relinked it and all is good.
May 15, 2013 at 8:16 am
SQL Server can very easily pump all that data in. You would not lose any data if done correctly.
You cannot go renaming objects once they have been created. There are one to many dependencies on those objects. And then you rename one, things will break. Access prefixed the table name for a reason.
Andrew SQLDBA
May 17, 2013 at 11:23 am
it.web (5/15/2013)
What I would now like to know is if there exists for SQL server a visual tool to help me build my queries like there is with the access application. I have the SQL management studio but have not yet had that much time to become familiar with it.Michael
Michael, this is just my two cents, but you will be far better off in the long run if you make a commitment to yourself to learn to program your queries in SQL, rather than letting a GUI do it for you, for a couple of reasons. First of all, while the query builders are ok to get you started with simple queries, they will limit your ability to create more complicated queries. It's kind of like using the wizards to create Access forms: they can do a lot of stuff, but the true power of what you can do with a form is not unleased until you delve into the VBA editor. Also, and this may sound a bit corny, but you will want to have an intimate relationship with your data. Having to express what you want to see in terms of SQL will help build your understanding of data type, good relational design, indexes, etc. Again, just my two cents.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 17, 2013 at 1:41 pm
Greg Snidow (5/17/2013)
it.web (5/15/2013)
What I would now like to know is if there exists for SQL server a visual tool to help me build my queries like there is with the access application. I have the SQL management studio but have not yet had that much time to become familiar with it.Michael
Michael, this is just my two cents, but you will be far better off in the long run if you make a commitment to yourself to learn to program your queries in SQL, rather than letting a GUI do it for you, for a couple of reasons. First of all, while the query builders are ok to get you started with simple queries, they will limit your ability to create more complicated queries. It's kind of like using the wizards to create Access forms: they can do a lot of stuff, but the true power of what you can do with a form is not unleased until you delve into the VBA editor. Also, and this may sound a bit corny, but you will want to have an intimate relationship with your data. Having to express what you want to see in terms of SQL will help build your understanding of data type, good relational design, indexes, etc. Again, just my two cents.
You will also find that there are things you can write by hand that Query Builder in Access and SSMS can't do. Now is the time to learn how to write your queries, while the ones you need as still relatively simple to create.
May 20, 2013 at 7:26 am
What I was looking for was a visual interface much like visual studio is to .net. Sure I can write sql if I have to... but why do it if there are programs to assist me. It would be like developing a .net application using notepad. Sure it can be done, but you don't get extra credit. I know that I can link the SQL server tables in Access and build queries that way, but then I still have to make them work in SSMS.
BTW, I didn't build the database. I'm simply moving the tables from access to SQL server and adding a web interface for remote users. Regardless of how intimate I am with the data, there's ten years worth of it and it that has to be maintained and I won't be able to adjust much.
Since the folks here in the office don't want things to change, I'm going to make the tables in the access DB linked tables pointing to the data in SQL Server. That way there is one central repository and I'm able to share the data in both web and client environments.
May 20, 2013 at 1:18 pm
I would recommend using stored procedures for all your queries. SQL Server is so much different than Access. You will want to use the SQL Data types, and re-think all your indexes. All of that kind of thing is totally different. You will want to use all the modern SQL Server T-SQL language. There is so much there that can greatly improve all your queries. Your constraints will be some what different. Or should be once you look at your design.
You can easily store all your older data in a separate database. You can always get to that data. But that will keep your working database small. When the tables are small, the indexes are small, and that means much better performance. But large to SQL Server and large to Access are night and day difference.
SQL Server is so much more than just a bunch of tables to store your data. Please don't treat it as such. Visual Studio is not the best tool for writing queries and working with SQL Server databases. You really should use SSMS and the related tools.
Andrew SQLDBA
May 20, 2013 at 1:33 pm
it.web (5/20/2013)
What I was looking for was a visual interface much like visual studio is to .net. Sure I can write sql if I have to... but why do it if there are programs to assist me. It would be like developing a .net application using notepad. Sure it can be done, but you don't get extra credit. I know that I can link the SQL server tables in Access and build queries that way, but then I still have to make them work in SSMS.BTW, I didn't build the database. I'm simply moving the tables from access to SQL server and adding a web interface for remote users. Regardless of how intimate I am with the data, there's ten years worth of it and it that has to be maintained and I won't be able to adjust much.
Since the folks here in the office don't want things to change, I'm going to make the tables in the access DB linked tables pointing to the data in SQL Server. That way there is one central repository and I'm able to share the data in both web and client environments.
The problem with the visual interfaces is they usually aren't smart enough to help with writing the more complex queries that are sometimes required to meet the more demanding data requests.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply