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 |
|
#1
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
I'm close to completing the consolidation of various small Access databases
and a couple of Excel spreadsheets that my little company uses (5 staff) into an all encompasing Access database and I was planning on splitting the database when I was finished to allow simaltaneous use of it by staff on our small office network. However it is becoming more and more obvious to me that I am going to need to give staff access to this new database from remote locations (outside of our office network). Two staff members are regularly overseas, one is mainly on the road with her laptop etc etc. I've been playing around with trying to link tables from remote locations (ftp path of the database on our Network Storage Device at the office) and needless to say - that don't work. So I've spent the last 10 hours downloading, installing and trying to get my head around Microsoft SQL Server 2008 Express and how it might help me out. It all started when I came across a very encouraging article on keeping your Access database as your front end application and linking to SQL Server database tables. I'm finding it MUCH more complicated than I had hoped. Can anyone suggest a simpler way of achieving remote access to my database? I have at my disposal a Network Storage Device which has remote access capabilities (this is where the database is currently stored), our website which it could be stored on, or I'd be happy to use my quite powerful workstation at the office as some kind of server. You may have guessed by now that I'm no IT guru by any stretch so please bare this in mind with any suggestions. |
#2
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
hi Jon,
On 25.02.2010 17:42, Jon22 wrote: It all started when I came across a very encouraging article on keeping your Access database as your front end application and linking to SQL Server database tables. I'm finding it MUCH more complicated than I had hoped. The first step is almost quite easy. Either use the Access built-in upsizing wizard or use SSMA http://www.microsoft.com/downloads/d...displaylang=en To push your back-end to the SQL Server. You need to test your application as some things my be broken during migration as some DAO/Access/Jet specific things do not longer work. After that you can dive into SQL Server Replication which is quite easy to master, but you need some time to get into it. Can anyone suggest a simpler way of achieving remote access to my database? The easiest way is to use a Terminal Server (-2008) or Remote Desktop Services (2008r2). Here you simple give your users another "virtual" desktop which can be easily access over the net. Your application still runs local to your domain network. mfG -- stefan -- |
#3
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
Thanks Stefan. I installed SQL Server 2008 Express on my laptop at home last
night just to have a fiddle with it and try to get familiar with it but as I said, it confused me no end. After I had installed it I tried to run the Upsizing Wizard on a dummie copy of our database but after selecting "New Database" on the first step, I got stuck at "What SQL Server would you like to use for this database?" on the next step. I'm struggling with the fundamentals of SQL Server databases. Is it a single file like an .mdb file? Where does it get reside? Is it creating an SQL Server on the machine which is a different thing to an SQL server database? Which machine/s should I be installing the program on? We don't have a 'Server' persay just a number of desktop machines and a network storage device so are we under equipped? What IS a server anyway? Can one of our more powerful desktop workstations act as our 'server'? I need to be able to run all this myself as we are too small to have regular outside IT costs, but large enough to require a WAN Access Database solution. I might be wrong but I think it'd also be better to have the data (tables) stored locally (office network) but accessable remotely as most of the usage is at the office and I'd prefer not to sacrifice too much speed for the ability to use the database remotely. Can I have my cake and eat it too using SQL Server? Would the SharePoint option make using the database slower at the office? Also the database relies heavily on unique key values in the tables such as Quote Numbers, Purchase Order numbers, Job Numbers, Invoice Numbers etc. Many of which are automatically generated in the forms via complex functions in the control's default values which in turn rely on up-to-date and constantly refreshed info from the tables so as to avoid duplicating these values. Is SQL Server the solution for this? Is there such a thing as a back end database that updates instantly with every single change or addition made to it that is instantly reflected in multiple user's front end db all over the world? I had a bit of a trial run a few months back at splitting this database and playing around with the sharing options and could not for the life of me get the thing to avoid duplicating key fields when opened simaltaneously. This worries me greatly as the whole thing relies on the integrity of these fields. "Stefan Hoffmann" wrote: hi Jon, On 25.02.2010 17:42, Jon22 wrote: It all started when I came across a very encouraging article on keeping your Access database as your front end application and linking to SQL Server database tables. I'm finding it MUCH more complicated than I had hoped. The first step is almost quite easy. Either use the Access built-in upsizing wizard or use SSMA http://www.microsoft.com/downloads/d...displaylang=en To push your back-end to the SQL Server. You need to test your application as some things my be broken during migration as some DAO/Access/Jet specific things do not longer work. After that you can dive into SQL Server Replication which is quite easy to master, but you need some time to get into it. Can anyone suggest a simpler way of achieving remote access to my database? The easiest way is to use a Terminal Server (-2008) or Remote Desktop Services (2008r2). Here you simple give your users another "virtual" desktop which can be easily access over the net. Your application still runs local to your domain network. mfG -- stefan -- . |
#4
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
Jon22 wrote:
I'm struggling with the fundamentals of SQL Server databases. Is it a single file like an .mdb file? Where does it get reside? Is it creating an SQL Server on the machine which is a different thing to an SQL server database? Which machine/s should I be installing the program on? We don't have a 'Server' persay just a number of desktop machines and a network storage device so are we under equipped? What IS a server anyway? Can one of our more powerful desktop workstations act as our 'server'? Good questions! Let's go back to Access just for a moment. When you create an Access database, you are directly managing a file, .mdb file. You manipulate it via Access UI, adding data (and objects, too) to it and perhaps even update or delete it as well. In this context, this is no different from what you've been doing with your Word document and Excel spreadsheet. You're working with a file. Now if we go to the SQL Server, you are no longer working with a file, per se. Rather, you "connect" to a server and thus communicate with a daemon (e.g. the program that resides in memory persistently and listens & reacts to requests from other programs). When you request a piece of data, it's the daemon that opens the data file, read the piece and send it back to you. Now, you examine this piece and decide you want to update. You then give the daemon the request to update, but it's the daemon that actually does updating into the data file. In all cases, you never ever touch the file directly. You work through the daemon and the daemon does all file handling. So, for most RDBMS out there, we interact with the daemons while in world of JET/ACE, or SQLite or SQL Server Compact Edition, there are no daemons and we manage the files directly. Once you understand that concept, it should help become clear why you connect to a "server" rather than open a file. You never ever touch those file directly, contrary to what you did in Access. The server can be anything, a dusty old 386 PC to whatever's the latest "big iron" supercomputer- it has nothing to do with hardware in question, though it is common to market "server hardware", but in fact, "server" refers to the role in the communication. As explained above, you need to have a place for the daemon to resides. The daemon very well couldn't exist in a void, can it? So whenever SQL Server happens to be installed, that is where the daemon is and thus that host is the "server" while other machines that requests data from this server are clients. In fact, Access is capable of being a client because you can create a linked table or use ADO connection to fetch data from a server. Now, generally speaking, it is usually expected that the server would run on more powerful hardware because obviously there is more demands placed on the server (and hence the market for "server hardware"). But it doesn't mean that you have to go out and buy yourself a server farm just to run SQL Server. As long the hardware where you install SQL Server are adequate for your actual demands, it's fine. So if it was lightly used, I bet that even a old computer could run SQL Server just fine. Of course, it would be wise to plan ahead for new hardware should you predict the use to grow beyond the current hardware's capacity. Now, I didn't answer your question about files... That was deliberate as I wanted to emphasize that with SQL Server (and any other server-based RDBMS) you don't really deal with files. You deal with the daemon and the daemon manages the files. But yes, there are files, and in SQL Server, it's .mdf and .ldf which may be stored in a certain folder, depending on how you installed SQL Server. But the only time you actually worry about the file is when you are dealing with backup & restore tasks and even then that is not strictly necessary. After I had installed it I tried to run the Upsizing Wizard on a dummie copy of our database but after selecting "New Database" on the first step, I got stuck at "What SQL Server would you like to use for this database?" on the next step. Hopefully the above explanation should make clear that because you interact with daemons, not files, you need to tell the client to how to find the daemon... hence the prompt "What SQL Server would you like to use for this database"... It's possible that you may have two SQL Server running in your organization... which one do you want to use? Normally, you input in the host address. For your first time fiddling, you would put in "localhost" because I'm assuming you've installed SQL Server on the same computer where you wanted to upsize the Access database so by telling "localhost", it'll contact the daemon residing on your computer. BTW, I've never used Upsized Wizard since that one time when the results was less than satisfactory... I've heard results that SQL Server Migration Assistant provides much better experience. You may want to give this a go. I hope this helps. |
#5
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
Thanks Banana! Definately cleared away some of the fog. I just finished
writing a big long reply message asking about remote linking only to lose it just as I was posting it, so I'm going to rewrite it as briefly as I can: I'm having trouble finding info on linking to tables on a SQL Server database from a computer outside the LAN. 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) 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? 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? 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? "Banana" wrote: Jon22 wrote: I'm struggling with the fundamentals of SQL Server databases. Is it a single file like an .mdb file? Where does it get reside? Is it creating an SQL Server on the machine which is a different thing to an SQL server database? Which machine/s should I be installing the program on? We don't have a 'Server' persay just a number of desktop machines and a network storage device so are we under equipped? What IS a server anyway? Can one of our more powerful desktop workstations act as our 'server'? Good questions! Let's go back to Access just for a moment. When you create an Access database, you are directly managing a file, .mdb file. You manipulate it via Access UI, adding data (and objects, too) to it and perhaps even update or delete it as well. In this context, this is no different from what you've been doing with your Word document and Excel spreadsheet. You're working with a file. Now if we go to the SQL Server, you are no longer working with a file, per se. Rather, you "connect" to a server and thus communicate with a daemon (e.g. the program that resides in memory persistently and listens & reacts to requests from other programs). When you request a piece of data, it's the daemon that opens the data file, read the piece and send it back to you. Now, you examine this piece and decide you want to update. You then give the daemon the request to update, but it's the daemon that actually does updating into the data file. In all cases, you never ever touch the file directly. You work through the daemon and the daemon does all file handling. So, for most RDBMS out there, we interact with the daemons while in world of JET/ACE, or SQLite or SQL Server Compact Edition, there are no daemons and we manage the files directly. Once you understand that concept, it should help become clear why you connect to a "server" rather than open a file. You never ever touch those file directly, contrary to what you did in Access. The server can be anything, a dusty old 386 PC to whatever's the latest "big iron" supercomputer- it has nothing to do with hardware in question, though it is common to market "server hardware", but in fact, "server" refers to the role in the communication. As explained above, you need to have a place for the daemon to resides. The daemon very well couldn't exist in a void, can it? So whenever SQL Server happens to be installed, that is where the daemon is and thus that host is the "server" while other machines that requests data from this server are clients. In fact, Access is capable of being a client because you can create a linked table or use ADO connection to fetch data from a server. Now, generally speaking, it is usually expected that the server would run on more powerful hardware because obviously there is more demands placed on the server (and hence the market for "server hardware"). But it doesn't mean that you have to go out and buy yourself a server farm just to run SQL Server. As long the hardware where you install SQL Server are adequate for your actual demands, it's fine. So if it was lightly used, I bet that even a old computer could run SQL Server just fine. Of course, it would be wise to plan ahead for new hardware should you predict the use to grow beyond the current hardware's capacity. Now, I didn't answer your question about files... That was deliberate as I wanted to emphasize that with SQL Server (and any other server-based RDBMS) you don't really deal with files. You deal with the daemon and the daemon manages the files. But yes, there are files, and in SQL Server, it's .mdf and .ldf which may be stored in a certain folder, depending on how you installed SQL Server. But the only time you actually worry about the file is when you are dealing with backup & restore tasks and even then that is not strictly necessary. After I had installed it I tried to run the Upsizing Wizard on a dummie copy of our database but after selecting "New Database" on the first step, I got stuck at "What SQL Server would you like to use for this database?" on the next step. Hopefully the above explanation should make clear that because you interact with daemons, not files, you need to tell the client to how to find the daemon... hence the prompt "What SQL Server would you like to use for this database"... It's possible that you may have two SQL Server running in your organization... which one do you want to use? Normally, you input in the host address. For your first time fiddling, you would put in "localhost" because I'm assuming you've installed SQL Server on the same computer where you wanted to upsize the Access database so by telling "localhost", it'll contact the daemon residing on your computer. BTW, I've never used Upsized Wizard since that one time when the results was less than satisfactory... I've heard results that SQL Server Migration Assistant provides much better experience. You may want to give this a go. I hope this helps. . |
#6
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
=?Utf-8?B?Sm9uMjI=?= wrote in
: I'm having trouble finding info on linking to tables on a SQL Server database from a computer outside the LAN. Remember, the SQL Server needs to be installed on a permanent server (in a small office, it could theoretically be a workstation, but this is really not advisable, unless nobody ever uses it), and for it to be accessible to remote users, the port on that server that SQL Server is listening for connections on has to be available for the remote users to connect to it. Think of using a web page: when you make am HTTP request, your browser connects on port 80 (by default -- you could request http://dfenton.com:80 to specify it explicitly), which is the default open port for the worldwide web. 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. Instead, it is most common that a SQL Server used for an app like this is made available on the LAN and remote users connect to the LAN across the Internet over a VPN (virtual private network). The security for initiating the VPN connection is usual very high (some VPN client software requires special key authorization and it's all encrypted so the data inside the VPN tunnel cannot be examined by someone as it passes through their servers), and thus it serves as a strong locked door to keep people out of the LAN. The alternative, i.e., making the SQL Server port public, is much more dangerous. Sure, SQL Server has its own authorization (or uses Windows authorization), but historically, there have been lots of security holes that allow people to skirt proper authorization and get into places they shouldn't be. A VPN is harder to exploit in that way. So, think of the VPN as a really long network cable that's connected to the local LAN. You have to "plug it in" in order to see the remote network (this usually involves initiating a connection similar to a dialup connection, with username and password provided), but once the VPN connection is established, you will have a "local" network that is identical to that you'd see if connected by wired cable to the LAN in the "home" office (with some caveats: a sysadmin can configure exactly what a remote user sees and has access to by managing certain configuration parameters, but the general practice is to make the VPN view of the LAN as familiar to the users as possible, i.e., similar to what they can see and connect to when in the office on the wired LAN). All that said, I'd still recommend Windows Terminal Server over VPN instead of upsizing to SQL Server, since to do the former requires no change to your application whatsoever, while upsizing to SQL Server is a pretty involved process (particularly if you need to design it for slow connections from the remote users) -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#7
|
|||
|
|||
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! |
#8
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
Banana Banana@Republic wrote in news:4B87420C.2070209@Republic:
Now, I didn't answer your question about files... That was deliberate as I wanted to emphasize that with SQL Server (and any other server-based RDBMS) you don't really deal with files. You deal with the daemon and the daemon manages the files. One of the issues here is that unlike Jet/ACE, you can't just copy the files to a laptop and use them on the laptop disconnected from your LAN. You have to have an instance of SQL Server running on the laptop, so that any PC where you have SQL Server as local data store is going to be running SQL Server, and this is a potential security risk. Now, in the case where you're not storing any data on the disconnected machines and just using the app when connected to the network (LAN or VPN over Internet), you don't need SQL Server running locally in order to connect to a SQL Server. 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. But yes, there are files, and in SQL Server, it's .mdf and .ldf which may be stored in a certain folder, depending on how you installed SQL Server. But the only time you actually worry about the file is when you are dealing with backup & restore tasks and even then that is not strictly necessary. 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. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#9
|
|||
|
|||
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. |
#10
|
|||
|
|||
Might be outgrowing Access but daunted by SQL Server
"Jon22" wrote in message ... Thanks Stefan. I installed SQL Server 2008 Express on my laptop at home last night just to have a fiddle with it and try to get familiar with it but as I Dunno, haven't tried that one. I guess that includes Visual Studio Express? Otherwise you will be stuck using SQLCMD to do even the most basic tasks. Another option would be to download the full version of SQL Server to experiment on. Last I looked, the full version is FREE for you, a DEVELOPER, to do DEVELOPMENT on. You just use Express when your company starts using it for real data. The advantage of the full version is that it comes with the complete client tools and documentation, including SQL Server Enterprise Manager, which is (was?) the primary administration tool for SQL Server. Dunno how Visual Studio/SQL Server Express compares with that now. said, it confused me no end. After I had installed it I tried to run the Upsizing Wizard on a dummie copy of our database but after selecting "New Database" on the first step, I got stuck at "What SQL Server would you like to use for this database?" on the next step. I'm struggling with the fundamentals of SQL Server databases. Is it a single file like an .mdb file? Yes, it is a couple of files, like Access requires MDB, LDB and MDW files, and yes the data is in just one MDF file, like Access data is in just one MDB file. 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. For Access, creating new databases is done from Access, and setting up network database shares in done from Windows Explorer: For SQL Server, it is done from Enterprise Manger, or Visual Studio, or using OSQL or SQLCMD Is it creating an SQL Server on the machine which is a different thing to an SQL server database? Creating a SQL Server on a machine means installing and starting the SQL Server service. For Access, the "SERVER" service is required (plus some other things). For SQL Server, a "SQL SERVER" service is required (plus some other things). The only difference is that the "SERVER" service is included and started by default on Windows: the "SQL SERVER" service you had to download and install. Which machine/s should I be installing the program on? We don't have a 'Server' persay just a number of desktop machines and a network storage device so are we under equipped? Wherever you want it. What IS a server anyway? The main differences between a typical server and a typical workstation are (1) a server is optimised for more people using it, and (2) a server permits more people to connect to it. Can one of our more powerful desktop workstations act as our 'server'? SQL Server Express is optimisied for a small PC with a small number of connections, so it fits just fine on a workstation or on a heavily used server. And if you use a full version for development, you will be the only person using it, so it also fits just fine anywhere. I need to be able to run all this myself as we are too small to have regular outside IT costs, but large enough to require a WAN Access Database solution. I might be wrong but I think it'd also be better to have the data (tables) stored locally (office network) but accessable remotely as most of the usage is at the office and I'd prefer not to sacrifice too much speed for the ability to use the database remotely. Can I have my cake and eat it too using SQL Server? You can never have your cake and eat it too. If you want it to work well over the network, you have to keep learning and keep working. It's not magic. Would the SharePoint option make using the database slower at the office? Haven't tried the new SharePoint. You don't want the old SharePoint. Also the database relies heavily on unique key values in the tables such as Quote Numbers, Purchase Order numbers, Job Numbers, Invoice Numbers etc. Many of which are automatically generated in the forms via complex functions in the control's default values which in turn rely on up-to-date and constantly refreshed info from the tables so as to avoid duplicating these values. Is SQL Server the solution for this? Is there such a thing as a back end database that updates instantly with every single change or addition made to it that is instantly reflected in multiple user's front end db all over the world? SQL Server is not a real-time communications network. It's just a database. I had a bit of a trial run a few months back at splitting this database and playing around with the sharing options and could not for the life of me get the thing to avoid duplicating key fields when opened simaltaneously. This worries me greatly as the whole thing relies on the integrity of these fields. You will have to completely re-write that stuff in TSQL. It's not a lot more difficult, but it is different. (david) |
Thread Tools | |
Display Modes | |
|
|