Requirements to Upscale an Access Server

[Bearlord]

Hello,

I've been trying to set up a database at my company, one that was easy enough for all of us to use and easy enough for me to deploy. We don't have a TI department, so I have been testing different methods to deploy a small database that wouldn't require a small army of coders and database experts to deploy and support.

I've had many failures... I tried using dropbox to share the back end of an access database... you can read about it in this thread: http://www.mrexcel.com/forum/micros...torage-dropbox-google-drive-etc-can-done.html It had some success, but not quite.

I've tried sharing the back end through a shared network folder, run by a decent computer. That one worked fine, but it only worked locally, in the same network where the computer was located. It wouldn't provide support for our off-site coworkers...and that was a big issue.

So finally, I had some budget approved to get our first server... however... I am not sure where to start. I want to "upscale" my database. I read about that in here: Move Access data to a SQL Server database by using the Upsizing Wizard - Access

It sounds really cool and easy to do (the upscale part)... however that article fails to explain what should I do on my server in order to have the database there... remember, I am really new into this matter and I am by no means an expert on servers nor databases.

The server we are buying will be running linux, or so I was told by the guy that will be installing it. Another huge requirement for the server, was the support of shared folders available through the web, not only through the local network. The guys that are installing the server said that we needed a VPN, so they are installing a Linux based server that will support VPNs, and they said it should be more than capable of handling a database like the one we are already running (on the local machine). Once they assured me that the server could run our database, I asked them if they could set that up for me, and they said they didn't know how... so I am left on my own once again to do this.

The VPN capable server was way beyond the approved budget, so I really don't think I can call one of those fancy companies that set up databases and help us get everything up and running. So I need to do that by myself. Naturally, I am full of doubts and pretty confused about what I am supposed to do next.

Once the server is up and running, what do I need to install on it in order to be able to run my "upscaled" database? Will a linux server work for this purpose? Does anyone know of a guide, book, youtube tutorials, etc... that can help me learn how to set up the back end database on a server.

Thanks a lot! I would really appreciate some guidance through this.

PS. Pardon my english, its not my main language.

 

[xenou]

If you want to run your database on a Linux server then you can't use Access or SQL Server. There are plenty of good databases that do work on Linux.

If you are in the windows world and only using Linux for running your VPN then Hi-Tech Coach has a good article on using Access with remote access:
Remote access for an Access Application
And of course SQL Server is an option for windows, but not for running on a Linux server.

 

[Bearlord]

Hello xenou, thank you for the fast reply!

I know I can't run a MSSQL server directly on Linux... but I was wondering if it was possible to use another type of SQL that can run on Linux to use it as a back end for Access. For example: PostgreSQL, or MySQL, etc.

Or maybe even use a virtual machine inside of the Linux Server to run MSSQL directly... I have vague ideas of how it could work, I just don't know exactly what are the steps to set up the database. I've been looking for books, or tutorials about it, but I can't find any.

I found this post on the web: Using MS Access & ODBC to connect to a remote PostgreSQL - Stack Overflow

It seems to be stable, but I wouldn't know where to start to get it up and running. Just wondering if anyone else has experience doing something similar

Thanks again for the reply

 

[xenou]

You will need to install the appropriate ODBC driver (for instance, Postgre or MySQL) on the machine that is connecting to the server. From Access you create a pass through query to link to your table(s) in the database. As far as installing the database itself is concerned you do that on the server. It depends on the system you are using. Most linux distros will have database packages you can download and install with a few commands from the command line (but this isn't the same as building your actual database - it just gives you the database server ready to go).