Connecting MS Access with SQL Server (1 Viewer)

Golo

New member
Local time
Tomorrow, 05:30
Joined
May 8, 2021
Messages
9
Hi guys, I am newbie who are extremely low-tech.

I am currently building up a MS Access database with Tables, Forms, Queries, Reports for a team of around 6-8 people. We are working in a new office with no shared networks, no Microsoft Office Enterprise yet. Each of us is using our own individual MS Office. Now that I want to create this database and will split BE and FE so that all of us can work simultaneously. Could you please correct me if I am wrong, I now understand that I need to move the BE to either a shared network or a SQL Server, and leave the FE on each of our own PC to make it work, theoretically.

Now after some considerations, I think I would go for a SQL Server as we already have a shared Dropbox folder so we would not probably need one more shared net work. In this case, could you please advise me what package of SQL Server should we buy so that we could all use this database? I did a little research but I was confused by the amount of terminology thrown at me :'( Do we only need 1 SQL Server Account or 6-8 of them to make it work? And what are CALs in this case?

Thanks for your time to enlighten me folks!
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:30
Joined
Sep 21, 2011
Messages
14,046
With your setup, I believe you could go with a reasonable desktop computer as a server and put the BE on that.?
You could even try using the most powerful PC in the office as the server first?

They should all likely be connected to a router, so can all be able to see other computers on the network.?

I now nothing about SQL server other than what I have read here, but unless you explciitly need the extras it offers, like better security, then the above should be able to handle what you have.?

In my last place of work, that was our setup with 20 users on the network and about 5 using the Access DBs.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:30
Joined
Feb 28, 2001
Messages
26,999
I'm going to suggest that if you already have a machine hosting a dropbox function, you can make a separate folder using simple Windows file sharing. For a group of 6-8 people, you should have no problems. Yes, you are correct that a split FE/BE database is EXTREMELY preferred - but it is OK if the BE file is simply a "native" Access BE and that hosting machine supports Windows File Sharing.

However, I will also warn you that most DROPBOX systems (if that are using explicit dropbox software) do NOT support what Access wants to use. Which is why I suggested a separate folder for the BE file independent of the dropbox setup.

IF the host permits ordinary Windows File Sharing then Access can use it. No need for SQL Server licenses at all - and in fact, the configuration of having a file sharing system that doesn't itself directly host a user doesn't need an Access license either. Only the FE files need the license in that case. The good news is that if your business grows, you can promote the BE to become an SQL Server database later if that becomes necessary, but can continue to use the Access FE. You just have to change the connection strings. (OK, not THAT simple, because of a few data type differences between Access and SQL Server - but ONLY a few.)

Once you decide which hardware configuration you want, come back and talk to us some more. Once you have a direction, we can guide you better.
 

Golo

New member
Local time
Tomorrow, 05:30
Joined
May 8, 2021
Messages
9
@The_Doc_Man @Gasman : Thank you so much for your prompt reply! I will try your recommendations and come back here once I learn which suits me best!
 

Minty

AWF VIP
Local time
Today, 18:30
Joined
Jul 26, 2013
Messages
10,355
A bit late to the party but if you are hosting it yourself look into SQL Server Express which is free, and perfectly useable for smaller environments.

Certainly useful for evaluation purposes even if you end up needing a more enterprise solution.
 

Auntiejack56

Registered User.
Local time
Tomorrow, 05:30
Joined
Aug 7, 2017
Messages
175
If you are 6-8 individuals using separate computers with no local network connecting them, then you are a company format that I am seeing more and more of. Sorry, 'of which I am seeing more and more'.
Where would you put a central SQL server in your case? If you have a shared Dropbox folder so that you can collaborate, that's very smart, but I don't see how it enables all of you to connect to a central database. A passive Access BE might be ok there for a while, but I wouldn't bank on it.
How high is the bar for your data security? That's a consideration, but one option is to use a SQL Azure cloud database. A broad overview of what you'd need to do is:
  • Download SQL Express (along with SSMS - SQL Server Management Studio) to your computer. It's free, so you're off to a flying start.
  • Using SSMS, create your database using SQL Express as the backend.
  • When FE and BE are working as they should, open your browser, go to Microsoft, get an Azure account, create a new database in the cloud. (That'll be free for a while too, and then inexpensive unless you use lots of data and bandwidth.)
  • With SSMS, attach your new Azure database to your local SQL Server Express.
  • Now you can use your SQL Azure database as though it was local. Copy the tables from your BE (the local that you've created) to the Azure BE (in the cloud).
  • When done, attach the tables for each user's FE to the Azure database.

Jack
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:30
Joined
Feb 19, 2002
Messages
42,971
You have a lot of technical hurdles to overcome. This may be falling to you because the company doesn't seem to have any infrastructure yet. Are you in a position to recommend infrastructure? One thing that must be clear is that Dropbox and any of its competitors will NOT work for what you want. PERIOD. Access to be shared, REQUIRES a LAN and you don't have one of those either.

If everyone works in the office and you have WiFI, you can make a network but sharing your Access BE using WiFi is dangerous. Access is very susceptible to network blips and they will cause the FE to loose its connection to the BE and that could result in corruption of the BE, so, using Wifi is NOT recommended.

OK, what now? If your app is simple and won't hold a lot of data, AND you have or can acquire a SharePoint license, you can use SharePoint to hold your data. Using an Access FE against a SharePoint BE over WiFi is less dangerous. You still have blips but are less likely to corrupt the SharePoint BE. This is still undesirable.

That leaves us with a third party Citrix option. This can be expensive but is probably the easiest short term solution to share the app. I haven't looked in a while but when I looked a few years ago for a client, they wanted $50 per month per seat for the Citrix + O365 licenses. If your Access app does not require automating other Office apps, you can get away with the Access Runtime engine which is free so you should be able to reduce the per seat cost by at least $10 per month. You only need the full version of Access on the development machine. So, I would look at this as a temporary solution. Once you have a LAN, you can stick with Citrix and host it yourself which will be more cost effective once you have more than a few users.
 

Golo

New member
Local time
Tomorrow, 05:30
Joined
May 8, 2021
Messages
9
Thanks you everyone for enlightening me!

@Auntiejack56 : So basically I did try your recommendation, but not entirely. This is what I did:
- Create a MS Access App with Tables, Forms, Querry.
- Create a MS Azure SQL Free Account to register for a server and a database.
- Download SSMA and use it to migrate the Access DB to Azure successfully.
- I am in Australia now, but Microsoft in this region does not support the 12-month free account, so I set my server location in Southeast Asia, which is the closest and free.
- Once I have done the migrating job, I am able to open the Access file, however, I am unable to open some forms. There is a message box saying cannot find the tables something (I am not at the office right now so I will be back tmr and take a screenshot of that message box) and I have not been able to figure out why it is so. Moreover, the speed is also of concern. I do understand that the server is in one region while I am in another could slower the connection speed, it is around 5 times slower than if it was not migration to Azure SQL. I am wondering if the server location was in Eastern Australia (where I am now), would the speed be improved significantly?
 

Golo

New member
Local time
Tomorrow, 05:30
Joined
May 8, 2021
Messages
9
@Pat Hartman : Thanks Pat. I did a bit more research and am referring to your comments in this thread access-programmers.co.uk/forums/threads/connect-my-ms-access-db-to-google-cloud.299363/

- As you mentioned in this post, is it still true now that even though Azure is the best online database for MS Access, there are problems with that, regarding to speed connection, forms, tables, etc.?
- I now understand that LAN (or a local shared network) is the best environment for MS Access and I probably go for this option if Azure does not work so well for my case. I am just looking a bit further to the future. We are now having only 1 office, so setting up a LAN for people in this office to work simultaneously is possible. However, let's say my boss wants to open another office at another place, then this LAN at the first office won't be able to be used at the second office. In this case, in order for people from 2 different locations to work simultaneously with the MS Access DB, could you please advise me what option I could consider? At the moment I have not had a chance to look at Citrix yet, but might try that in the next few weeks!
 

Minty

AWF VIP
Local time
Today, 18:30
Joined
Jul 26, 2013
Messages
10,355
Moreover, the speed is also of concern. I do understand that the server is in one region while I am in another could slower the connection speed, it is around 5 times slower than if it was not migration to Azure SQL. I am wondering if the server location was in Eastern Australia (where I am now), would the speed be improved significantly?
Short answer yes.

If a form takes 4-5 seconds (just about acceptable) to load on a local area based server, it's suddenly going to take 25 seconds (unacceptable) on the one a long way away. A complex update that takes 10-12 seconds to run suddenly takes over a minute, and might timeout.

We have a user in India connecting to a UK based server. When you added in their poor local infrastructure their average latency time was approaching 500ms, rendering things very unusable.
We did a lot of work on restricting datasets and passing things back to the server for processing, but it was still not good.
Anyone in the UK or Europe was absoulutely fine though.

Azure latency;
This is on a average fibre broadband connection at home (35Mbs ish)
1622024590235.png
 

Auntiejack56

Registered User.
Local time
Tomorrow, 05:30
Joined
Aug 7, 2017
Messages
175
Hi, I ran a free Azure database for a month, then it was low cost after the free period expired. I'm in Sydney, connected to an Azure database located in the US. Response time was about the same as refreshing a browser page - about 2 seconds. Databases that I've converted in this way run without problems. Post the screenshot if you can't figure out the error on the forms that you mentioned.
Jack
 

Isaac

Lifelong Learner
Local time
Today, 11:30
Joined
Mar 14, 2017
Messages
8,738
I am amazed at how expert some companies/people are at setting up servers and infrastructure. When I worked for Wells Fargo, we accessed SQL Servers and File Servers all the time.

The guys accessing it from our India team had just as good performance as I did in USA.

And we were frequently on VPN over Wifi, and even when we went into the office, we were connected to in-office WiFi (never plugged in the Ethernet cord).

Pretty amazing to me
 

Golo

New member
Local time
Tomorrow, 05:30
Joined
May 8, 2021
Messages
9
Hi, I ran a free Azure database for a month, then it was low cost after the free period expired. I'm in Sydney, connected to an Azure database located in the US. Response time was about the same as refreshing a browser page - about 2 seconds. Databases that I've converted in this way run without problems. Post the screenshot if you can't figure out the error on the forms that you mentioned.
Jack

Hi Jack, so these are some errors I have after migrating to Azure SQL!

This is the screen when I try to open the "Employee Record" button on the Menu form

1622074728169.png


And this happens when I open "All Working Notes" form from my "Client Basic Info" form

1622074535546.png
 

Attachments

  • 1622073141415.png
    1622073141415.png
    42 KB · Views: 204

Auntiejack56

Registered User.
Local time
Tomorrow, 05:30
Joined
Aug 7, 2017
Messages
175
Hokie dokie,

3 things to check. First, if you have Name Autocorrect on (it's very handy while you are developing), turn it off at this stage. It looks to me like your database was unsuccessfully trying to autocorrect names within your recordsources during the migration. So turn off all the Name Autocorrect options for the db (and leave them off now, they should never be on after deployment) and rebuild the recordsources.
1622122261078.png


Second, another option: make sure your Default record locking is No locks

1622122319103.png


And last, check the record locking for the forms, which should also be No Locks
1622122359999.png


Going back to your first error screenshot, the name prefix ~sq_ suggests that the recordsource has been built automagically by access, so look into the underlying queries, you may need to delete them and rebuild.

Jack
 

kentgorrell

Member
Local time
Today, 18:30
Joined
Dec 5, 2020
Messages
47
Hi, I ran a free Azure database for a month, then it was low cost after the free period expired. I'm in Sydney, connected to an Azure database located in the US. Response time was about the same as refreshing a browser page - about 2 seconds. Databases that I've converted in this way run without problems. Post the screenshot if you can't figure out the error on the forms that you mentioned.
Jack
Hello Auntie Jack, I knew you'd be back... grew up in Wollongong myself

I'm finding it rather difficult to get any idea of cost on an Azure Db. MS's calculators come up with figures or 200-300 / month and as my client really only needs SQL Express that is a bit over the top.
Roughly how much is Azure costing for a handful of users with only moderate use?

I've not been able to find much on setting up windows authentification on P2P networks. Azure AD that comes with 365 apparently only works with Azure SQL Server.

The only reason I'm considering Azure for this project is that windows authentification is proving difficult on a P2P network. I never had a problem doing this before as long as identical user credentials were on the machine hosting SQL Server but this doesn't seem to be working anymore and I think it has something to do with MS deprecating HomeGroup in Windows 10 1803 or it might be that I've forgotten something. It's been a while - most of my work now is in networks with AD and managing AD is not my resonsibility.

Even tried using the same MS account on both but that's a mess, MS accounts don't seem to synch very well, for example: connecting to other machines to share a drive seems to require old passwords even if I've logged onto both machines with the same MS account.
 

Auntiejack56

Registered User.
Local time
Tomorrow, 05:30
Joined
Aug 7, 2017
Messages
175
Hi, yes I'm back!

Unfortunately, my info on this subject is rather mixed.

First, MS calculators seem to me to be a little pessimistic, but taking a 'suck it and see' approach might turn out expensive if a user is logging in frequently or the data drag is high. Sorry I haven't got any indicative figures on it, because ...
Second, I gave up on Azure a while ago because of security concerns - MS Access is still not really able to connect securely in my humble opinion. if you build a VPN etc then maybe you can justify it, but I found there were too many pieces in the puzzle. I looked at using other ODBC drivers and various other approaches and threw my hands up in despair. Not once but several times.
So now I host my own SQL / Access environment on a set of virtual machines, using a thin client to login (it has a built in VPN which holds your password, and its little icon sits on the task bar, so it looks local). Sensitive data is encrypted, cost is low (fixed price user/mth), response time is good, and the whole box and dice is ringfenced so everything is nice and tight. My own clients, and other peoples, so I take the FE and a backup and everything is up and running fairly quickly.

Apologies that I couldn't help you with Windows authentication (eek, not surprised you found it difficult) or pricing for 'a handful of users' (which magically propels Azure prices into larger more expensive machines and more licenses). Keeping a demo Azure environment to show clients, as I did, was inexpensive for a single occasional user, but further up the scale I don't really know.

Jack
 

kentgorrell

Member
Local time
Today, 18:30
Joined
Dec 5, 2020
Messages
47
Thanks Auntie,

When you say "Thin Client" do you mean RemoteApp?

RemoteApp is another approach I'm contemplating, and I have setup a remoteApp for an Access application with a SQL Server BE but only tested on my LAN so far. This will not have the latency issues with Azure and should be just as secure as any RDS. MS claim that Azure DBs can now be secured with Azure AD but that's just one of the issues.

I 'm a developer not an IT expert but I'm currently experimenting with a bear metal install of 2019 Standard (Essentials seems to be on the way out) to host VMs but this requires AD, Remote Gateway etc and it all gets time consuming to set up. Not to mention the complexity of MS's licensing. 1 CAL per user + 1 RDS CAL per user + how many server licenses if you need 2 Domain Controllers... and the list goes on!

How are you hosting your VMs?
 

Auntiejack56

Registered User.
Local time
Tomorrow, 05:30
Joined
Aug 7, 2017
Messages
175
Nope, remoteapp was not supported well enough when I was developing my solution (in fact I got the impression that it was unworkable), and so I gave it the el dumpo and I use ParallelRAS. Best decision.
In short, my VM hosting eliminates the AD, gateway, domain controllers, fees, licensing and whatnot. You just get a login, download the ParallelRAS client and away you go. But my VMs, and the ParallelRAS licensing, are part of a larger multi-tenant hosting, so you can't do it for a single client - it would be way too expensive. If your app is straightforward and already connects to SQL Server, then we don't charge to put it up, just a monthly per user fee, which is a fraction of the price.
 

Users who are viewing this thread

Top Bottom