Question How to connect to SQL database file

[genesis]

I am a newbie in SQL and Access.

I would like to know if I can link my access database file to SQL database file without installing the SQL server itself.

For example, I have my front and back-end ms access and I have also created a SQL database file as data storage. So instead of connecting to my back-end, I would like to connect my front-end to SQL database file without installing the SQL server. Can it be?

Any help is greatly appreciated. Thanks.

 

[Albert D. Kallal]

No, when you use sql server, you are not linking to a file. You NEVER even a specify a file location when using sql server. You are linking to a server box.

In fact, this means that you can use an IP address and even link to a server over the internet. Thus, when you use sql server, ms-access knows nothing about the back end file, and never even opens it. In fact, this means that the sql server on the other end does not even have to be a windows server or even based on a windows compatible file system. You can connect to a Oracle box running on Linux.

In normal practice with a mdb back end, your startup code can check if the link is bad, and then run code to re-link to the back end that just might be in a different location. Just about anyone who deploying access software to customers usually has cobbiled up some re-linking code on startup. That way if I deploy a new update to you, then on startup the application code can re-link to the correct location for the back end mdb file (in fact, I store the back end path name in a text file that is read in on startup each time…if the backend location does not match..then I run some re-link code).

The process is really very much the same for sql server. If you need to change, or have a different sql server, then in your startup code you can some code run to re-link to the tables to a differnt sql server (but, you links will be to a server, NOT some back end file aymore).

So, at the end of the day, the process of re-linking tables is very much the same in both case (mdb back end, or sql server back end). In both cases, the application will be already linked BEFORE it is deployed.

So, no, you can’t have a front end pre-linked to a server that is not running. When you use a mdb file (jet based back end), you are opening a file. When you link to sql server, you NEVER open a file, but are connecting to a “server box” That server box is the one that opens and reads the files for you. So, the file NEVER transfers accorss the network..Only bits of the data that the server had read from that file is transmitted (so, you connect to a server, not some file).

This also very much explains why sql server is more security then a mdb back end (you NEVER open the back end sql server file direct. In you can remove ALL user permissions to that back end sql file, and your software will still run fine). It is the server that opens an reads that sql file, not ms-access. So, you NEVER need nor have physical access to the back end sql server files.

Albert D. Kallal
Edmonton, Alberta Canada
kallal@msn.com

 

[sunilvedula]

Hi albert ,
i have been reading your posts for some time now.i was also about to email today when ifound your post here. i was reading an article about splitting the database which people forget often to use. The article was awesome and thank you for putting up for people like us. NOw i have some questions realted to your answere here and about the posts http://members.shaw.ca/AlbertKallal/Articles/split/index.htm
1. Does splitting of database work when we want multiple users located at different location in the world to work?
2. If i split the database does it not become slow. There was a solution you mentioned in the article to create a form which will open one table and keeep it so that again when they open it will run fast but i thought i would require more clarity on it.
3. I have a read a solution where it says that i can use the upsizing wizard in Access 20003 (i use it) which allows me to convert my access db to sql and still can use my forms. i.e i can still go ahead with my mdb and mde and give all my users located at different location the mde and it willl connect to the sql server where i kept my convert db. Does this option work (as still i am trying) and what problem would i face by doing it.
4. Would it be necessary for me to recreat the fornt end in any webpage or asp.net if i convert my db to sql server using upsizing wizard?

 

[Albert D. Kallal]

Hi albert ,

1. Does splitting of database work when we want multiple users located at different location in the world to work?

Well in theory it does. But this will only work if the users around the world can see and view the folder with that back end mdb data file resides. So if I want a use the word file on your computer, or PowerPoint file, or in this case a standard mdb file, Then I’ll have to have access and be able to see that file on your computer.

The problem is accessing a windows file over the Internet does not work very well at all. Things work a lot better when you’re connecting to SQL server, then as I mentioned you’re not actually opening a file any more, but only connecting to a server. In this scenario, using sql server as the back end can work very well over the inetnet.

However trying to use a backend mdb access database file over the Internet will not work well at all. I explain why this sport is a problem in the following article:
http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

( by the way, I don’t normally post to this newsgroups so, I’m not really sure from allowed to post web links). If not simply go to my website and find the article on using access on a WANS.)

2. If i split the database does it not become slow. There was a solution you mentioned in the article to create a form which will open one table and keeep it so that again when they open it will run fast but i thought i
would require more clarity on it.

Depending on your network setup, and amounts of data you pull across the network, a split system will still often runs slow. In fact because then when you have two files, access also tends to do a little bit more overhead in terms of opening and closing things on that back end file, and thus you often see a bit of a slowdown. To prevent this opening + closing of the back end database file, we use what is called a persistent connection trick. This simply just means that you force and keep the connection all open to the back end mdb file at all times during the operation of the application. You can do this by having some startup code that opens up a recordset, or sometimes people just build a form bound to a table and then minimize it. You then run the rest of the application as before. At the end of the day it doesn’t matter what table you keep open, the trick here is you want to force the connection on the network to stay open at all times to that back end and that will improve performance by quite a bit.

3. I have a read a solution where it says that i can use the upsizing wizard in Access 20003 (i use it) which allows me to convert my access db to sql and still can use my forms. i.e i can still go ahead with my mdb and mde and give all my users located at different location the mde and it willl connect to the sql server where i kept my convert db.

Yes if those users have permsisos to use that server.

Since you’re using SQL server in this example, that it has actually nothing to do where the location of your mde file is. Remember, with sql server there is no back end file that access knows or cares about. However if those users from any location around the world have permissions AND a connection to that database server, then yes you can distribute your front end database to any of those users no matter where they are, and they’ll be able to use your application. Read my article on WANS for more detailed and further explanation of this issue.

The main problem of opening up the SQL server to the wild Internet is that the Internet is a very nasty place full of hackers and all kinds of people that will want to mess with your system. So while the technology is fairly easy to implement, the knowledge and skill required to make sure that the setup is secure and free from other people messing around with your data is a different matter that will require MUCH knowledge and experience on your part. A VPN is usually the best approach for security in this fashion. Again, my WANS article talks about using a VPN.

Does this option work (as still i am trying) and what problem would i face by doing it.

The major issues and using SQL server is that some of your code will have to be modified to take advantage of SQL server for better performance. You can upsize all of your access tables to SQL server, and then setup links from your front end to sql server. At this point about 95% of your application should run correctly. It will be a small amount a work to get the rest your application functioning correctly. However, the bulk of the work involved will still remain, and that bulk of work will be improving the performance of your application. Migrating to SQL server usually performs ok, but running over the internet means you must optimize your bandwidth usage. Unless your application is well written and takes steps to minimize bandwidth requirements, moving to SQL server will not perform well enough over the Internet. Keep in mind that the average Internet high speed connection is about 100 times slower than your typical cheap office network that access is running on. So you’re dealing with something that will perform 100 times slower, and thus you need very much caution here.

Again I explain this performance problem in detail in my WANS article.

4. Would it be necessary for me to recreat the fornt end in any webpage or asp.net if i convert my db to sql server using upsizing wizard?

Well if you create a front end with a web page, then you not using access at all. If you want to continue to use your access application, then you keep in MS access, and linked it to SQL server. However in my WANS article I outlined a number of other technologies you can use that are far less work, and work far better, and allow anyone anywhere to use your access application even if they don’t have access installed on their computer.

Albert D. Kallal
Edmonton, Alberta Canada
kallal@msn.com

 

[sunilvedula]

Thank you for all your replies and so prompt. It give me a fair idea and of course i am studying your link for better understanding. Just one more question
1 If i create a space on the server and give access to that drive which is access across all groups (approx 30 users across 7 countries- same office network) and just place my mde (without splitting) will all of them be able to use it? Please be reminded that i am not converting into sql or anything. Just pasting my db on tht drive where nothing else resides. what problem would i face?

 

[shadow9449]

If i create a space on the server and give access to that drive which is access across all groups (approx 30 users across 7 countries- same office network) and just place my mde (without splitting) will all of them be able to use it? Please be reminded that i am not converting into sql or anything. Just pasting my db on tht drive where nothing else resides. what problem would i face?

You don't want to have a database shared without splitting it - certainly not with 30 users. Albert has a good explanation on his website as to the perils involved with an unsplit database.

What I've done successfully is to create a folder for each user, put a front end into each folder and then link each user to his/her respective front end. I have a system with over 40 users using nothing but Access/Jet and Windows Terminal Services and it's been working great for almost 2 years now.

SHADOW

 

[Albert D. Kallal]

1 If i create a space on the server and give access to that drive which is access across all groups (approx 30 users across 7 countries- same office network) and just place my mde (without splitting) will all of them be able to use it? Please be reminded that i am not converting into sql or anything. Just pasting my db on tht drive where nothing else resides. what problem would i face?

You face two major problems:

The fact that you split or do not split does not change if this will work. The problem is when you don’t split you wind up with a very unreliable application. If your application is so unreliable your users will not like you anymore, and they lose complete faith in your competence and they not want to trust the software anyway (it’ll be a waste of time for users to enter data into something that they don’t know is going to work. With frequent crashes, the users will think you’re incompetent)

The other issue I talked about and I answer my WANS article is the fact that your performance is like to be about 100 times slower. I’m not sure if you can grasp the concept of 100 times slower. Lets try: When you walk to the store to purchase a soda pop it might take fifteen minutes. 100 times slower means it’s going to take you 1500 minutes (25 hours). So, you can inqure as to how fast your Wide Area Network is (WAN), but it likely going to be too slow performance wise for this to work.

However as I mentioned, if you read my article on WANS even if you did have a fast enough WAN network (which you don’t have), you’d still have the problem of frequent damage and constant file corruption.

So the downsides of doing this is that it will be so un-reliable that no one will want to use the application. The second downside is that it will run so slow as to be un-usable. As I said, your Wide Area Network (WAN) is likely 50 or 100 times slower then your cheap-o local office network.

Albert D. Kallal
Edmonton, Alberta Canada
kallal@msn.com

 

[genesis]

Thank you very much for your replies Sir Kallal. So to conclude with, I cannot connect my front-end MS Access to my SQL database file without the SQL server.

So how can I connect my front-end MS Access to SQL database? I am not very familiar with sql. thanks

 

[Albert D. Kallal]

Thank you very much for your replies Sir Kallal. So to conclude with, I cannot connect my front-end MS Access to my SQL database file without the SQL server.

So how can I connect my front-end MS Access to SQL database? I am not very familiar with sql. thanks

Well, you have to setup and install sql server. the process is then VERY much like running a split data base. the up-sizing wizard built into ms-access will move up your data to sql server.

HOW TO: Convert an Access Database to SQL Server (a97,a2000)
http://support.microsoft.com/Default.aspx?id=237980

ACC2000: Optimizing for Client/Server Performance (odbc)
http://support.microsoft.com/?id=208858

ACC: Tips for Optimizing Queries on Attached SQL Tables
http://support.microsoft.com/?id=99321

ACC2002: "Access 2002 Upsizing Tools" White Paper Available in Download Center
http://support.microsoft.com/?id=294407

You can google this issue, you will get more material faster then you can read it....


Keep in mind that using Terminal Services is a LOT less work because the you don’t have to use sql server to make your application work over the internet. In fact, you can even get your application to run inside of a browser when you use terminal services. So, again, if you read my article on WANS I suggest several possible solutions here, and also have some links to using terminal server.

Albert D. Kallal
Edmonton, Alberta Canada
kallal@msn.com

 

[genesis]

ok. thanks Sir. it is clear to me now. thank you. can I ask more from you sir. I have stated here that I dont know much of setting up SQL nor SQL language.

can you please show me how can i connect to SQL database thru my ms access if i have installed my sql server!
thanks.

 

[Albert D. Kallal]

ok. thanks Sir. it is clear to me now. thank you. can I ask more from you sir. I have stated here that I dont know much of setting up SQL nor SQL language.

can you please show me how can i connect to SQL database thru my ms access if i have installed my sql server!
thanks.

The steps are outlined here:

http://office.microsoft.com/en-us/access/HA102004941033.aspx

The above is for 2007, but just go file->get external data, and the steps are then the same as above outlined if you using a previous version.

Albert D. Kallal
Edmonton, Alberta Canada
kallal@msn.com

 

[sunilvedula]

Thank you very much sir albert. I have read your article and it did give me insight into what WAN can do and not do. I have suggested my business area to get me an SQL server ((after reading your articel))which would be reliable and to use the upsizing wizard (i use access 2003) and if not i have to go through terminal server (which i am still unclear about). Thank you a lot for all the help.