If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
On 26 Feb 2010 21:57:16 GMT, "David W. Fenton"
wrote: With a SQL Server, you have to have a port for client PCs to connect to but the big question is where it is available. You could expose it to the public Internet like you do your web server, but that would be extremely dangrous. Hi David, Lots of SQL Server databases (publicly and privately hosted) are open outside their domain for external usage without a VPN. SQL Server is pretty secure if you follow best practices. You can also use other methods like restricting IP addresses in your firewall. Also, by definition SQL Azure will be open to the "public Internet" and will use IP address restriction, so we might as well get used to it. Armen Stein Microsoft Access MVP www.JStreetTech.com |
#22
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
Jon22 wrote:
Can anyone suggest a simpler way of achieving remote access to my database? Now one of the things I didn't see mentioned in the discussion is will the folks who access your data remotely from overseas always be on a reasonably reliable Internet connection? If so then a VPN to SQL Server, Terminal Server or Sharepoint would work well. If not then you'd have to look at replication, either Jet or SQL Server or possibly SharePoint has enough capabilty that you could go away from the home network for a week or two. Of course if replication is required then backups need to be done while on the road in case the laptop is lost/stolen/etc. And likely you want the laptop hard drive to be encrypted such as using Windows Vista/7 Ultimate Bitllocker. Tony -- Tony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ For a convenient utility to keep your users FEs and other files updated see http://www.autofeupdater.com/ Granite Fleet Manager http://www.granitefleet.com/ |
#23
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
"Banana" Banana@Republic wrote in message news:4B87C75A.8050100@Republic... While this is technically accurate, this is conceptually misleading because in client's interaction with server, the question of "where is the .mdf?" will never be asked by the client. In fact, a properly set up While this is technically accurate, it is conceptually misleading, because in a clients interaction with an application, the question of "where is the database" will never be asked by the client. If it is asked by the client, it works the same way: to use the application, you use the connection string, to connect to the service which provides the records. server would _deny_ access to the users to those files. Only the daemon (aka SQLSERVER service) or the administrator would have access to those files. Yes, only the daemon (aka SERVER service) has access to those files. Yes, if you have the correct permissions, you can request that data from the service. Yes, the permissions and protocols are different, and allow you to do different things. Thus, it's important to understand that you do not "browse and open a file" as you would with other applications such as documents or You do not browse and open an MDB or MDF file, unless you want to. Normally, you connect a database application to a database. Yes you can browse MDF files, either using a file browser or OSQL or SQLCMD or Active Directory, or using an AD addin or etc. Yes you can open MDF files. Using OSQL or SQLCMD or etc. Yes you can open MDB files. Using Access or whatever. spreadsheet, but rather "connect to" a server using one of many protocols... TCP/IP, Named Pipes, Named Memory, and then some more. Yes, you can connect to the SERVER service using one of many protocols, TCP/IP, Named Pipes, Named Memory, and then some more thus the question of "where is the file" is totally nonissue It's a total non issue to the end-user. However, it is not a non-issue for a person setting up a system, Unless the end-user is also administering the logical location of files and the physical architecture of the file-server. Which is what the Personal Computer revolution was all about, so you may be familar with that kind of behaviour. because that's the daemon's job to manage it and you communicate with the daemon. You communicate with a daemon regardless of whose job it is to organise the logical location of files and the physical architecture of the server. There are two points the 1) It's just services communicating with services. It all works that way regardless of what kind of database you are using. 2) Databases map to URI's because someone set them up that way. It is all mapped: none of it is physical. Many people are familier with the simplest way a file mapping may be set up using SERVER. Far fewer people are familer with even the simplest way to setup a file mapping using SQL SERVER. Actually, it's no more difficult, and no less flexible, it's just unfamilier. Where does it get reside? Like Access, they reside where you put them. If you want to share them, you put them somewhere where they can be shared. Then you set up the shares so the user can use them. No. As I explained, daemon does all file handling here. Yes, as I explaned, the server does all the file handling, and presents it through the SERVER service and a client at the workstation. At the workstation, you ONLY deal with the CLIENT service. Therefore a proper setup will _deny_ the users access to the files. All Right, this is a difference between the two services. communication should go through the daemon. Yes, communications always goes through Windows Services. So you never put .mdf and .ldf files in a share folder Conversely, if you want to use the Access/Jet, you never put the MDB in a Master database. but rather merely open the port 1433 for TCP/IP or use Named Pipes or whatever for the clients to connect to. Yes, if you want to use the native windows database primatives through the SERVER service you merely open the port 139 for SMB This is why if you look at various ODBC connection strings, the only time you do see a filepath is when you're working with a serverless source (e.g. a .mdb will have its filepath in the ODBC connection, but you'll never see that for a Oracle, MySQL, DB/2, SQL Server!) Databases aren't bits of sand, or bricks. You use a mapping syntax which depends on the ODBC driver, and you map to names which may, or may not, mean something to the end user. For example, it is conventional to use a similar name for the logical name of a SQL Server database, and for the logical name of the MDF file in the NTFS file record. But the most important thing you got wrong is this: working with a serverless source (e.g. a .mdb Look at the second word of this phrase: File Server There is a basic misunderstanding that constantly confuses people. Mostly, they get confused because they don't realise that a file server is a server, and that messes up their understanding of how Access/Jet works. That is not relevant here, so I won't say anymore about it. connectionstrings.com To be crystal clear: The users do not need and should not have access to the .mdf and .ldf files!! What does that have to do with Port Numbers, Services, or where you put the database files? Nothing. So what are you on my case about? I do believe that "Server" service is not actually germane to the discussion here. If you read its description, it says it enables file, print and named piped sharing over the internet. That what it does. SQL Server services, OTOH, enables clients to connect to this machine and issue requests for data. The SERVER service, on the other hand, enables clients to connect to this machine and issue requests for data. Using the SMB protocol If you want to learn more about the SMB protocol, which includes the Record requests, and the Record Locking requests necessary to support the native Windows database primitives, you will have to read more than just the 'description' of the SERVER service. In fact, if I turn off "Server" service, I still can connect to SQL Server on my SSMS, and reach a Sharepoint site remotely... In fact, if you turn of the the SQL SERVER service, you can still connect to the SERVER service, and reach your Access database remotely (or any database which uses the native Windows database services) IOW, "SQL Server" service and "Sharepoint" service are not dependent on the "Server" service. There is of course the notable exception that if I wanted to use Named Pipes, then yes, I do need "Server" service. But with TCP/IP, then no. They have nothing to do with each other. I'm not sure what your point is? SERVER and SQL SERVER and IIS are different services. SERVER is the service (daemon if you will) used for SMB IIS is the service (daemon) used for HTTP and others SQL SERVER is the service used for OLEDB and others. As I explained in my previous reply to Jon22, the term "server" actually refers to a role, rather than some kind of program. Yes. A server usually has a daemon (aka Windows service) that listens on a specific protocol (e.g. a port on TCP/IP, a region of address for Named Pipes/Memory, etc.) and react to the requests through there. Yes. It goes back to why it's important to not be so concerned with the questions such as "where is the file?" because those are wrong questions to ask. Except when you are setting up your servers or trying to understand what a 'server' or trying to understand what a 'service' is And you've come in here and told us the answer to some completely different question. You've told us that with the SQL Server, (as with Access) the end user doesn't need to know where the file is physically. And then you've told us that the original questions were 'wrong'. But you've been so quick, you missed an important point: the OP is not an end-user. He still has to work out where to put the files. You will have to completely re-write that stuff in TSQL. It's not a lot more difficult, but it is different. I would question that. A well-written Access application usually will run on any backend, be it Oracle, MySQL, DB/2, SQL Server with minimum change (e.g. using linked tables). It all depends on whether one has written the application specifically for server-client architecture in mind, of which also benefits even ACE engine. The original poster has an application that will not run on "any backend" He has clearly described the problem, and the observed behaviour. You have implicitly equated "A well-written Access application" with "written ... specifically for server-client architecture". I would question that, but what difference? His application clearly is written specifically not for server-client architecture, whether or not you call it "well-written" There are times and places where re-writing a piece of functionality into T-SQL, stored procedure, views or other SQL Server objects does make sense, This specific case. This specific case about the specific problem as described in this specific thread. but I really don't believe it requires a wholesale rewrite. Well, I haven't seen the application, and you haven't seen the application, but at least I never suggested a wholesale rewrite. I suggested a "complete re-write" specifically of the stuff which is not compatible with a "server-client" architecture" "You will have to completely re-write that stuff in TSQL" But that is largely a function of how well written the application was to begin with. And you finish off be again implicitly equating "well written" with "specifically for server-client architecture." You know, telling people that you think their questions are wrong, and suggesting that a clearly described application architecture is not well written, is mildly insulting at the best of times, but I think that if you manage to read down this far you've done your penance. (david) |
#24
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
Jon22 wrote:
Thanks Banana! Definately cleared away some of the fog. Glad to hear that. Let me know if I'm off the mark here, but I think the best approach for me is going to be: Copy the tables from my Access database to a SQL Server database then split the Access database, delete the linked tables from the FE database and then relink to the tables residing on the SQL database (which should have exactly the same table names and field names and datatype as the ones that were put into the now obselete back end Access database so that the front end database should still function as it does now) That can work. You'll need to learn which SQL Server data types to replace though those are minor differences but good for you to know nonetheless. Now, just to be 100% sure, also do consider David Fenton's advice of using Windows Terminal Server because he is correct that this would require no rewrite of the application so all you would have to do is move it to a server and deploy there then you're done. We want to be sure you've considered all possible solutions and what they means to you in terms of performance, scalability and cost. I think that I'll be able to link to the SQL database tables easily enough from my copy of the front end Access database because this FE .mdb file will be sitting on the same computer if I install SQL Server on my computer. What about other clients on our office network? Or even more scary, our computers in our overseas office? How do they find the SQL database to link to? Do I need to save the SQL database (effectively our back end database) on to our network storage device which has a static IP address assigned to it and ftp & https access or our website or something? Or does my computer (effectively the server once I install SQL Server onto it) suddenly gain remote access capabilities even sitting behind our LAN router/moden firewall? Well, yes, SQL Server has to be accessible from somewhere. Static IP is a simple way to implement this though you'll definitely want to ensure you have proper security in place (e.g. accept connections from only certain IPs that your company owns for example) or use a VPN connection so the connection to SQL Server is as if it's local, as David Fenton explained. From what I can gather, when I link to the SQL database tables I'll need to create new ODBC files which, from my understanding, in the simplest terms gives the FE Access database the filepath of the BE database objects to link to? Will this have to be done for each client computer? Or can I create one database with the linked tables all done and then send this .mdb file to all the staff to load onto their hard drives? In other words, are ODBC files only useful to the computer they were created on? DSN (which you call ODBC files) are useful to any computer, provided that they can reach the destination specified on the file. To provide an example, if you had in your DSN: "Server=192.168.0.100", it'd work for all computer where it is in the same network with SQL Server (including those connected via VPN), but that would not work for any client outside the network because the 192.168.xxx.xxx is a private network IP address; you'd have to use a public IP address if you want it to be world-accessible. So the answer would be "it depends." As an alternative, you can use "DSN-less connection", means that you don't have to distribute the DSN, but instead create the link with the complete paths within code. Doug Steele has an excellent example at his site: http://www.accessmvp.com/DJSteele/DSNLessLinks.html More importantly I guess, am I way off here or perhaps still missing any vital fundamental steps in my perception of how this is going to all work? I think you've covered this. As mentioned before, be sure to consider the other advices as well. If you've decided to press on this route, and you are interested in some referential materials which also contains more links, here's two for you: Beginning SQL Server by Leigh Purvis: http://www.utteraccess.com/forum/ind...wtopic=1732935 Beginner's Guide to ODBC by myself: http://www.utteraccess.com/forum/ind...wtopic=1843709 Best of luck! |
#25
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
David W. Fenton wrote:
But if you're contemplating allowing disconnected use, you'll need the SQL Server running on the laptops and you'll probably need to implement SQL Server replication in order to keep the laptop databases synchronized with the central one. Replication is not a minor issue, and if you can avoid it, you really should. If OP wanted to use the application in disconnected state, I would really not want to entertain the idea of running SQL Server Express on every client machine. That would be too much administration and training. I'd daresay it'd be easier to just use Access local tables and write scripts to synchronize when the laptop comes home. I also understand that SQL Server supports replication with linked server, but I'm not sure how well SQL Server handles synchronizing with non-SQL Server sources or whether it'll be practical in this use, though. I agree that replication is difficult no matter how you set it up. In general, backing up the live SQL Server files is not going to give you a reliable backup. It's just like backing up an MDB/ACCDB that is open by a user -- you may or may not get a valid file out of it (it's probably even less likely with the SQL Server files, I would think). SQL Server has a backup agent to take care of backups for you, but one of the disadvantages of SQL Server Express 2008 is that the backup agent is not included! Some backup software is able to talk directly to the SQL Server and get a backup file, but if that is dependent on the agent, it won't work with SQL Server Express. That's why I had qualified my statement as "even then that is not strictly necessary." Perhaps I should have had used "is not actually necessary", because in most practical backup usage, we'd be dealing with ..bak files, not .mdf or .ldf files. |
#26
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
David W. Fenton wrote:
Banana Banana@Republic wrote in news:4B87C75A.8050100@Republic: I do believe that "Server" service is not actually germane to the discussion here. It can also not be turned off without disabling your PC, even if you aren't sharing anything at all. (I tried it with my first copy of NT, as I wasn't sharing any files) Well, as I said in my previous reply, I could turn it off without any ill effects to the connections to SQL Server or Sharepoint. Now, if I had used named pipes, this would be a problem because Server service provides this functionality, so in this context, SQL Server service would have to depend on Server service for named pipes connection. Otherwise, TCP/IP connection will work with or without Server service. Maybe it changed since last time you tried it on your first copy of NT? |
#27
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
I don't know why, but there seems to be snippets where it should have
been quoted but wasn't so I had to filter which was your words and which was my words not correctly quoted. Anyway... david wrote: Yes, only the daemon (aka SERVER service) has access to those files. Yes, if you have the correct permissions, you can request that data from the service. Yes, the permissions and protocols are different, and allow you to do different things. However, you said in your previous reply: Like Access, they reside where you put them. If you want to share them, you put them somewhere where they can be shared. Then you set up the shares so the user can use them. The statement sounded like you were suggesting that we could put a .mdf and .ldf files on a share folder. Perhaps that's not what you meant, but I was very concerned by that statement. Hence, my emphasis that users doesn't need access to those files, even to "administer" it because it's the daemon's job to administer it. You do not browse and open an MDB or MDF file, unless you want to. Normally, you connect a database application to a database. How do you connect to a .mdb file? In Access, you get a file dialog and browse for a file and choose a .mdb file. In SQL Server, you will not see such choices in most tasks. The closest to this would be when you do a CREATE DATABASE. Yes you can browse MDF files, either using a file browser or OSQL or SQLCMD or Active Directory, or using an AD addin or etc. I'm not sure I understand what is the point you're trying to make here. Sure, I can see the files in the Windows Explorer, but in the SSMS, I don't get asked for the physical location of database I want to connect to. Same with SQLCMD. I'm not sure what AD has to do with this here. Yes you can open MDF files. Using OSQL or SQLCMD or etc. No. It's daemon's decision to open the file - it may be already open before you connect to the database contained in the .mdf file. The point is that daemon lives on its own and does its own thing whether there are clients connected or not. In case of file-based RDBMS such as JET/ACE, it's the application (e.g. Access) that open & manage files. JET/ACE runs in-process with Access so it goes out of scope when Access quits. This is not the case with server-based RDBMS where daemon will continue to live on its own with or without any client connections. thus the question of "where is the file" is totally nonissue It's a total non issue to the end-user. However, it is not a non-issue for a person setting up a system, Unless the end-user is also administering the logical location of files and the physical architecture of the file-server. Which is what the Personal Computer revolution was all about, so you may be familar with that kind of behaviour. However, the OP said he got stuck at the prompt to choose a SQL Server and this was what I was replying to. I think I've already qualified the few special cases where we do have to be concerned with physical location, but when OP was trying to upsize it, he was working within a client context and this is the context I explained. There are two points the 1) It's just services communicating with services. It all works that way regardless of what kind of database you are using. I'm not sure about that. If Access were the front-end client to the SQL Server, that's a application communicating to service... e.g. client to server. This is not to say that servers can't be also a client, but I suspect the scenario where it's a service to another service is the minority here. 2) Databases map to URI's because someone set them up that way. It is all mapped: none of it is physical. This is the case with server-based RDBMS, but this is not the case with file-based RDBMS. As I said, a connection string for an ACE database requires a filepath. You won't see a filepath in a connection string for a server-based RDBMS. Many people are familier with the simplest way a file mapping may be set up using SERVER. Far fewer people are familer with even the simplest way to setup a file mapping using SQL SERVER. Actually, it's no more difficult, and no less flexible, it's just unfamilier. To be honest, you've totally lost me here. I already said that I don't think "SERVER" (presumably referring to Server service??) is germane to the discussion - it's an entirely different service from what SQL Server service does, and as I replied to David Fenton, SQL Server may or may not depend on Server service on whether we require Named Pipes connection or not. And I'm not seeing how file mapping fits in here. Maybe you're referring to setting up a file server?? If so, that doesn't really have anything to do with SQL Server, which we still don't deal with it in terms of filepaths. Yes, as I explaned, the server does all the file handling, and presents it through the SERVER service and a client at the workstation. At the workstation, you ONLY deal with the CLIENT service. What client service? If Access is the front-end with linked tables to the SQL Server, it's a client to SQL Server service, but it's not a service itself. Web browsers are client to IIS and Apache but it's not service themselves. As I asserted earlier, there is nothing preventing a server from being a client itself but this is less common. Therefore a proper setup will _deny_ the users access to the files. All Right, this is a difference between the two services. communication should go through the daemon. Yes, communications always goes through Windows Services. So you never put .mdf and .ldf files in a share folder Conversely, if you want to use the Access/Jet, you never put the MDB in a Master database. As I said earlier, you earlier replied that 'they' (which is not all that clear and could refer to .mdf/.ldf files) could be put in a share folder, and that was completely wrong way to do this. Again, maybe that was not what you intended, but if I could have had read it as referring to .mdf/.ldf files, then it's possible that others would have had likewise and thus I replied with a clarification. I'm not following the phrase "never put the MDB in a master database." Master database is a SQL Server construct and I don't know why anyone would want to put .mdb file in there (presumably as a BLOB) and I don't see how this is a converse of my assertion that .mdf and .ldf don't belong on a share folder. Databases aren't bits of sand, or bricks. You use a mapping syntax which depends on the ODBC driver, and you map to names which may, or may not, mean something to the end user. For example, it is conventional to use a similar name for the logical name of a SQL Server database, and for the logical name of the MDF file in the NTFS file record. But the point here is that you never need to know the .mdf's path in order to connect to the database residing there, whereas with .mdb, you must know the path to use data within it. That's the whole point, clearly contrasting a file-based RDBMS from a server-based RDBMS. But the most important thing you got wrong is this: working with a serverless source (e.g. a .mdb Look at the second word of this phrase: File Server There is a basic misunderstanding that constantly confuses people. Mostly, they get confused because they don't realise that a file server is a server, and that messes up their understanding of how Access/Jet works. That is not relevant here, so I won't say anymore about it. If you're suggesting that JET/ACE is a file server (and thus not serverless), then I've seen many places making that claim but I do not think this is entirely accurate. Here's the reason why: What is a server? A residual program that listens to the specificed communication protocol and responds to any requests. In order for the program to be "residual", it has to be started up at the machine's startup and active the whole time. After all, it couldn't very well listen if it wasn't alive to begin with, no? This is why we use daemons or services - they're one that's doing the listening. For JET/ACE (as well SQLite and SQL Server Compact Edition), they are not listed as a service. They are just a bunch of .DLLs that may be called by an application (e.g. Access for ACE for example), and when called by this application, comes into the memory but it will go out of the scope when Access quits. ACE uses filesystem to locate the data file and work on it in much like manner as Word would upon its .doc document and Excel upon its .xls document. In case of split databases, when two users connect to same backend, what it means is there are two ACE engines running on each client. When they want to get some pages they use cooperative locking, so they "play nice" with each other when they share the file. There is no central administration or gatekeeper as is the case with SQL Server service making decisions who get what access to what data. If there are ten users, there are 10 ACE engines, all cooperatively sharing a single file. With SQL Sever, there's only one daemon whether there's zero client, one client, or nth client. The reason why ACE has been called a "file server" was because it works with files and uses pages within it to read the data and write data to it, but this is not accurate - an actual file server merely means it provides a set of folders that can be shared among clients. ACE certainly may require a service of file server, but it's not a file server itself. The SERVER service, on the other hand, enables clients to connect to this machine and issue requests for data. Using the SMB protocol If you want to learn more about the SMB protocol, which includes the Record requests, and the Record Locking requests necessary to support the native Windows database primitives, you will have to read more than just the 'description' of the SERVER service. I wonder what you mean by "native Windows database primitives"? I think I already acknowledged that this is necessary to share files, prints and named pipes. Also, I'm not sure whether "Record requests" and "Record Locking requests" are in reference to JET/ACE's constructs or perhaps SMB has its own construct? I'd have had thought that a file server dispense a file, while JET/ACE would handle the interior structure, but I don't know SMB as much as I would. Maybe I'm missing your point here and would like to hear it. In fact, if you turn of the the SQL SERVER service, you can still connect to the SERVER service, and reach your Access database remotely (or any database which uses the native Windows database services) Your reply was very confusing to me that I had to go back and read, and now I realize you were talking about Server service as essential for file sharing for _Access_. The whole time I thought you were talking about it in connection to SQL Server service and that was why I failed to see how it was relevant to the discussion previously. My apologies. Except when you are setting up your servers or trying to understand what a 'server' or trying to understand what a 'service' is And you've come in here and told us the answer to some completely different question. You've told us that with the SQL Server, (as with Access) the end user doesn't need to know where the file is physically. And then you've told us that the original questions were 'wrong'. But you've been so quick, you missed an important point: the OP is not an end-user. He still has to work out where to put the files. Well, I answered this way because OP said he was stuck on the prompt to choose a SQL Server. In this context he was the client so I did believe that by stepping back and explain why this was being asked of him it would help him better understand the overall process, including the physical location of .mdf and .ldf. You have implicitly equated "A well-written Access application" with "written ... specifically for server-client architecture". Perhaps so, but my intention behind this is indicate that how much work will depend on how well-written it was to start with. You know, telling people that you think their questions are wrong, and suggesting that a clearly described application architecture is not well written, is mildly insulting at the best of times, but I think that if you manage to read down this far you've done your penance. I have no intend to insult others. I have no specific knowledge of OP's application, which is why I wanted to clarify that this is a function and leave it up to OP to hopefully work out a realistic assessment of how much work he will need to invest into achieving his end goals. As for questions being wrong, I perhaps have had went on a limb out there and made guesses at what OP was thinking about, but the intention was to help reorient the OP with how server works. |
#28
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
Thanks David, they sound like convincing enough arguments to me. I have spent
way too many hours on this Access database to go back and rewrite anything. Can you clear something up for me though. Is Windows Terminal Services exactly the same thing as Remote Desktop? Or is it a platform that allows multiple simultaneous Remote Desktop connections or something? The reason I ask is, I regularly use Remote Desktop to access/connect to my workstation at the office from my notebook at home and I have to say, I find it great for some things but not so great for others. The concept is brilliant but I don't like the way the interface looks through this connection or how it reacts to commands. Everything looks pixelated and runs 'jumpy' and not very responsively. So with WTS would the staff be just remotely opening the desktops of their computers on our office network and then opening the Access database from there? Or is it something closer to a copy of the Front end being on their remote computers and the back end being on our office network and the two talk to eachother via WTS? One other thing, the firewall settings on our 2701HGV-W Gateway modem only allows one instance of Remote Desktop to be used at a time between all the computers on the network (although this is a relatively small issue in the scheme of things). "David W. Fenton" wrote: =?Utf-8?B?Sm9uMjI=?= wrote in : I think I am going to persevere with trying to get my head around SQL server. I think this is a mistake. Writing an Access app to run on SQL Server across the Internet means you have to really understand how to design and maintain server-side components (view and stored procedures) and then you have to carefully design your app to use those server-side objects, and you may have to jump through certain hoops (such as using ADO to get editable results from sprocs) that are not compatible with default Access behaviors/designs. Also, you'd have to set up a VPN (assuming you're not silly enough to just expose your SQL Server port directly to the wide-open Internet). If you've got a server to run SQL Server, you've got a server that can run Windows Terminal Server. Since you'd have the VPN anyway, the only step to use WTS would be to acquire the CALs to allow users to connect to run their Remote Desktop Sessions, and then the basic setup of the terminal server for your application (giving each user an individual copy of the front end, automating updates, etc.). And WTS requires NO ALTERATIONS to your application, and no learning curve. Going with SQL Server to support remote access has a very high learning curve, and would require substantial alteration to most Access apps designed to run with a Jet/ACE back end (or even most designed to use SQL Server on a local LAN). There really is no comparison in regard to the cost and difficulty level. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ . |
#29
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
Banana,
Access/Jet is built on top of the Windows Database primatives. There is no indexing, or transactions, in the database services that JET uses. Transactions in particular have recently been added to Windows, and are not used by JET. Jet builds it's own locking and Workgroup security on top of the Windows Services. Workgroup security, because Jet pre-dates Windows security. Locking, because without Windows Security, Jet needed to build it's own lock database to be able to report who had a record locked. So the Jet/MDB locking is done by locking records in the lock file, rather than by locking records in the data file. The locking of records in the lock file is done by using Windows Record locking. Windows has a mechanisim for reading/writing/locking records. Jet uses this to get what are sometimes called 'physical records' from the database. On old IDE drives, physical records are disc sectors, not even disk clusters, and certainly not what is returned by a record request, so that nomenclenture is not correct, but if the OS was integrated with the File System and the Disk Controller, as they were in old OS's, then the OS database primatives would return 'physical records'. ACE uses Windows security instead of Jet Workgroup security, which is good, because Jet Workgroup security is very old, not properly implemented, and has never been updated. But ACE as written can't do table level security. ACE needs Windows record-level security to implement ACE table-level security. Windows security doesn't have record- -level security, and Windows table-level security was based on a different model. So ACE will get table-level security if ACE is completely re-written, or if Windows adds record-level security. (david) |
#30
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
Replication is out of the question. Too much of the data relies on unique
values that if doubled up would cause massive headaches at the time of consolidating it all. "Tony Toews [MVP]" wrote: Jon22 wrote: Can anyone suggest a simpler way of achieving remote access to my database? Now one of the things I didn't see mentioned in the discussion is will the folks who access your data remotely from overseas always be on a reasonably reliable Internet connection? If so then a VPN to SQL Server, Terminal Server or Sharepoint would work well. If not then you'd have to look at replication, either Jet or SQL Server or possibly SharePoint has enough capabilty that you could go away from the home network for a week or two. Of course if replication is required then backups need to be done while on the road in case the laptop is lost/stolen/etc. And likely you want the laptop hard drive to be encrypted such as using Windows Vista/7 Ultimate Bitllocker. Tony -- Tony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ For a convenient utility to keep your users FEs and other files updated see http://www.autofeupdater.com/ Granite Fleet Manager http://www.granitefleet.com/ . |
Thread Tools | |
Display Modes | |
|
|